Hello;
I have two dropdowns that I want to use as criteria of a lookup formula.
Based on user selections, I want the formula to lookup those values on a good
sized table on another sheet. The first drop down has six options (tiers 1-6)
and the second dropdown of usage values that has 3 values (teaching, admin,
research).

I have not been able to find a way to do that without having to do an if
formula for each of the multiple permutations of the two dropdowns: if(tier1
and teaching, (vlookup range), if(tier 2 and teaching...

I have to believe there is a better, more elegant way, but several hours of
looking hasn't found it, so I was hoping you gurus could help. Thanks in
advance.

Assuming your dopdown criteria in C1 & D1

=INDEX(C1:C100,MATCH(1,(A1:A100=C1)*(B1:B100=D1),0 )

ctrl+shift+enter, not just enter
Adjust to suit your needed


"price guy" wrote:

Hello;
I have two dropdowns that I want to use as criteria of a lookup formula.
Based on user selections, I want the formula to lookup those values on a good
sized table on another sheet. The first drop down has six options (tiers 1-6)
and the second dropdown of usage values that has 3 values (teaching, admin,
research).

I have not been able to find a way to do that without having to do an if
formula for each of the multiple permutations of the two dropdowns: if(tier1
and teaching, (vlookup range), if(tier 2 and teaching...

I have to believe there is a better, more elegant way, but several hours of
looking hasn't found it, so I was hoping you gurus could help. Thanks in
advance.

thank you.

Regarding the ranges you have C1:100, A1:A100 etc,

columns a,b, c would be which variable? the drop down with three options?

thanks



"Teethless mama" wrote:

Assuming your dopdown criteria in C1 & D1

=INDEX(C1:C100,MATCH(1,(A1:A100=C1)*(B1:B100=D1),0 )

ctrl+shift+enter, not just enter
Adjust to suit your needed


"price guy" wrote:

Hello;
I have two dropdowns that I want to use as criteria of a lookup formula.
Based on user selections, I want the formula to lookup those values on a good
sized table on another sheet. The first drop down has six options (tiers 1-6)
and the second dropdown of usage values that has 3 values (teaching, admin,
research).

I have not been able to find a way to do that without having to do an if
formula for each of the multiple permutations of the two dropdowns: if(tier1
and teaching, (vlookup range), if(tier 2 and teaching...

I have to believe there is a better, more elegant way, but several hours of
looking hasn't found it, so I was hoping you gurus could help. Thanks in
advance.

Perhaps this would help clarify Teethless Mama's earlier response a little
further ...

Assume your "good sized" table is in Sheet2, where A1:A100 and B1:B100
contain values corresponding to the values within the 2 dropdowns (in say,
Sheet1's C1 and D1 respectively), and the value to be returned is from C1:C100

Then in Sheet1,
with the dropdowns in C1 and D1

you could use something like this in say, E1:
=INDEX(Sheet2!C1:C100,MATCH(1,(Sheet2!A1:A100=C1)* (Sheet2!B1:B100=D1),0))

Remember to array enter the formula with CTRL+SHIFT+ENTER
(instead of just pressing ENTER)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"price guy" wrote:
.. Regarding the ranges you have C1:100, A1:A100 etc,
columns a,b, c would be which variable? the drop down with three options?

thanks for the clarification Max.

it did not work for me so I wonder what I am doing wrongI think I am
conveying how my data is set up incorrectly or I am not expressing myself in
what I want to do.

my understanding is that if using Index and specifying both column and row
(in this case with a Match function to determine row and column) the result
will give the intersection of the column and row.

Maybe my data is not set up correctly? For sake of brevity, here is a small
sample of how my data is set up on sheet 2.
Column A B C D
Tier Administration Research Teaching
1 $1,000 $500 $250
2 $2,000 $1,000 $500
3 $3,000 $1,500 $750
4 $4,000 $2,000 $1,000
5 $5,000 $2,500 $1,250
6 $6,000 $3,000 $1,500

Then when a user selects from a dropdown list Tier and usage on sheet 1,
cell D1 and D2 respectively (let's say tier 3 and research), I want the
result in the formula cell D4 in sheet 1 to show $1500.

so not sure what I am doing wrong here.

Thanks




"Max" wrote:

Perhaps this would help clarify Teethless Mama's earlier response a little
further ...

Assume your "good sized" table is in Sheet2, where A1:A100 and B1:B100
contain values corresponding to the values within the 2 dropdowns (in say,
Sheet1's C1 and D1 respectively), and the value to be returned is from C1:C100

Then in Sheet1,
with the dropdowns in C1 and D1

you could use something like this in say, E1:
=INDEX(Sheet2!C1:C100,MATCH(1,(Sheet2!A1:A100=C1)* (Sheet2!B1:B100=D1),0))

Remember to array enter the formula with CTRL+SHIFT+ENTER
(instead of just pressing ENTER)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"price guy" wrote:
.. Regarding the ranges you have C1:100, A1:A100 etc,
columns a,b, c would be which variable? the drop down with three options?

Aha, now that's a good clarification you gave there ..
(specifics should always be provided in your queries <g)

Ok, assuming the reference table in A1:D7 in Sheet2,

In Sheet1,

D1 contains: 3
D2 contains: Research

then this in D4 (normal ENTER will do):
=IF(COUNTA(D1:D2)<2,"",INDEX(Sheet2!$A$1:$D$7,MAT CH(D1,Sheet2!$A$1:$A$7,0),MATCH(D2,Sheet2!$A$1:$D$ 1,0)))

D4 would return the required result (1500)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"price guy" wrote:
thanks for the clarification Max.

it did not work for me so I wonder what I am doing wrong I think I am
conveying how my data is set up incorrectly or I am not expressing myself in
what I want to do.

my understanding is that if using Index and specifying both column and row
(in this case with a Match function to determine row and column) the result
will give the intersection of the column and row.

Maybe my data is not set up correctly? For sake of brevity, here is a small
sample of how my data is set up on sheet 2.
Column A B C D
Tier Administration Research Teaching
1 $1,000 $500 $250
2 $2,000 $1,000 $500
3 $3,000 $1,500 $750
4 $4,000 $2,000 $1,000
5 $5,000 $2,500 $1,250
6 $6,000 $3,000 $1,500

Then when a user selects from a dropdown list Tier and usage on sheet 1,
cell D1 and D2 respectively (let's say tier 3 and research), I want the
result in the formula cell D4 in sheet 1 to show $1500.

so not sure what I am doing wrong here.

Thanks

In Sheet1,

Should D1 actually contain the text "Tier" as well,
viz. in D1: Tier 3
(instead of just the number 3)

then use this version in D4:
=IF(COUNTA(D1:D2)<2,"",INDEX(Sheet2!$A$1:$D$7,MAT CH(SUBSTITUTE(D1,"Tier","")+0,Sheet2!$A$1:$A$7,0), MATCH(D2,Sheet2!$A$1:$D$1,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---