vlookup across more than one sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup 3
vlookup 2
Vlookup increment No 4
Nested IFs 1
LOOKUP across Multiple Sheets 4
Using VLOOKUP with a 3D range 4
Vlookup from different sheets 2
Vlookup and return the value from the look up value sheet 1

Back
Top