Using VLOOKUP with a 3D range
 

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?

Using VLOOKUP with a 3D range
 

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

On Sep 11, 11:48 am, Cassie wrote:
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?

Using VLOOKUP with a 3D range
 

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


"Pete_UK" wrote:

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

On Sep 11, 11:48 am, Cassie wrote:
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?

Using VLOOKUP with a 3D range
 

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


http://nwexcelsolutions.com/advanced_function_page.htm


look at number 5



--
Regards,

Peo Sjoblom



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


"Pete_UK" wrote:

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

On Sep 11, 11:48 am, Cassie wrote:
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?

Using VLOOKUP with a 3D range
 

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

Pete

On Sep 11, 9:58 pm, JNW wrote:
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.
--
JNW



"Pete_UK" wrote:
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

On Sep 11, 11:48 am, Cassie wrote:
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?- Hide quoted text -

- Show quoted text -