vlookup across more than one sheet

A

Anita

Is it possible, without code, to do a vlookup across multiple sheets? as
65536 rows isn't enough. I don't want to have to select the ranges each time
though as this is time consuming.

Thanks

Anita

version 2000
 
P

Pete_UK

Assume your lookup table spans sheets 2 and 3 and occupies columns A to
B, and that in A1 of Sheet1 you have the search value. In B1 you can
enter this formula:

=IF(ISNA(VLOOKUP(A1,Sheet2!A$1:B$65536,2,0),IF(ISNA(VLOOKUP(A1,Sheet3!A$1:B$65536,2,0),"",VLOOKUP(A1,Sheet3!A$1:B$65536,2,0)),VLOOKUP(A1,Sheet2!A$1:B$65536,2,0))

The formula can be copied down column B if you have other values in
column A, and returns a blank if the search value is not in the
(extended) lookup table.

Hope this helps.

Pete
 
Top