If, And & Or In Same Formula
 

Need help in figuring out formula.
Cell 1 is a drop down between a choice of 2 regions (A or B)
Cell 2 is another drop down between a choice of 2 term lengths (3 or 6)
L8=$2200.00; L9=$2500.00; L10=$2000.00; L11=$2100.00

If Cell 1 is A, and Cell 2 is 3, then L8 ......If Cell 1 is A, and Cell 2 is 6, then L10......if Cell 1 is B, and Cell 2 is 3, then L9......if Cell 1 is B, and Cell 2 is 6, then L11

How do Iwrite this formula for this 1 cell?

If, And & Or In Same Formula
 

Hi Jennifer,

Am Fri, 5 Oct 2018 17:00:53 +0100 schrieb Jennifer Briles:

Need help in figuring out formula.
Cell 1 is a drop down between a choice of 2 regions (A or B)
Cell 2 is another drop down between a choice of 2 term lengths (3 or 6)
L8=$2200.00; L9=$2500.00; L10=$2000.00; L11=$2100.00

If Cell 1 is A, and Cell 2 is 3, then L8 ......If Cell 1 is A, and Cell
2 is 6, then L10......if Cell 1 is B, and Cell 2 is 3, then L9......if
Cell 1 is B, and Cell 2 is 6, then L11

Cell1 = G1, Cell2 = G2.
Then try:
=IF(G1&G2="A3",L8,IF(G1&G2="A6",L10,IF(G1&G2="B3", L9,IF(G1&G2="B6",L11,""))))
or
=INDIRECT(VLOOKUP(G1&G2,{"A3","L8";"A6","L10";"B3" ,"L9";"B6","L11"},2,0))


Regards
Claus B.
--
Windows10
Office 2016

If, And & Or In Same Formula
 

Hi Jennifer,

Am Fri, 5 Oct 2018 19:10:09 +0200 schrieb Claus Busch:

Cell1 = G1, Cell2 = G2.
Then try:
=IF(G1&G2="A3",L8,IF(G1&G2="A6",L10,IF(G1&G2="B3", L9,IF(G1&G2="B6",L11,""))))
or
=INDIRECT(VLOOKUP(G1&G2,{"A3","L8";"A6","L10";"B3" ,"L9";"B6","L11"},2,0))

or a bit shorter:
=INDEX(L8:L11,VLOOKUP(G1&G2,{"A3",1;"B3",2;"A6",3; "B6",4},2,0))


Regards
Claus B.
--
Windows10
Office 2016

If, And & Or In Same Formula
 

Hi again,

Cell1 = G1, Cell2 = G2.
Then try:
=IF(G1&G2="A3",L8,IF(G1&G2="A6",L10,IF(G1&G2="B3", L9,IF(G1&G2="B6",L11,""))))
or
=INDIRECT(VLOOKUP(G1&G2,{"A3","L8";"A6","L10";"B3" ,"L9";"B6","L11"},2,0))

or a bit shorter:
=INDEX(L8:L11,VLOOKUP(G1&G2,{"A3",1;"B3",2;"A6",3; "B6",4},2,0))

20 characters shorter:
=INDEX(L8:L11,FIND(G1&G2,"A3B3A6B6")/2+0.5)


Regards
Claus B.
--
Windows10
Office 2016