Look up closest match

[yusunghee]

Hi Guys

I am looking for a fourmla to return a value in a column next to the closest value.

I need to find the closest value to the value compared in the range and return the value to the right of it.

This is my formula to lookup the closet value:

=INDEX(B5:B9,MATCH(MIN(ABS(B5:B9-B13)),ABS(B5:B9-B13),0))

The range values go in increments of 2. For example (43%, 45%, 47%, etc.)

When I enter 44%, i want the 45%. Instead, its showing 43%. So basically, if the input number is at least greater than the midpoint of the two numbers in the range, it will pull up the larger number. I can't figure out how to do that.

I have included a sample worksheet to show you guys.

[shg]

Then change the gross margin % column to be the actual breakpoints, and use a simple index/match to get the bonus amount.