Lookup formula that returns a specific value between two numbers.

[adamb2233]

I have two different list of numbers:

I am looking for a formula that will find a value in the "Attainment %" column and return the corresponding value in the "Payout %" column.

I am using a simple LOOKUP function now and it will return 10% when the reference value is 25%. Problem is, when the reference value is 25.1% up to 25.9%, it always returns 10%. I would like for it to return the incremental number between 10% and 11% (i.e., 25.1% returns 10.1%).

I don't want to build out a massive list that includes all of the decimal places in between.

I have toyed with different functions and know I can do it mathematically but was hoping there is a function that performs the task.

thanks!
Adam

 

[Joe4]

Welcome to the Board!

I think you would have to look up the two values your number is in between, and then do an interpolation between the two values that come back.

However, maybe you can do something easier and just create a formula to get what you need, as it seems to be a linear relationship.

Is your example that you posted actually indicative of what you are really working with, or is it an oversimplified example?
If it is an oversimplified example, can you show us what the real grid looks like?

Also, are there any upper/lower limits to your grid?

 

[adamb2233]

Welcome to the Board!

I think you would have to look up the two values your number is in between, and then do an interpolation between the two values that come back.

However, maybe you can do something easier and just create a formula to get what you need, as it seems to be a linear relationship.

Is your example that you posted actually indicative of what you are really working with, or is it an oversimplified example?
If it is an oversimplified example, can you show us what the real grid looks like?

Also, are there any upper/lower limits to your grid?

Thank you! What I posted is the exact data table I am using.

Payout % won't go below 2% or above 20%. It is very linear.

The reference number in the Attainment % column could be any value between 17% - 35% to the hundredth (i.e., 22.25% or 30.47%, etc.).

 

[Joe4]

OK, then I think it is very easy. Your payment amount should just be your attainment percent minus 15%, .i.e.

=A2-.15

You can use the MIN and MAX functions in your formula to cap your upper and lower limits, i.e. to cap it at 20%, you would use:

=MIN(A2-.15,.2)

 

[adamb2233]

OK, then I think it is very easy. Your payment amount should just be your attainment percent minus 15%, .i.e.

=A2-.15

You can use the MIN and MAX functions in your formula to cap your upper and lower limits, i.e. to cap it at 20%, you would use:

=MIN(A2-.15,.2)

Ha, duh, this was certainly a case of overthinking it. I appreciate this very simple solution. Thank you!

 

[Joe4]

You are welcome!
Glad I was able to help!