Need to run VLOOKUP using two different workbooks

[Elma_Letic]

Hello!

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:

VLOOKUP(A2’[b.xlsx]Sheet1’!$E:$F,2,FALSE)

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.

[nameless]

You can try Join Datatables activity, join type Left join

[Elma_Letic]

Thank you for the suggestion! I imagine I’d be joining the tables on the Unique IDs columns (A in a.xlsx and E in b.xlsx)? Is there a way to include the data from column F of b.xlsx into the join activity as well?

I need all the data in a.xlsx, with column F from b.xlsx added as another column so that the demand for each product is seen clearly and cohesively in one sheet/table.

[nameless]

yes, you will join on the unique id columns, and if i’m not mistaken, the end results should have all the data from both the input datatables joined

[Elma_Letic]

Thank you so much! This works exactly as expected!