Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula autofill to cells below – formula arguments are cell areas in different columns | Excel Discussion (Misc queries) | |||
Build formula using field values as text in the formula referencing another workbook | Links and Linking in Excel | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |