Using VLOOKUP with a 3D range

C

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

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
 
J

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

Pete_UK

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

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

Top