vlookup across more than one sheet
 

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

vlookup across more than one sheet
 

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(ISN A(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


Anita wrote:
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