Two condition formula help

[fixcpa]

I am trying to create a formula that will autmatically enter an answer based on two conditions. The first condition is from a choice of 15 from a drop down list. The second condition is from a choice of 6 from a drop down list. Based on the conditions chosen, the formula should pick an answer from a chart (i.e. Condition 1 is "20,000 to 30,000", and condition 2 is 3, the formula will return 627). Below is
a copy of the information.


"At But
least" less
than" 1 2 3 4 5 "Over5"

$0 $20,000 394 450 487 515 537 569
20,000 30,000 509 580 627 662 691 731
30,000 40,000 585 666 720 760 793 838
40,000 50,000 650 740 799 843 880 930
50,000 60,000 709 806 870 918 957 1012
60,000 70,000 761 865 933 984 1027 1085
70,000 80,000 810 920 992 1047 1092 1153
80,000 90,000 854 970 1045 1103 1150 1215
90,000 100,000 896 1018 1097 1157 1206 1274
100,000 120,000 952 1080 1164 1228 1280 1351
120,000 140,000 1026 1164 1254 1322 1378 1455
140,000 160,000 1092 1238 1333 1406 1465 1546
160,000 180,000 1155 1309 1409 1485 1547 1633
180,000 200,000 1214 1375 1480 1560 1625 1714
200,000 or more 1474 1667 1792 1888 1965 2073

Please note that I am new to this forum and that I am a novice when it comes to excel. Any help is appreciated.

[Domenic]

Here's one approach you may want to consider...

First, combine the first two columns into one so that the values match the values in your first drop down list, for example...

Column A
------------

0 to 20,000
20,000 to 30,000
30,000 to 40,000
40,000 to 50,000
50,000 to 60,000
60,000 to 70,000
70,000 to 80,000
80,000 to 90,000
90,000 to 100,000
100,000 to 120,000
120,000 to 140,000
140,000 to 160,000
160,000 to 180,000
180,000 to 200,000
200,000 or more

Your table should look like this...

Please Login or Register  to view this content.

Then use the following formula...

=INDEX(B2:G16,MATCH(A20,A2:A16,0),MATCH(B20,B1:G1,0))

...where A20 contains your first condition from your drop down list and B20 contains your second condition from your drop down list.

Hope this helps!

I am trying to create a formula that will autmatically enter an answer based on two conditions. The first condition is from a choice of 15 from a drop down list. The second condition is from a choice of 6 from a drop down list. Based on the conditions chosen, the formula should pick an answer from a chart (i.e. Condition 1 is "20,000 to 30,000", and condition 2 is 3, the formula will return 627). Below is
a copy of the information.


"At But
least" less
than" 1 2 3 4 5 "Over5"

$0 $20,000 394 450 487 515 537 569
20,000 30,000 509 580 627 662 691 731
30,000 40,000 585 666 720 760 793 838
40,000 50,000 650 740 799 843 880 930
50,000 60,000 709 806 870 918 957 1012
60,000 70,000 761 865 933 984 1027 1085
70,000 80,000 810 920 992 1047 1092 1153
80,000 90,000 854 970 1045 1103 1150 1215
90,000 100,000 896 1018 1097 1157 1206 1274
100,000 120,000 952 1080 1164 1228 1280 1351
120,000 140,000 1026 1164 1254 1322 1378 1455
140,000 160,000 1092 1238 1333 1406 1465 1546
160,000 180,000 1155 1309 1409 1485 1547 1633
180,000 200,000 1214 1375 1480 1560 1625 1714
200,000 or more 1474 1667 1792 1888 1965 2073

Please note that I am new to this forum and that I am a novice when it comes to excel. Any help is appreciated.