Is an if formula the best way to look through a list of values?

[Musa15]

I would need to write a formula that looks at cell with total cost value of $17812.61 and then references this table and points to 6.5%, what is the best way to achieve this? I was thinking an if formula might be too long as it checks for each percentage scenario but would appreciate any sugegstions.

thank you,

Rate Range		Annual Collections	Monthly Collections
11.00%		362,714.07	30,226.17
10.75%		354,470.57	29,539.21
10.50%		346,227.07	28,852.26
10.25%		337,983.57	28,165.30
10.00%		329,740.07	27,478.34
9.75%		321,496.57	26,791.38
9.50%		313,253.06	26,104.42
9.25%		305,009.56	25,417.46
9.00%		296,766.06	24,730.51
8.75%		288,522.56	24,043.55
8.50%		280,279.06	23,356.59
8.25%		272,035.56	22,669.63
8.00%		263,792.05	21,982.67
7.75%		255,548.55	21,295.71
7.50%		247,305.05	20,608.75
7.25%		239,061.55	19,921.80
7.00%		230,818.05	19,234.84
6.75%		222,574.55	18,547.88
6.50%		214,331.04	17,860.92
6.25%		206,087.54	17,173.96
6.00%		197,844.04	16,487.00
5.75%		189,600.54	15,800.04
5.50%		181,357.04	15,113.09
5.25%		173,113.54	14,426.13
5.00%		164,870.03	13,739.17
4.75%		156,626.53	13,052.21
4.50%		148,383.03	12,365.25
4.25%		140,139.53	11,678.29
4.00%		131,896.03	10,991.34
3.75%		123,652.53	10,304.38
3.50%		115,409.02	9,617.42
3.25%		107,165.52	8,930.46
3.00%		98,922.02	8,243.50
2.75%		90,678.52	7,556.54
2.50%		82,435.02	6,869.58
2.25%		74,191.52	6,182.63
2.00%		65,948.01	5,495.67

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Musa15]

Actually, points to 6.25% since the monthly collections of $17812.61>=17,173.96

 

[Eric W]

You can do something like this:

Excel 2010

	A	B	C	D	E	F	G
1	Rate Range		Annual Collections	Monthly Collections		Value	$ 17,812.61
2	11.00%			999999		Rate	6.25%
3	11.00%		362,714.07	30,226.17
4	10.75%		354,470.57	29,539.21
5	10.50%		346,227.07	28,852.26
6	10.25%		337,983.57	28,165.30
7	10.00%		329,740.07	27,478.34
8	9.75%		321,496.57	26,791.38
9	9.50%		313,253.06	26,104.42
10	9.25%		305,009.56	25,417.46
11	9.00%		296,766.06	24,730.51
12	8.75%		288,522.56	24,043.55
13	8.50%		280,279.06	23,356.59
14	8.25%		272,035.56	22,669.63
15	8.00%		263,792.05	21,982.67
16	7.75%		255,548.55	21,295.71
17	7.50%		247,305.05	20,608.75
18	7.25%		239,061.55	19,921.80
19	7.00%		230,818.05	19,234.84
20	6.75%		222,574.55	18,547.88
21	6.50%		214,331.04	17,860.92
22	6.25%		206,087.54	17,173.96
23	6.00%		197,844.04	16,487.00
24	5.75%		189,600.54	15,800.04
25	5.50%		181,357.04	15,113.09
26	5.25%		173,113.54	14,426.13
27	5.00%		164,870.03	13,739.17
28	4.75%		156,626.53	13,052.21
29	4.50%		148,383.03	12,365.25
30	4.25%		140,139.53	11,678.29
31	4.00%		131,896.03	10,991.34
32	3.75%		123,652.53	10,304.38
33	3.50%		115,409.02	9,617.42
34	3.25%		107,165.52	8,930.46
35	3.00%		98,922.02	8,243.50
36	2.75%		90,678.52	7,556.54
37	2.50%		82,435.02	6,869.58
38	2.25%		74,191.52	6,182.63
39	2.00%		65,948.01	5,495.67
40

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula G2 =INDEX(A3:A40,MATCH(G1,D2:D39,-1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I added an extra row on the top so that the MATCH works properly. But an INDEX/MATCH works better than a lot of IFs.

Let me know if this helps.

 

[Musa15]

Hi Eric,

This looks perfect, would you be able to explain what the -1 in the formula means/does?

Thank you!

 

[Eric W]

Sure! The -1 just tells the MATCH function that the list you're seaching (D2:D39) is sorted in descending order.

You might also note that the INDEX range is offset by 1, (A3:A40). This is so that it gets the next line in the list, otherwise you'd get the 6.5% in your example. Also, the way it's set up, the number in the D column represents the top of the range. In other words, 17,860.92 will get the 6.25% rate.

Hope this helps!

 

[Musa15]

This makes sense what is the 999999 number for?

 

[Eric W]

In case you have a value greater than 30,226.17. I put a number greater than anything I thought you'd encounter. Without the 999999, and you had a value of 31,000, it would have caused an error. This way a number of 31,000 will default to the A3 value.

The Excel lookup functions are really nice, but you do have to make sure you obey the rules, especially at borders.