Learn Excel

Range Lookup in Excel – How to lookup the pricing tier? [Formulas]

Last updated on December 17, 2022

Chandoo

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.

Range Lookup Excel - Formula for looking up a value to match corresponding range

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,

XLOOKUP 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 can’t 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). Added in December, 2022:

We can use XMATCH:

Since we just want to know which row will contain the value, we can use XMATCH as shown below.

				
					=XMATCH(1, (B6:B15<=C3)*(C6:C15>=C3))

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.

How this works:

C3 contains our lookup value
B6:B15 has the lower boundary
C6:C15 has the higher boundary
The (B6:B15<=C3)*(C6:C15>=C3) returns a bunch of 1s or 0s. It will be 1 whenever C3 is between column B&C values and 0 otherwise.
XMATCH will match the first 1, ie the first row that matches the range.

Or even the SUMPRODUCT

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 C3
The start and end values are in B6:B15 and C6:C15 respectively,

We write,

				
					=SUMPRODUCT((B6:B15<=C3)*(C6:C15>=C3),ROW(B6:B15))-5

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
-5 part: 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.

Click here to download the sample workbook.

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 XMATCH (or 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 🙂