Referecing a range in another workbook in vlookup

[DMT]

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.

 

[JohnnyTightlips]

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)