Range Lookup in Excel 

Range Lookup in Excel  – When we use the VLOOKUP function in Microsoft Excel, we usually use the range Lookup in Excel to find an approximate or exact match. The following blog will show you how to use the range lookup feature in the VLOOKUP function with proper illustrations and examples.

https://youtu.be/i0mHB9Zbu54?si=qGrdxorAlJzOk1YE

It is estimated that the majority of Excel users have never used the range lookup feature of VLOOKUP.  Excel users familiar with the VLOOKUP formula are simply conditioned to know that, in order to get an exact match, they should simply type “FALSE” when they get to the range lookup option.  In most cases when using the VLOOKUP formula, we’re looking for an exact match, and utilizing the range lookup option is not necessary.

How do I look up the pricing tier? [Formulas]

Let’s take a look at this really tricky problem. Several weeks ago, I was given a data set like this (shown below) and asked to find the position of the lookup value. There is only one glitch: the lookup table contains lower and upper boundaries instead of values. You can understand what I mean by looking at the illustration below.

My point is this. 

 To put it simply, we need to find the range that contains the lookup value. We discussed a similar trick a few days ago, but this is a completely different problem. As we all know,

• In the XLOOKUP formula, a value in a table is looked up and the value in the next column is returned
• Based on a value’s value, a MATCH formula determines where it belongs in a list

It is possible to use XMATCH as follows:

As shown below, we can use XMATCH to determine which row will contain the value.

=XMATCH(1, ‘B6:B15>C3’) / ‘C6:C15’ ))

Please copy

Let me give you a minute to absorb that formula’s awesomeness.

Hey, are you back? Well, let’s find out what this formula does.

Basically, it works like this:

• Our lookup value is contained in C3
• The lower boundary of B6:B15 is B6:B15
• The highest limit is C6:C15
• The (B6:B15<=C3)*(C6:C15>=C3) formula returns a bunch of 1s or 0s. The value will be 1 if C3 is between the values of columns B and C, otherwise, it will be 0.
• The XMATCH function matches the first row corresponding to the range, i.e. the first row that matches the field.

Alternatively, the SUMPRODUCT could be used instead of Range Lookup In Excel

After that, I thought, “Maybe the SUMPRODUCT formula would work in situations like these?”

Eventually, I came up with the perfect formula.

• Let’s assume the value is in cell C3
• B6:B15 and C6:C15 are the start and end values, respectively.

This is what we write,

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

The formula contains three portions,

1. (B6:B15<=C3) + (C6:C15>=C3): part checks the range B6:B15 and C6:C15 to find one set of start and end values that contain C3. Most likely, the output would be a bunch of 0s with a single 1
2. The ROW(B6:B15): Part displays running numbers from 6 to 15. SUMPRODUCT this with above to get a single number corresponding to the row in which the match occurred.
3. -5 part: Since our value begins in row 6, not row 1, we reduce the output value by 5.

For looking up date ranges, you can also use this method:

In the same way, you can use Vlookup to lookup matching date ranges, you can easily use the SUMPRODUCT formula to do so.

You can download the Range Lookup in Excel Example Workbook here:

There are both values and date examples in the download workbook. Don’t hesitate to download it. You can play with it to better understand the range lookup formula.

To download important shortcuts in Excel E-book, click here.

Is there a problem with range lookup?

I often find myself checking when a date falls between two sets of start and end dates when working on project planning. Previously, I used helper columns to solve this problem. However, the XMATCH (or SUMPRODUCT) solution is much more elegant and scalable. The process of writing it is also much more enjoyable.

Is that what you think?

How often do you have trouble with range lookups? What is your solution? Feel free to share your techniques and tips in the comments.

Here are some resources to help you learn about Excel tables 

• What are Excel Tables and how do I use them?
• Transposing in Excel: how to do it?
• How to use Excel functions?
• How to use Excel shortcuts?
• Excel Tutorial

Linked resources

• Use of Sumifs, Countifs, and other Excel formulas
• Logical functions
• If Functions
• How to use Excel functions?