Vlookup over multiple sheets

S

SS

Hi

Anyone know if it is possible to do a vertical lookup over multiple sheets

Any ideas would be great

Thanks Shona
 
C

Chip Pearson

You can't do that. VLOOKUP only takes a range on a single sheet.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
P

Pete

You could have a contruct like:

=IF(ISNA(VLOOKUP(first_sheet_formula)),
IF(ISNA(VLOOKUP(second_sheet_formula)),"Not
Present",VLOOKUP(second_sheet_formula)),VLOOKUP(first_sheet_formula))

You could extend this for more sheets, but the formula will become a
bit unwieldy.

Hope this helps.

Pete
 
W

wrishibothra

You might want to try nested If statements to run the vlookup in the
2nd, 3rd, 4th sheets if the 1st, 2nd and 3rd return error values (which
you can test using the iserror function).

The big downfall is that it will become a very processor hungry
formula. You should try using an Index and Match function combinations.
Plenty of examples are available on the web

Regards

Wrishi Bothra
 
D

Domenic

How many sheets do you have? How are they named?

Where are your tables located? Are they all located in the same range
of cells for each sheet?
 
S

SS

Sheet 1 to have the fomula looking up A2 the arra Sheets are named Jan, Feb
etc so 12 which will be the array and only columns A & B with the result to
be what is in the second column

Hope that helps
Thanks
 
P

Pete

If you could give us a few more details then we might be able to
provide a more detailed solution for you.

Pete
 
D

Domenic

Two options...

Option one:

Let F2:F13 contain your sheet names, and then use the following
formula...

=VLOOKUP(A2,INDIRECT(INDEX(F2:F13,MATCH(TRUE,COUNTIF(INDIRECT(F2:F13&"!A2
:A100"),A2)>0,0))&"!A2:B100"),2,0)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the
ranges (A2:A100, A2:B100, and F2:F13) accordingly.

Option two:

This doesn't require you to list your sheet names, but it uses three
cells...

B2:

=MATCH(TRUE,COUNTIF(INDIRECT(TEXT(DATE(2005,ROW(INDIRECT("1:12")),1),"mmm
")&"!A2:A100"),A2)>0,0)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

C2:

=INDEX(TEXT(DATE(2005,ROW(INDIRECT("1:12")),1),"mmm"),B2)

D2:

=VLOOKUP(A2,INDIRECT(C2&"!A2:B100"),2,0)

Hope this helps!
 
Top