Using VLOOKUP with a 3D range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cassie - 11 Sep 2007 11:48 GMT

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?

Reply to this Message

Pete_UK - 11 Sep 2007 13:02 GMT

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

> Hi
>
[quoted text clipped - 6 lines]
> workbook. I've tried VLOOKUP, MATCH, INDEX and a very complicated IF function
> but nothing seems to work. Any ideas?

Reply to this Message

JNW - 11 Sep 2007 21:58 GMT

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.

Signature

JNW

> 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
[quoted text clipped - 26 lines]
> > workbook. I've tried VLOOKUP, MATCH, INDEX and a very complicated IF function
> > but nothing seems to work. Any ideas?

Reply to this Message

Peo Sjoblom - 11 Sep 2007 22:06 GMT

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

http://nwexcelsolutions.com/advanced_function_page.htm

look at number 5

Signature

Regards,

Peo Sjoblom

> Just remember that this will only work if you have no more than 7 sheets
> you
[quoted text clipped - 34 lines]
>> > function
>> > but nothing seems to work. Any ideas?

Reply to this Message

Pete_UK - 11 Sep 2007 23:29 GMT

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

Pete

> 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.
[quoted text clipped - 33 lines]
>
> - Show quoted text -

Reply to this Message