LOOKUP formula not pulling correct data

Attached you will find the spreadshhet in question. If you click on the click here button input the following info:

8.0
3508949
80
0
0
25

the sheet will auto populate all numbers. this part is working properly. MY issue is if you look at the Bad Debt number, the correct response should in cell f21 should be $636.46 because in order to hit the next level of pay, you must at least hit $3,424,082.00. I can not figure out how to get the correct formula that will put the right pay out. Basically in order to hit a certain level of payout you must hit that next number, even if you are a $1 higher you can not get the pay out. So for example if your bad debt is $3,424,083 you would be paid out at 100%, not 120%...

Hope this makes sense..Thanks in advance for your help

Hi Anelson,

LOOKUP is not working because the function can only return the value in the same row as an exact match, or the value in the same row of the largest value that is smaller than the lookup value - and it must be in an ascending list. It won't return the value in the same row of a value larger than the lookup value. Hopefully that made sense, if not check the Excel help files under LOOKUP.

As for an alternative, try this:
1. Select A18:D26 and sort descending on column A (so 4,945,890 is first, and "-" is last).
2. Change the formula in D5 to: =INDEX(B18:B26,MATCH(D4,A18:A26,-1))
3. Change the formula in F21 to: =INDEX(D18:D26,MATCH(D4,A18:A26,-1))

The -1 argument at the end tells the MATCH function to return the smallest value that is greater than or equal to the lookup value (D4). The -1 argument only works in lists that are sorted descending, too. For more assistance with this function you can look in Excel help, or Google INDEX MATCH functions.

Hope that helps.

To lookup numbers ( amounts ) you best use the =SUMPRODUCT() formula. There's an example on my site (Excel, Multiple lookup).

I think pjoaquin's solution is best, however, if resorting is an issue I would advise the below:

=INDEX($D$18:$D$26,MATCH($D$4,$A$18:$A$26,1)+ISNA(MATCH($D4,$A$18:$A$26,0)))