Solved
Excel 2007: look up CLOSEST values in a list
Posted on 2010-09-09
I have some calculated number X. I have a table with let's say ten rows and two columns in another tab in the spreadsheet. The value of X can not be found exactly in the righthand side of the table (or it would be a coincidence). Idea is this:
Table:
1,0% | 10
1,5% | 20
2,0% | 30
2,6% | 45
If value of X is 22, I want to pick up 1,5%. If the value is 63, I want to pick up 2,6%. If the value of X is less than 10, nothing happens. So not exactly the closest value, but only a new percentage if X is higher than the next category.
I don't see any fuzzy matching as a function in Excel. I tried to solve it with nested IF-clauses (ugly, but it should work), but the number of rows is 12 and Excel is not able to handle that amount of nesting. For just a few IF-clauses, it worked perfetly. I actually want to avoid VB, but maybe there is no function that can do what I want. A VLOOKUP searches for exact value
Example that works of how I solved it with IF-clauses:
=(IF(AH19>=Target!D5;IF(AH19>=Target!D6;IF(AH19>=Target!D7;IF(AH19>=Target!D8;IF(AH19>=Target!D9;IF(AH19>=Target!D10;Target!C10);Target!C9);Target!C8);Target!C7);Target!C6);Target!C5) / 100)