Remember Me?

 Jennifer Briles Junior Member First recorded activity by ExcelBanter: Oct 2018 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? October 5th 18, 06:10 PM posted to microsoft.public.excel.worksheet.functions
 Claus Busch external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,787 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 October 5th 18, 07:15 PM posted to microsoft.public.excel.worksheet.functions
 Claus Busch external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,787 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 October 6th 18, 03:29 PM posted to microsoft.public.excel.worksheet.functions
 Claus Busch external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,787 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post [email protected] Excel Discussion (Misc queries) 1 September 7th 12 09:15 PM solardirect Links and Linking in Excel 6 June 4th 12 10:47 PM PJ[_3_] Excel Worksheet Functions 2 June 2nd 10 03:45 PM [email protected] Excel Programming 1 July 20th 06 07:58 PM Muxer Excel Programming 2 July 24th 03 01:02 AM

All times are GMT +1. The time now is 12:38 AM. Copyright ©2004-2020 ExcelBanter.