• Status: Solved
• Priority: Medium
• Security: Public
• Views: 721

# 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)
0
georgedb
• 5
• 5
• 4
• +3
4 Solutions

VBA DeveloperCommented:
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.

Book2.xls
0

Commented:
Perhaps if data are in A1:B4 and search value in F5, then this?

=INDEX(A1:A4,MATCH(F5,B1:B4))
0

Commented:
use this formula, see how it works in the screenshot.

=IF(X1<10,10,VLOOKUP(X1,\$G\$1:\$H\$4,2,TRUE))
expex.bmp
0

Commented:
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.

0

Commented:
abitoun,

In future for screenshots you might like to try:

MWSnap from:

http://www.mirekw.com/winfreeware/mwsnap.html

It produces very small files and you can set it up so that the screenshot files are automatically saved in your chosen folder.

Patrick
0

Commented:
Patrick,

I love it! is very cool thank you :-)
0

Author Commented:
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
0

Commented:
abitoun,

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.

Patrick
0

Commented:
Try this version of abitoun's formula:
=IF(AH19<Target!D5,0,INDEX(Target!C5:C16,MATCH(AH19,Target!D5:D16))/100)
0

Author Commented:
Hi Byundt:

Thanks. After replacing the comma's with (",") semi-colons (";"), it worked (might have to do with country specific things?). I know how to get it working for the other months.

(February: =IF(AE21<Target!E5;0;INDEX(Target!C5:C16;MATCH(AE21;Target!E5:E16))/100))

Can you also please briefly explain HOW it works, I'm not familiar with these functions.

What would be fair to do with the points? In my opinion this last solution was the input I needed to get it to work. You say it's based on Abitoun's code and some others gave valuable tips.

I see:

Bromee9
StephenJR
Byundt
Abitoun

Please let me know if and how I should share the points.
0

Commented:
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.

0

Commented:
Hi george, i was hoing to get some points since my solution included the if portion...

but i know you were willing to be fair so thank you for that :-)
0

Author Commented:
Hi Abitoun,

Bromee9 = 20 points
StephenJR = 20 points
Abitoun = 105 points
Byundt = 225 points

I'm awfully sorry, something must have gone wrong... I now see that Byundt also got the 105 points that were meant for you...

Is there some way that I can change this?

My apologies!

George
0

Commented:
I do not know i think you cannot but that is ok, I do apreciatte your willingnes to split the points and I am glad all these solutions helped you :-)
0

Author Commented:
Thanks for reopening, hope this solved the problem. Apologies for the inconvenience.
0

Commented:
Thank you George.
0

Commented:
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.

TEST--UrenGeorgeQ26462294.xlsx
0

Commented:
Thank you George and  Thank you byundt.

Happy Friday!
0

Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.