Solved

Excel 2007: look up CLOSEST values in a list

Posted on 2010-09-09
20
706 Views
Last Modified: 2012-05-10
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
Comment
Question by:georgedb
  • 5
  • 5
  • 4
  • +3
20 Comments
 
LVL 24

Assisted Solution

by:broomee9
broomee9 earned 20 total points
ID: 33638706
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
 
LVL 24

Assisted Solution

by:StephenJR
StephenJR earned 20 total points
ID: 33638716
Perhaps if data are in A1:B4 and search value in F5, then this?

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

Assisted Solution

by:abitoun
abitoun earned 105 total points
ID: 33638747
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
 
LVL 80

Expert Comment

by:byundt
ID: 33638878
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.

Brad
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33638934
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
 
LVL 4

Expert Comment

by:abitoun
ID: 33639000
Patrick,

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

Author Comment

by:georgedb
ID: 33639072
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
 
LVL 45

Expert Comment

by:patrickab
ID: 33639113
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
 
LVL 80

Expert Comment

by:byundt
ID: 33639248
Try this version of abitoun's formula:
=IF(AH19<Target!D5,0,INDEX(Target!C5:C16,MATCH(AH19,Target!D5:D16))/100)
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:georgedb
ID: 33644045
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
 
LVL 80

Accepted Solution

by:
byundt earned 225 total points
ID: 33645420
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.

Brad
0
 
LVL 4

Expert Comment

by:abitoun
ID: 33648229
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 Comment

by:georgedb
ID: 33648312
Hi Abitoun,

I first didn't understand your message, but then had a look. Something went wrong, the idea was this:

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
 
LVL 4

Expert Comment

by:abitoun
ID: 33648353
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 Closing Comment

by:georgedb
ID: 33648547
Thanks for reopening, hope this solved the problem. Apologies for the inconvenience.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33648579
Thank you George.
0
 
LVL 80

Expert Comment

by:byundt
ID: 33648701
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.

Brad

TEST--UrenGeorgeQ26462294.xlsx
0
 
LVL 4

Expert Comment

by:abitoun
ID: 33648821
Thank you George and  Thank you byundt.

Happy Friday!
0
 

Author Comment

by:georgedb
ID: 33656153
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

How your wiki can always stay up-to-date

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

Join & Write a Comment

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.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now