Range Lookup in Excel [Formulas]
Here is a really tricky problem. Recently I was given a data set like this (shown below) and asked to find the position of lookup value in the list. The only glitch is that, instead of values, the lookup table contained lower and upper boundaries of the values. See the below illustration to understand what I mean.
In simple words, we have to find the range that has the lookup value.
Now, the problem is similar to between formula trick we discussed a few days back, yet very different.
We all know that,
- VLOOKUP formula looks up a value in a table and returns the corresponding value in next column
- MATCH formula looks up a value and tells the position of it in a list
But neither seem to solve this problem. So I naturally turned to a cup of home brewed coffee (remember, I no longer work in a office, so I cant rush to espresso machine) and stared long and hard out of the window (remember, I no longer go to office, that means I can sit in front of a window and work).
Then I thought, “may be SUMPRODUCT formula would work for situations like these?!?”
After playing for a while, I got the perfect formula for this.
- Assuming the value to be looked up is in cell
- The start and end values are in
Ok, go ahead, I will give you a minute to soak in the awesomeness of that formula.
Are you back?, well, lets explore what this formula does.
There are 3 portions in that formula,
--(B6:B15<=C3)*(C6:C15>=C3)part: This is checking the range B6:B15 and C6:C15 to find that one set of start and end values that would contain the value in C3. The output would be a bunch of 0s with probably a single 1
ROW(B6:B15)part: This just gives running numbers from 6 to 15. When you SUMPRODUCT this with above you get a single number corresponding the row in which the match occurred
-5part: We reduce the output value by 5 since our value began in row 6, not row 1.
Use this to lookup date ranges too:
As you can guess, you can easily use the above SUMPRODUCT formula to lookup matching date ranges too a la vlookup for date ranges.
Download Range Lookup Example Workbook:
In the download workbook, you can find both examples (values and dates). Go ahead and download it. Play with it to understand range lookup formula better.
Range Lookup Formula [XLS]
Do you face range lookup problem?
Often, when working on project planning, I end up checking where a date falls between given set of start and end dates. Earlier, I used helper columns to solve such a problem. But the SUMPRODUCT solution above is much more elegant and scalable. Plus it is much more fun to write.
What about you?
Do you face range lookup problem often? How do you solve it? Share your techniques and tips using comments. Thank you
More Excel Formula Magic:
- Average of top 5 values – Excel Formula Tutorial
- Count the number of unique values in a range
- Reverse a list of values using INDEX formula
|Excel Links – Change in Posting Schedule Edition||Even Faster Way to Compare 2 Lists in Excel [Quick Tip]|