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,

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

 

[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
Rate

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Rate Range[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Annual Collections[/TD]
[TD="bgcolor: #FAFAFA, align: right"]Monthly Collections[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Value[/TD]
[TD="align: right"] $ 17,812.61 [/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]11.00%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]999999[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6.25%[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]11.00%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]362,714.07[/TD]
[TD="bgcolor: #FAFAFA, align: right"]30,226.17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10.75%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]354,470.57[/TD]
[TD="bgcolor: #FAFAFA, align: right"]29,539.21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10.50%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]346,227.07[/TD]
[TD="bgcolor: #FAFAFA, align: right"]28,852.26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10.25%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]337,983.57[/TD]
[TD="bgcolor: #FAFAFA, align: right"]28,165.30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10.00%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]329,740.07[/TD]
[TD="bgcolor: #FAFAFA, align: right"]27,478.34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9.75%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]321,496.57[/TD]
[TD="bgcolor: #FAFAFA, align: right"]26,791.38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9.50%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]313,253.06[/TD]
[TD="bgcolor: #FAFAFA, align: right"]26,104.42[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9.25%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]305,009.56[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25,417.46[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9.00%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]296,766.06[/TD]
[TD="bgcolor: #FAFAFA, align: right"]24,730.51[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8.75%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]288,522.56[/TD]
[TD="bgcolor: #FAFAFA, align: right"]24,043.55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8.50%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]280,279.06[/TD]
[TD="bgcolor: #FAFAFA, align: right"]23,356.59[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8.25%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]272,035.56[/TD]
[TD="bgcolor: #FAFAFA, align: right"]22,669.63[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8.00%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]263,792.05[/TD]
[TD="bgcolor: #FAFAFA, align: right"]21,982.67[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7.75%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]255,548.55[/TD]
[TD="bgcolor: #FAFAFA, align: right"]21,295.71[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7.50%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]247,305.05[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20,608.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7.25%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]239,061.55[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19,921.80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7.00%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]230,818.05[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19,234.84[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6.75%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]222,574.55[/TD]
[TD="bgcolor: #FAFAFA, align: right"]18,547.88[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6.50%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]214,331.04[/TD]
[TD="bgcolor: #FAFAFA, align: right"]17,860.92[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6.25%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]206,087.54[/TD]
[TD="bgcolor: #FAFAFA, align: right"]17,173.96[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6.00%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]197,844.04[/TD]
[TD="bgcolor: #FAFAFA, align: right"]16,487.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5.75%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]189,600.54[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15,800.04[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5.50%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]181,357.04[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15,113.09[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5.25%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]173,113.54[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14,426.13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5.00%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]164,870.03[/TD]
[TD="bgcolor: #FAFAFA, align: right"]13,739.17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4.75%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]156,626.53[/TD]
[TD="bgcolor: #FAFAFA, align: right"]13,052.21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4.50%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]148,383.03[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12,365.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4.25%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]140,139.53[/TD]
[TD="bgcolor: #FAFAFA, align: right"]11,678.29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4.00%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]131,896.03[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10,991.34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3.75%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]123,652.53[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10,304.38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3.50%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]115,409.02[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9,617.42[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3.25%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]107,165.52[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8,930.46[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3.00%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]98,922.02[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8,243.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2.75%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]90,678.52[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7,556.54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2.50%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]82,435.02[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6,869.58[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2.25%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]74,191.52[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6,182.63[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2.00%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]65,948.01[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5,495.67[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=INDEX(A3:A40,MATCH(G1,D2:D39,-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.