VLOOKUP across multiple sheets

  • Thread starter Thread starter rainbm
  • Start date Start date
R

rainbm

CAn anyone tell me if it's possible to use VLOOKUP across multiple
sheet.
I've tried naming an area accross several sheets but I get a #VALUE
error:

data='sheet1:sheet4'!$A:$G

then

=VLOOKUP($A5,Data,3,"FALSE") gives me the #VALUE error

If you can do this, do you have any other suggestions?

Thanks:)
 
How about just looking. If you don't find it, go to the next sheet:

Does this mean that you look in sheet1 and use it if you find it, else check
sheet2, then finally sheet3?

=if(not(isna(yourlookupforsheet1)),yourlookupforsheet1,
if(not(isna(yourlookupforsheet2)),yourlookupforsheet2,
if(not(isna(yourlookupforsheet3)),yourlookupforsheet3,"missingfromall"))))

for example:

=IF(NOT(ISNA(VLOOKUP(A1,Sheet1!$a:$g,3,FALSE))),
VLOOKUP(A1,Sheet1!$a:$g,3,FALSE),
IF(NOT(ISNA(VLOOKUP(A1,Sheet2!$a:$g,3,FALSE))),
VLOOKUP(A1,Sheet2!$a:$g,3,FALSE),
IF(NOT(ISNA(VLOOKUP(A1,Sheet3!$a:$g,3,FALSE))),
VLOOKUP(A1,Sheet3!$a:$g,3,FALSE),
"missingfromall")))

All one cell.
 
Thanks Dave, I'd come to the conclusion this was a way to do it. BUt
didn't see any reason why VLOOKUP shouldn't be able to read data sprea
accross a few sheets. Seemed a bit more elegant:) Anyway. Yours work
well. Thank
 
Back
Top