Getting an approximate match from a range
January 22, 2010 • JP • No Comments • Rate This Article
• Links to this article
In Return value if in range in excel, Oscar shows us a formula for returning values in a column based on a number range. Let's review his formula and also provide a few more that can do the same thing simpler.
He uses the following formula:
=INDEX(D4:D6, SUMPRODUCT(–($D$8<C4:C6), –($D$8>B4:B6), ROW(A1:A3)))
- D4:D6 is the range of cells that contains the value to be returned
- $D$8 is the value being sought
- B4:B6 are the lower limit cells of the data range
- C4:C6 are the upper limit cells of the data range
According to Microsoft Office Online, the SUMPRODUCT formula "multiplies corresponding components in the given arrays, and returns the sum of those products."
Three arguments for SUMPRODUCT
There are three arguments used in the SUMPRODUCT formula above.
1. Compare D8 to the values in C4:C6 and return True where D8 is less than one of those values, else False.
D8 compared to C4: False (0)
D8 compared to C5: True (1)
D8 compared to C6: True (1)
The double dash (–) converts the boolean values to numeric: one for True, zero for False.
So the formula becomes SUMPRODUCT({0,1,1}, –($D$8>B4:B6), ROW(A1:A3)).
2. Compare D8 to the values in B4:B6 and return True where D8 is greater than one of those values, else False.
D8 compared to B4: True (1)
D8 compared to B5: True (1)
D8 compared to B6: False (0)
The double dash (–) again converts the boolean values to numeric: one for True, zero for False.
So the formula is now SUMPRODUCT({0,1,1}, {1,1,0}, ROW(A1:A3)).
3. The ROW formula (as it is used) returns an array of numbers from 1 to 3.
{1,2,3}
The formula finally becomes SUMPRODUCT({0,1,1}, {1,1,0}, {1,2,3}).
SUMPRODUCT will then multiply the corresponding components of each array and return the sum. The first element in each array will be multiplied together, the second number in each array, and so on.
(1st element) 0 * 1 * 1 = 0
(2nd element) 1 * 1 * 2 = 2
(3rd element) 1 * 0 * 3 = 0
The sum of these numbers is 2, so the SUMPRODUCT portion of the formula returns the number 2.
The formula is now =INDEX(D4:D6, 2) which returns B. You can check Oscar's post (or download the sample workbook below) and see that is in fact the result. Note that you can step through the formula and observe the above steps by using the formula auditing tools available in Excel.
In Excel 2007/2010: Select formula cell, on the Ribbon go to Formulas tab, Formula Auditing group, click Evaluate Formula.
There's an easier way
I found two simpler formulas to do the same thing. These approaches assume the data is sorted (which in the referenced blog post, it is).
- =VLOOKUP(D8,$B$4:$D$6,3,TRUE)
- =INDEX($D$4:$D$6,MATCH(D8,$B$4:$B$6,1))
The fourth argument of VLOOKUP is set to TRUE, which tells the function that if it cannot find the value in D8, to return the next highest value that is lower than D8. The classic example is grades, which you can view at Contextures page on VLOOKUP.
This formula does the same thing as above. Instead of using 0 (exact match), the third argument of the MATCH function is 1 which tells it to return the largest value less than or equal to the lookup value (if it cannot find the lookup value in B4:B6). Same as VLOOKUP!
Translated into natural language, this formula reads "return the corresponding value in D4:D6 where the value in B4:B6 is either equal to D8 or the largest value less than D8."
I find these methods to be simpler than using the SUMPRODUCT formula. But as we've seen, there are usually multiple ways to do anything in Excel.
Download the sample workbook to see all three formulas in action.
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Listing built-in Access database properties
Next Post: Office Links for January
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].