Referecing a range in another workbook in vlookup

DMT

New Member
Joined
Jan 21, 2004
Messages
28
Hi,

I am trying to do the following. I want to be able to use vlookup to find a value within a table, but the table could be in any one of four workbooks. I want to be able to replace the "Table_array" part of the vlookup formula [note: =VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)] with a reference to a cell. The contents of the cell would be the workbook, sheet and range name, such as [Book1.xls]Sheet1!A1:D10.

So now I would be able to use concetentate and change the contents of the cell to Book2, Book3, etc. and the vlookup formula would refer to the appropriate workbook.

Thanks.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94
Excel's indirect function should be of use to you. Unfortunately, as a volatile function, it only works if the workbook is open

in the table array, enter indirect(cellreference)
 

Forum statistics

Threads
1,141,720
Messages
5,708,078
Members
421,545
Latest member
TWR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top