Multiple Workbook Vlookup

Hi all,

This is my first post on the forum, so forgive my potential lack of excel/function knowledge (and possibly forum etiquette!)!

My issue involves using Vlookups across dynamic workbooks.

I want to have a cell that a user will enter the name of a workbook - a macro will then use this to perform a Vlookup on that workbook (i understand that the sheet has to be open).
I have attached a picture that will explain it much more clearly than the explanation below!

The end goal is to lookup a column in another workbook (ill call it Car Sales) and to copy and paste the data into the second sheet of this workbook (Workbook 1).

I have been playing with a Vlookup with an Indirect function within it. The indirect function is used because there will be multiple workbooks with the same format and layout as Car Sales that I want to consolidate into this workbook. (sorry it is hard to explain..)

This is my first attempt at the formula -
=VLOOKUP(M24,INDIRECT("'["&G20&".xlsx]"&H20&"'!"&A$1:$E$8&""),2,FALSE)

Where M24 contains the name of the column "Car Colour",
G20 contains the name of the other workbook "Car Sales"
H20 contains the name of the worksheet on the workbook "Car Sales"
and A1:E8 is the range of data (on Car Sales) that I want the information from.

The information I am after is in column B Car Colour and the info is in the form "Red" "Blue" "Green" on separate rows.

On Workbook 1 sheet two I would ideally look like..
"Read"
"Blue"
"Green"

As you can see from the picture, my first attempt at this equation (unsurprisingly) brings a #VALUE! error.



Any advice as to how I could fix this or use another function to get the same outcome would be amazing!

Hopefully I haven't made this too confusing and hopefully someone can give me a hand with this.

Thanks in advance,

James

You want

=VLOOKUP(M24,INDIRECT("'["&G20&".xlsx]"&H20&"'!A$1:$E$8"),2,FALSE)

Hi Bob,

Thanks for your reply, seems I was pretty close!

Am having a slight issue with it though. Firstly I had to change false to true in order to get a value and not N/A.
However the issue that I am having is it is only giving me the value of the last cell


"Red
Blue
Green"

And it is giving me "Green" as an answer but I dont seem to be able to get it to give either of the other answers.
Ideally I want it to look up all of the values in column B in the same order as they are on worksheet 2.


Thanks again
James