Using VLOOKUP with a 3D range

[Cassie]

Hi

Is it possible to use VLOOKUP across a 3d range? I have six sheets in a
workbook, and on each sheet column G holds a store number. I have created a
3d range across the sheets.

I want to cross reference the data on the six sheets with a master sheet to
ensure that all the stores from the master sheet appear somewhere else in the
workbook. I've tried VLOOKUP, MATCH, INDEX and a very complicated IF function
but nothing seems to work. Any ideas?

 

[Pete_UK]

Assuming your six sheets are named Sheet1, Sheet2 etc, and you have a
seventh sheet with the lookup value in A1 and you want details of
where the lookup value does not occur in B1, then how about something
like this in B1:

=IF(ISNA(VLOOKUP(A1,Sheet1!G:G,1,0)),"1 ","") &
IF(ISNA(VLOOKUP(A1,Sheet2!G:G,1,0)),"2 ","") &
IF(ISNA(VLOOKUP(A1,Sheet3!G:G,1,0)),"3 ","") &
IF(ISNA(VLOOKUP(A1,Sheet4!G:G,1,0)),"4 ","") &
IF(ISNA(VLOOKUP(A1,Sheet5!G:G,1,0)),"5 ","") &
IF(ISNA(VLOOKUP(A1,Sheet6!G:G,1,0)),"6","")

If the lookup value is present in all sheets you will get a blank
returned, but if it is missing from, say, Sheets 2 and 5 you will get
"2 5 " returned.

Hope this helps.

Pete

 

[JNW]

Just remember that this will only work if you have no more than 7 sheets you
are using. Excel won't let you nest more than 7 IF statements at a time.

 

[Peo Sjoblom]

Here's an example that work like a 3D range


http://nwexcelsolutions.com/advanced_function_page.htm


look at number 5

 

[Pete_UK]

The IFs are not nested in my formula, so it will not suffer from a
limit of 7.

Pete