Nested If then with VLOOKUP trouble

[PAMR]

Need a formula to look at the value in column Z, which
has 14 variables, then look at the value in column AA,
which has 5 variables, then depending on the combination
look up the corresponding value which resides on a
different sheet of the workbook and place that value in
column AC.

Before I had so many variables in column Z, I was able to
get the job done with this formula
=IF(Z2="1",VLOOKUP(AA2,'Sheet 4'!$B$14:$H$18,4),IF
(Z2="1D",VLOOKUP(AA2, 'Sheet 4'!$B$14:$H$18,5),IF
(Z2="2",VLOOKUP(AA2,''Sheet 4'!$B$14:$H$18,6),IF
(Z2="2D",VLOOKUP(AA2, 'Sheet 4'!$B$14:$H$18,7),"CHECK COL
Z & AA"))))

 

[Guest]

I'm not sure what your environment is, but here's what I'd do in your
situation.

I would create a seperate sheet containing the lookup data for column AA.
For instance (I'm making up value for the "DATA" column):

Z_VALUE DATA
---------------------------
1 A
2 B
1D C
....

You can repeat that for all 14 values.

Then you'd put the following function in column AA:

=VLOOKUP(Z2,'Data Match Sheet'!$A:$B,2)

That should put the right value in your column AA.

Then I would put the following function in column AB:

=Z2&AA2

This would generate values like this:

1A
2B
1DC
.... etc.

Use the value in that column for your other lookup.

 

[Don Guillett]

try using lookup to get your 4,5,6,7

=LOOKUP(D1,{"a","b","c","d";1,2,3,4})
vlookup(aa2,yourrange,lookup(z2,etc

 

[PAMR]

I don't think that will accomplish the task. The look up
table has the 14(group level) Z values in columns and the
5 z values (tier within group) in the rows.

The look up table has values of AA in columns and value
of z in rows the price associated with the specific
group/tier is the lookup result. I don't think I can
flip flop the values because the $ varys first with which
group a participant is in, then what level of
particpation they have chosen.


A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D
AA $ $ $ $ $ $ $ $ $ $ $ $ $ $
BB
CC
DD
EE

 

[PAMR]

I think I understand what you are getting at, but I am
not sure how to write it. Can you give me a clearer
example?

 

[Guest]

I see. Try this for Column AC:

=VLOOKUP(AA2,'yourlookupsheet'!$A:$N,MATCH(Z2,'yourlookupsheet'!$A$1:$N$1))

I'm going to deconstruct it from right to left.

MATCH(Z2,'yourlookupsheet'!$A$1:$N$1) returns a reference number looking for
the value in column Z2, looking at the range A$1:N$1. I'm assuming that
A$1:N$1 is this:

[empty cell A1] A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D

So, MATCH("A",'yourlookupsheet'!$A$1:$N$1) would return 2.
MATCH("2D",'yourlookupsheet'!$A$1:$N$1) would return 7.

VLOOKUP(AA2,'yourlookupsheet'!$A:$N...) looks at the value in column AA2
(values such as "AA","BB","CC", etc) and returns a value from range A:N,
where you tell it how many columns to go. The result of the MATCH function
should feed you that info.

HTH

 

[Guest]

Rereading my post, I may have switched column Z and column AA from the way
you actually use them. My example was based on a sheet called
'yourlookupsheet' that was organized like this:

A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D
AA $ $ $ $ $ $ $ $ $ $ $ $ $ $
BB
CC
DD
EE

Looking at your code, though, it looks like your actual sheet may look like
this:

AA BB CC DD EE
A $ $ $ $ $
B
1
1D
2
2D

My example would still work, you'd just need to flip the references for Z2
and AA2, and change the MATCH() function to look in A$1:H$1.

Hope I didn't confuse things more!

I see. Try this for Column AC:

=VLOOKUP(AA2,'yourlookupsheet'!$A:$N,MATCH(Z2,'yourlookupsheet'!$A$1:$N$1))

I'm going to deconstruct it from right to left.

MATCH(Z2,'yourlookupsheet'!$A$1:$N$1) returns a reference number looking for
the value in column Z2, looking at the range A$1:N$1. I'm assuming that
A$1:N$1 is this:

[empty cell A1] A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D

So, MATCH("A",'yourlookupsheet'!$A$1:$N$1) would return 2.
MATCH("2D",'yourlookupsheet'!$A$1:$N$1) would return 7.

VLOOKUP(AA2,'yourlookupsheet'!$A:$N...) looks at the value in column AA2
(values such as "AA","BB","CC", etc) and returns a value from range A:N,
where you tell it how many columns to go. The result of the MATCH function
should feed you that info.

HTH

I don't think that will accomplish the task. The look up
table has the 14(group level) Z values in columns and the
5 z values (tier within group) in the rows.

The look up table has values of AA in columns and value
of z in rows the price associated with the specific
group/tier is the lookup result. I don't think I can
flip flop the values because the $ varys first with which
group a participant is in, then what level of
particpation they have chosen.


A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D
AA $ $ $ $ $ $ $ $ $ $ $ $ $ $
BB
CC
DD
EE

 

[PAMR]

You had it correct the first time. But I am not getting
the correct answer for the code.

-----Original Message-----
Rereading my post, I may have switched column Z and column AA from the way
you actually use them. My example was based on a sheet called
'yourlookupsheet' that was organized like this:

A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D
AA $ $ $ $ $ $ $ $ $ $ $ $ $ $
BB
CC
DD
EE

Looking at your code, though, it looks like your actual sheet may look like
this:

AA BB CC DD EE
A $ $ $ $ $
B
1
1D
2
2D

My example would still work, you'd just need to flip the references for Z2
and AA2, and change the MATCH() function to look in A$1:H$1.

Hope I didn't confuse things more!

I see. Try this for Column AC:

=VLOOKUP(AA2,'yourlookupsheet'!$A:$N,MATCH (Z2,'yourlookupsheet'!$A$1:$N$1))

I'm going to deconstruct it from right to left.

MATCH(Z2,'yourlookupsheet'!$A$1:$N$1) returns a reference number looking for
the value in column Z2, looking at the range A$1:N$1. I'm assuming that
A$1:N$1 is this:

[empty cell A1] A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D

So, MATCH("A",'yourlookupsheet'!$A$1:$N$1) would return 2.
MATCH("2D",'yourlookupsheet'!$A$1:$N$1) would return 7.

VLOOKUP(AA2,'yourlookupsheet'!$A:$N...) looks at the value in column AA2
(values such as "AA","BB","CC", etc) and returns a value from range A:N,
where you tell it how many columns to go. The result of the MATCH function
should feed you that info.

HTH

I don't think that will accomplish the task. The look up
table has the 14(group level) Z values in columns and the
5 z values (tier within group) in the rows.

The look up table has values of AA in columns and value
of z in rows the price associated with the specific
group/tier is the lookup result. I don't think I can
flip flop the values because the $ varys first with which
group a participant is in, then what level of
particpation they have chosen.


A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D
AA $ $ $ $ $ $ $ $ $ $ $ $ $ $
BB
CC
DD
EE

-----Original Message-----
I'm not sure what your environment is, but here's what
I'd do in your
situation.

I would create a seperate sheet containing the lookup
data for column AA.
For instance (I'm making up value for the "DATA" column):

Z_VALUE DATA
---------------------------
1 A
2 B
1D C
....

You can repeat that for all 14 values.

Then you'd put the following function in column AA:

=VLOOKUP(Z2,'Data Match Sheet'!$A:$B,2)

That should put the right value in your column AA.

Then I would put the following function in column AB:

=Z2&AA2

This would generate values like this:

1A
2B
1DC
.... etc.

Use the value in that column for your other lookup.

:

Need a formula to look at the value in column Z, which
has 14 variables, then look at the value in column AA,
which has 5 variables, then depending on the
combination
look up the corresponding value which resides on a
different sheet of the workbook and place that value
in
column AC.

Before I had so many variables in column Z, I was able
to
get the job done with this formula
=IF(Z2="1",VLOOKUP(AA2,'Sheet 4'!$B$14:$H$18,4),IF
(Z2="1D",VLOOKUP(AA2, 'Sheet 4'!$B$14:$H$18,5),IF
(Z2="2",VLOOKUP(AA2,''Sheet 4'!$B$14:$H$18,6),IF
(Z2="2D",VLOOKUP(AA2, 'Sheet 4'! $B$14:$H$18,7),"CHECK
COL
Z & AA"))))





.

.

 

[PAMR]

I can't paste the table into this text box, but I priced
the sample look up table 1(answer to intersection of A
with AA),2(A with BB),3(A with CC),4(A with DD),5( A with
EE)6 (B with AA), 7 (B with BB), etc.

-----Original Message-----
Rereading my post, I may have switched column Z and column AA from the way
you actually use them. My example was based on a sheet called
'yourlookupsheet' that was organized like this:

A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D
AA $ $ $ $ $ $ $ $ $ $ $ $ $ $
BB
CC
DD
EE

Looking at your code, though, it looks like your actual sheet may look like
this:

AA BB CC DD EE
A $ $ $ $ $
B
1
1D
2
2D

My example would still work, you'd just need to flip the references for Z2
and AA2, and change the MATCH() function to look in A$1:H$1.

Hope I didn't confuse things more!

I see. Try this for Column AC:

=VLOOKUP(AA2,'yourlookupsheet'!$A:$N,MATCH (Z2,'yourlookupsheet'!$A$1:$N$1))

I'm going to deconstruct it from right to left.

MATCH(Z2,'yourlookupsheet'!$A$1:$N$1) returns a reference number looking for
the value in column Z2, looking at the range A$1:N$1. I'm assuming that
A$1:N$1 is this:

[empty cell A1] A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D

So, MATCH("A",'yourlookupsheet'!$A$1:$N$1) would return 2.
MATCH("2D",'yourlookupsheet'!$A$1:$N$1) would return 7.

VLOOKUP(AA2,'yourlookupsheet'!$A:$N...) looks at the value in column AA2
(values such as "AA","BB","CC", etc) and returns a value from range A:N,
where you tell it how many columns to go. The result of the MATCH function
should feed you that info.

HTH

I don't think that will accomplish the task. The look up
table has the 14(group level) Z values in columns and the
5 z values (tier within group) in the rows.

The look up table has values of AA in columns and value
of z in rows the price associated with the specific
group/tier is the lookup result. I don't think I can
flip flop the values because the $ varys first with which
group a participant is in, then what level of
particpation they have chosen.


A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D
AA $ $ $ $ $ $ $ $ $ $ $ $ $ $
BB
CC
DD
EE

-----Original Message-----
I'm not sure what your environment is, but here's what
I'd do in your
situation.

I would create a seperate sheet containing the lookup
data for column AA.
For instance (I'm making up value for the "DATA" column):

Z_VALUE DATA
---------------------------
1 A
2 B
1D C
....

You can repeat that for all 14 values.

Then you'd put the following function in column AA:

=VLOOKUP(Z2,'Data Match Sheet'!$A:$B,2)

That should put the right value in your column AA.

Then I would put the following function in column AB:

=Z2&AA2

This would generate values like this:

1A
2B
1DC
.... etc.

Use the value in that column for your other lookup.

:

Need a formula to look at the value in column Z, which
has 14 variables, then look at the value in column AA,
which has 5 variables, then depending on the
combination
look up the corresponding value which resides on a
different sheet of the workbook and place that value
in
column AC.

Before I had so many variables in column Z, I was able
to
get the job done with this formula
=IF(Z2="1",VLOOKUP(AA2,'Sheet 4'!$B$14:$H$18,4),IF
(Z2="1D",VLOOKUP(AA2, 'Sheet 4'!$B$14:$H$18,5),IF
(Z2="2",VLOOKUP(AA2,''Sheet 4'!$B$14:$H$18,6),IF
(Z2="2D",VLOOKUP(AA2, 'Sheet 4'! $B$14:$H$18,7),"CHECK
COL
Z & AA"))))





.

.

 

[Don Guillett]

NOT tested
vlookup(aa2,yourrange,LOOKUP(z2,{"1","1d","2","2d";4,5,6,7}))

 

[PAR]

TRIED THIS, BUT I GET FORMULA CONTAINS AN ERROR MESSAGE.

 

[kraljb]

Not sure if you have room to do this or not, but I would suggest makin
a dummy table. The table being:

ZData, Num
1, 4
d1, 5
etc.

Then do vlookup(AA2,LookupSheet!$A:AB,vlookup(z2,dummytablerange,2)

 

[Guest]

What's the function you're currently using?

You had it correct the first time. But I am not getting
the correct answer for the code.

-----Original Message-----
Rereading my post, I may have switched column Z and column AA from the way
you actually use them. My example was based on a sheet called
'yourlookupsheet' that was organized like this:

A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D
AA $ $ $ $ $ $ $ $ $ $ $ $ $ $
BB
CC
DD
EE

Looking at your code, though, it looks like your actual sheet may look like
this:

AA BB CC DD EE
A $ $ $ $ $
B
1
1D
2
2D

My example would still work, you'd just need to flip the references for Z2
and AA2, and change the MATCH() function to look in A$1:H$1.

Hope I didn't confuse things more!

I see. Try this for Column AC:

=VLOOKUP(AA2,'yourlookupsheet'!$A:$N,MATCH (Z2,'yourlookupsheet'!$A$1:$N$1))

I'm going to deconstruct it from right to left.

MATCH(Z2,'yourlookupsheet'!$A$1:$N$1) returns a reference number looking for
the value in column Z2, looking at the range A$1:N$1. I'm assuming that
A$1:N$1 is this:

[empty cell A1] A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D

So, MATCH("A",'yourlookupsheet'!$A$1:$N$1) would return 2.
MATCH("2D",'yourlookupsheet'!$A$1:$N$1) would return 7.

VLOOKUP(AA2,'yourlookupsheet'!$A:$N...) looks at the value in column AA2
(values such as "AA","BB","CC", etc) and returns a value from range A:N,
where you tell it how many columns to go. The result of the MATCH function
should feed you that info.

HTH

:

I don't think that will accomplish the task. The look up
table has the 14(group level) Z values in columns and the
5 z values (tier within group) in the rows.

The look up table has values of AA in columns and value
of z in rows the price associated with the specific
group/tier is the lookup result. I don't think I can
flip flop the values because the $ varys first with which
group a participant is in, then what level of
particpation they have chosen.


A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D
AA $ $ $ $ $ $ $ $ $ $ $ $ $ $
BB
CC
DD
EE

-----Original Message-----
I'm not sure what your environment is, but here's what
I'd do in your
situation.

I would create a seperate sheet containing the lookup
data for column AA.
For instance (I'm making up value for the "DATA" column):

Z_VALUE DATA
---------------------------
1 A
2 B
1D C
....

You can repeat that for all 14 values.

Then you'd put the following function in column AA:

=VLOOKUP(Z2,'Data Match Sheet'!$A:$B,2)

That should put the right value in your column AA.

Then I would put the following function in column AB:

=Z2&AA2

This would generate values like this:

1A
2B
1DC
.... etc.

Use the value in that column for your other lookup.

:

Need a formula to look at the value in column Z, which
has 14 variables, then look at the value in column AA,
which has 5 variables, then depending on the
combination
look up the corresponding value which resides on a
different sheet of the workbook and place that value
in
column AC.

Before I had so many variables in column Z, I was able
to
get the job done with this formula
=IF(Z2="1",VLOOKUP(AA2,'Sheet 4'!$B$14:$H$18,4),IF
(Z2="1D",VLOOKUP(AA2, 'Sheet 4'!$B$14:$H$18,5),IF
(Z2="2",VLOOKUP(AA2,''Sheet 4'!$B$14:$H$18,6),IF
(Z2="2D",VLOOKUP(AA2, 'Sheet 4'! $B$14:$H$18,7),"CHECK
COL
Z & AA"))))





.

.

 

[Don Guillett]

It should work if you do this or as before with z2 formatted as TEXT.

'=LOOKUP(D1,{1,"1d",2,"2d";1,2,3,4})

 

[Guest]

I don't understand what you mean when you say you "priced the sample".

When you say that you're not getting the right answer, what do you mean?
Are you getting something like "#NAME!" (which would indicate that there's a
problem with the structure of the function or something), or are you getting
the wrong answer?

I can't paste the table into this text box, but I priced
the sample look up table 1(answer to intersection of A
with AA),2(A with BB),3(A with CC),4(A with DD),5( A with
EE)6 (B with AA), 7 (B with BB), etc.

-----Original Message-----
Rereading my post, I may have switched column Z and column AA from the way
you actually use them. My example was based on a sheet called
'yourlookupsheet' that was organized like this:

A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D
AA $ $ $ $ $ $ $ $ $ $ $ $ $ $
BB
CC
DD
EE

Looking at your code, though, it looks like your actual sheet may look like
this:

AA BB CC DD EE
A $ $ $ $ $
B
1
1D
2
2D

My example would still work, you'd just need to flip the references for Z2
and AA2, and change the MATCH() function to look in A$1:H$1.

Hope I didn't confuse things more!

I see. Try this for Column AC:

=VLOOKUP(AA2,'yourlookupsheet'!$A:$N,MATCH (Z2,'yourlookupsheet'!$A$1:$N$1))

I'm going to deconstruct it from right to left.

MATCH(Z2,'yourlookupsheet'!$A$1:$N$1) returns a reference number looking for
the value in column Z2, looking at the range A$1:N$1. I'm assuming that
A$1:N$1 is this:

[empty cell A1] A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D

So, MATCH("A",'yourlookupsheet'!$A$1:$N$1) would return 2.
MATCH("2D",'yourlookupsheet'!$A$1:$N$1) would return 7.

VLOOKUP(AA2,'yourlookupsheet'!$A:$N...) looks at the value in column AA2
(values such as "AA","BB","CC", etc) and returns a value from range A:N,
where you tell it how many columns to go. The result of the MATCH function
should feed you that info.

HTH

:

I don't think that will accomplish the task. The look up
table has the 14(group level) Z values in columns and the
5 z values (tier within group) in the rows.

The look up table has values of AA in columns and value
of z in rows the price associated with the specific
group/tier is the lookup result. I don't think I can
flip flop the values because the $ varys first with which
group a participant is in, then what level of
particpation they have chosen.


A B 1 1D 2 2D U97 U98 U99 U00 BU1 BU1D BU2 BU2D
AA $ $ $ $ $ $ $ $ $ $ $ $ $ $
BB
CC
DD
EE

-----Original Message-----
I'm not sure what your environment is, but here's what
I'd do in your
situation.

I would create a seperate sheet containing the lookup
data for column AA.
For instance (I'm making up value for the "DATA" column):

Z_VALUE DATA
---------------------------
1 A
2 B
1D C
....

You can repeat that for all 14 values.

Then you'd put the following function in column AA:

=VLOOKUP(Z2,'Data Match Sheet'!$A:$B,2)

That should put the right value in your column AA.

Then I would put the following function in column AB:

=Z2&AA2

This would generate values like this:

1A
2B
1DC
.... etc.

Use the value in that column for your other lookup.

:

Need a formula to look at the value in column Z, which
has 14 variables, then look at the value in column AA,
which has 5 variables, then depending on the
combination
look up the corresponding value which resides on a
different sheet of the workbook and place that value
in
column AC.

Before I had so many variables in column Z, I was able
to
get the job done with this formula
=IF(Z2="1",VLOOKUP(AA2,'Sheet 4'!$B$14:$H$18,4),IF
(Z2="1D",VLOOKUP(AA2, 'Sheet 4'!$B$14:$H$18,5),IF
(Z2="2",VLOOKUP(AA2,''Sheet 4'!$B$14:$H$18,6),IF
(Z2="2D",VLOOKUP(AA2, 'Sheet 4'! $B$14:$H$18,7),"CHECK
COL
Z & AA"))))





.

.