Hi

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

Any ideas would be great

Thanks Shona

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


"SS" wrote in message
...
Hi

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

Any ideas would be great

Thanks Shona

OK thanks

Is there some other formula I could use?

Shona


"Chip Pearson" wrote in message
...
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


"SS" wrote in message
...
Hi

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

Any ideas would be great

Thanks Shona

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(fi rst_sheet_formula))

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

Hope this helps.

Pete

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

Chip Pearson wrote:
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


"SS" wrote in message
...
Hi

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

Any ideas would be great

Thanks Shona

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?

In article ,
"SS" wrote:

Hi

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

Any ideas would be great

Thanks Shona

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

"Domenic" wrote in message
...
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?

In article ,
"SS" wrote:

Hi

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

Any ideas would be great

Thanks Shona

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

Pete

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,COUNT IF(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(IN DIRECT("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),"mm m"),B2)

D2:

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

Hope this helps!

In article ,
"SS" wrote:

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