Excel 2007: look up CLOSEST values in a list

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)

georgedb,
The convention on Experts Exchange is to post formulas with US syntax (list separators, function names), making the assumption that the Asker knows how to handle the conversion.
MATCH(AE21;Target!E5:E16)

Use a vlookup and set the last criteria to True, which will look for the closest match. The list needs to be sorted in order for this to return your expected result.
=VLOOKUP(22,A1:B4,2,TRUE)
See attached example.

I think the elements of the solution to the question are all there, but it hasn't yet been tied up in a nice package.

If you use VLOOKUP with TRUE as the fourth parameter (as suggested by broomee9 and abitoun and which would be my recommendation), the two columns in your lookup table need to be switched. The percentage needs to be to the right of the lookup column. If this is not possible, then you need to use an INDEX and MATCH type solution such as suggested by StephenJR.

Either way, you will need the additional IF test for values less than 10 as suggested by abitoun. Adding that feature to StephenJR's INDEX and MATCH gives:
=IF(F5<10,"",INDEX(A1:A4,MATCH(F5,B1:B4)))

Another approach is to add a row to your lookup table that contains the lowest possible value (such as 0) in the lookup column. If you do that, then you can avoid the complication of the extra IF test, and use either a simple VLOOKUP (columns reversed) or INDEX & MATCH exactly as posted by broomee9 and StephenJR.

My problem is slightly more complicated, I'm afraid. Per month (sheet), I want to see for the hours I made where they 'score' on the first sheet (targets). The percentage column is not always adjacent to the column with the hours, this is only the case for January.

Another thing is that the percentage needs to be looked up not based on closest value, but on the value of the previous category. So, for January, even if "Waarvan declarabel" equals 132, then the "Bonus percentage" should be 5,5% (and not 6%, because the category that belaongs to 6% only starts with 133+).

I played a bit with the above code for January. What Abitoun writes is needed as well, but if the number of hours is below the category for 1%, the answer will be 0% (like in June). The number of hours in the row with 1%, is the threshold. If (January) the number of hours is over 145, the bonus percentage remains 7,5%...TEST--UrenGeorge.xlsx

It's a pleasure. I searched for some time to find it and I now use only that. I even do a screencapture, paste it into MSWord and then use MWSnap on the image in MSWord to reduce the file size. I find that necessary in some circunstances, but usually I just use MWSnap on its own.

georgedb,
The convention on Experts Exchange is to post formulas with US syntax (list separators, function names), making the assumption that the Asker knows how to handle the conversion.

MATCH(AE21;Target!E5:E16) returns the index position of AE21 in the range Target!E5:E16. There is a third parameter not included (but assumed) that tells MATCH that the list in Target!E5:E16 is sorted in ascending order and that an approximate match is acceptable. Approximate match means that MATCH returns the index position of the largest number in the list that is less than or equal to AE21 (like a rounding down type of thing). The index number is the position of the found element in Target!E5:E16 in the list (E5 would be index 1, E6 would be index 2, etc.). Note: MATCH returns an error value if AE21 is less than Target!E5

INDEX(Target!C5:C16;MATCH(AE21;Target!E5:E16)) INDEX uses a row index to return a value from the range Target!C5:C16. In this particular case, the MATCH function is returning that index. Unless the value of AE21 is less than Target!E5, the INDEX function returns the desired percentage.

=IF(AE21<Target!E5;0;... IF tests whether AE21 is less than Target!E5. If so, it returns 0. If not, it returns the third parameter, which is the INDEX and MATCH part of the formula.

I could have posted one formula that would work for any worksheet. Such a formula would not require changing to a different column in worksheet Target for each different month. Doing so requires three changes to your workbook:
1) The large caption in cell A1 should be an actual date, formatted to look like the name of the month
2) The column heading labels in your lookup table must be the three letter abbreviations for the month
3) Please add a row to the lookup table with 0 as the percentage in Target!C5 and 0 as the value in Target!E5 and following

Had I been familiar with your language, I'd have posted such a workbook for you. The first two requests allow the formula to properly match the column in the lookup table. The third request allows me to eliminate the IF part of the formula.

Regarding the points, the way this question was answered suggests a split of the points. My posts were clearly based on the comments of the others. That said, any way that you decide to explain a split will be found acceptable by the Experts here.

I modified your lookup table as suggested. I was then able to use the following formulas in cells AH18:AH20 on the January worksheet. These formulas could be copied and pasted on each of the following worksheets, and the references would update correctly:
=AH14
=SUM(AH$3:AH$6)
=INDEX(Target!$C$5:$C$17,MATCH(AH19,INDEX(Target!$D$5:$O$17,,MATCH($B$2,Target!$D$3:$O$3,0))))/100

The third formula includes an extra INDEX and MATCH to find the correct column in the lookup table. As previously mentioned, I needed to put an actual date in cell B2 on the monthly worksheets, but I could format it to appear like Januari 2010. Likewise, I needed to put an actual date in the lookup table header row, but I could format those to look like Jan. Finally, I needed to add an extra row in the lookup table to hold the minimum possible number of whatever it is that your workbook is summarizing.

The $ in the above formulas fix the row and column references such that the formulas can be pasted into different rows and columns on the various month worksheets without needing to be modified at all. the row and column references that do not include $ as a prefix are relative, and will automatically adjust as required to suit your worksheet layout.

Please see the sample workbook, which works in English and I hope translates the list separators, function names and date formatting into proper Dutch settings.

Thnx Brad. I love to solve things in a generic way. 100 years ago when I developed stuff in Turbu Pascal 3.0 (!), that was always a goal. In this case it was 12 times adapting the formula, so that's what I already did. I'll have a look at your code to learn from it, thanks for you help!

Cheers,

George

0

Featured Post

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

A little background as to how I came to I design this code:
Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation)
David Miller (dlmille)
Synopsis
In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …

This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.