## Range Lookup in Excel – How to lookup the pricing tier? [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,

• 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).

## 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))
```
```

## 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),ROW(B6:B15))-5
```
```

## There are 3 portions in that formula,

1. `(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
2. `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
3. `-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.

### 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.