I have two Excel sheets, a.xlsx and b.xlsx, where a contains column A (unique IDs), and b contains columns E (unique IDs that match column A in a.xlsx, but may not be in the same order as they are in a), and F (the demand for the IDs in dollars).
The VLOOKUP formula is set up in a blank column of a.xlsx (let’s say column E), and here are the parameters passed in the VLOOKUP formula when doing it manually in column E of sheet 1 in workbook a.xlsx:
This formula should essentially return the demand sales for each unique ID in column E of a.xlsx so that the user can see which of the items should be ranked first, second, third, so on.
I have tried both the Execute Macro and Invoke VBA activities, as well as using the Lookup Data Table activity, but they either do not work due to errors (I am not that proficient in Excel therefore prefer to try to keep out of building a macro for VLOOKUP), or, in the case of the Lookup Data Table activity, are not returning the needed results.
The closest thread I have found might be: Vlookup between two different excel file sheets - #3 by Shikhar_Tandon but again, the solution does not do exactly what I need (unless I am missing something when trying to fit the example to my case).
Please keep in mind I am very much a beginner in Excel so if the best option is to create a Macro for VLOOKUP and use it in my sequence, any details on how to approach that would be extremely beneficial.