How to Use VLOOKUP’s Range Lookup Feature

by Matthew Kuo on January 3, 2016

in Database Theory, Excel, Formula Writing

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the most popular Excel books on Amazon
 

VLOOKUP Range Lookup 01

The vast majority of Excel users have never used VLOOKUP’s range lookup feature.  Most Excel users familiar with the VLOOKUP formula are just conditioned to know that, when they get to the range lookup option portion of the VLOOKUP syntax, they should just put in the word “FALSE” because they want an exact match.  This is because an exact match is what we want 95% of the time we’re using the VLOOKUP formula, and utilizing the range lookup option is just not a common requirement.

Today, we’ll walk through how you can actually utilize this feature of the VLOOKUP formula.  As mentioned before, the Range Lookup’s lack of prevalence is mainly due to the lack of situations that require it.  However, one key
situation does exist:

The Range Lookup feature of VLOOKUP can be used as an alternative to writing a Nested IF Statement

Click here to download an example Excel file using VLOOKUP’s Range Lookup Feature

The Nested IF Formula

A Nested IF Statement is an Excel formula with multiple IF Statements embedded inside of it.  Nested IF Statements are used for two primary purposes:

  • Assigning data values to a set of categories
  • Assigning data values to histogram bins

While writing a Nested IF formula is a very popular approach to address these requirements, there are a number of issues that make using it difficult.  Nested IF formulas are difficult to write, prone to errors, and not scalable if you have several categories or bins.

Nested IF 12

When you use VLOOKUP’s Range Lookup feature as an alternative to using a Nested IF, it solves the majority of these problems:

  • VLOOKUP is simpler and much easier to write
  • Because it’s simpler to write, it’s much less prone to errors
  • The VLOOKUP approach is scalable because the formula does not increase in length as you add more categories and bins

However, the VLOOKUP formula’s Range Lookup is definitely less flexible than a Nested IF, as there are certain specifications you need to ensure are in place before you can use it.

Requirements for Using VLOOKUP’s Range Lookup Feature

Assume we want to use VLOOKUP’s Range Lookup to assign a set of data values to a smaller set of categories.  Assume each category has a minimum value and a maximum value.  In order to make VLOOKUP work, we need to ensure the following specifications are in place:

  • The full range of categories needs to be continuous – there shouldn’t be any breaks between one category’s maximum and the next category’s minimum
  • Your category minimums must be sorted in ascending order – they need to go up sequentially from smallest to largest
  • Your category minimums need to be on the left hand side of your table – VLOOKUP performs lookups on the left hand side of your table and will be looking up off of your minimums
  • Your category names need to be to the right – because your category names are your return column, and VLOOKUP performs a left to right lookup, they need to be to the right of your lookup values (category minimums)
  • Your minimum threshold has to be greater than or equal to that minimum – it cannot be just greater than because of VLOOKUP’s exact match logic

It’s difficult to digest these criteria without context, so we’ll go through an audit of our setup to ensure that these conditions are met during the actual exercise.

Click here to download an example Excel file using VLOOKUP’s Range Lookup Feature

Step 1:  Define the categories you want to assign your data set values to

The first step of this category assignment process will be to define the objective.  In the following example, our objective is to assign each number in our data set to one of three categories:

  • Low
  • Medium
  • High

Based on the following criteria:

  • Low is from 0 to less than 30
  • Medium is from 30 to less than 60
  • High is any value 60 or greater

For simplification purposes we’ll assume that our data set does not contain negative numbers.

Step 2:  Setup your criteria table based on the requirements for VLOOKUP’s range lookup feature

The simplest way to complete this process is to put all of your categories and criteria in a simple table detailing the minimum and maximum for each category.  See below for an example based on the conditions we’ve identified:

VLOOKUP Range Lookup 02

Now we’ll go through an audit of each of the specifications we identified before to make sure our setup of the criteria is appropriate for use with VLOOKUP’s Range Lookup:

  • The full range of categories needs to be continuouscheck
  • Your category minimums must be sorted in ascending ordercheck
  • Your category minimums need to be on the left hand side of your tablecheck
  • Your category names need to be to the rightcheck, note that this is somewhat unintuitive as generally, we’d put the category names before any of the numbers
  • Your minimum threshold has to be greater than or equal to that minimum – check; note in the upper left category I’ve denoted the minimums as (>=)

Since our setup is correct, we can now begin the category assignment process using VLOOKUP.

Step 3:  Load your Data Set

Load your data into a single vertical column (highlighted in yellow below) and add a field next to it to assign it to a category.

VLOOKUP Range Lookup 03

Step 4: Start Writing your Category Assignment Formula using VLOOKUP

The syntax for the VLOOKUP formula is as follows:

= VLOOKUP ( lookup_value, table_array , col_index_num , [ range_lookup ] )

Remember that we’re not doing a lookup in the traditional sense here, where we’d want to find an exact match to our lookup number.  We’re instead utilizing the range lookup and we only need to change a portion of the syntax to do so.

Click here for a tutorial on VLOOKUP using the more common exact match option

Step 5:  Select your first data set value as the lookup value

This is the value we want to assign a category to.

VLOOKUP Range Lookup 04

Step 6:  Select the criteria table as your table array and remember to reference lock it

Hit the “F4” Key right after selecting your criteria table to reference lock it.

VLOOKUP Range Lookup 05

Step 7:  Reference the Category Name using the column reference

Because our category name is in the third column of our table array, we’ll input the number 3.

VLOOKUP Range Lookup 06

Step 8:  Enter TRUE to enable VLOOKUP’s Range Lookup feature

This is the key that makes VLOOKUP work in this particular use case.  You could also just leave this part of the syntax blank to have it activate range lookup, but for the purposes of this tutorial, we’ll be explicit about what type of lookup we want.

VLOOKUP Range Lookup 07

Step 9:  Copy the formula down

Before doing this, look back to Step 5 and make sure that you reference locked your table array before doing this.  If this is already done, just double click in the lower right corner of the cell for the formula you just wrote, and the cells will copy down for you.

VLOOKUP Range Lookup 08

We now have assigned all of our values to a specific category using the VLOOKUP formula.

How it Works

When you review VLOOKUP’s documentation from Microsoft, you’ll see that you have two options when you get to the range lookup portion of the syntax:

InputResult
0 or FALSEVLOOKUP does an exact match
1 or TRUE or leave blankVLOOKUP does an approximate match

You can see that for the very last option, which we are utilizing in our example, VLOOKUP does an approximate match.  VLOOKUP’s range lookup works exactly the same as the MATCH formula’s approximate match option.  While Microsoft Excel’s documentation is very explicit about the approximate match logic when describing the MATCH formula, for VLOOKUP it feels somewhat hidden away.  Therefore, let’s go into the specifics of what Excel does during an approximate match:

  • If an exact match is found Excel performs an exact match VLOOKUP.
  • If an exact match is not found Excel returns the next largest value that is less than the lookup value.

To see how it works, we’ll work through the logic that a range lookup goes through:

  • Excel looks for the next largest value within the left hand column of our table array that is still less than our lookup value.  Because each of our category ranges are continuous (there are no breaks between them) this logic works out to be the exact same as looking for the category minimum of each of our lookup values.

VLOOKUP Range Lookup 09

  • For 42, the category minimum is 30, because 30 is the next largest number less than 42.
  • Once Excel knows which number to lock on to, it uses VLOOKUP to return the corresponding value in the third column of the overall table array, which is “Medium”
  • “Medium” will be the category name for 42

Just for the sake of completeness, let’s run through a scenario where we tell Excel to do a range lookup, but we get an exact match:

  • For the number 60, the category minimum is 60, because when an exact match is found, Excel just does an exact match VLOOKUP
  • Once Excel knows which number to lock on to, it uses VLOOKUP to return the corresponding value in the third column of the overall table array, which is “High”
  • “High” will be the category name for 60

Click here to download an example Excel file using VLOOKUP’s Range Lookup Feature

Conclusion

The Range Lookup feature of VLOOKUP remains a mystery to most Excel users in world.  However, one specific use case does exist and based on our example, it turns out to be a much better option than using a Nested IF Statement.  While VLOOKUP’s Range Lookup does have very specific setup conditions you need to put in place before you can use it, it ends up being much easier to write, less prone to errors, and much more scalable than a Nested IF.  While it’s still not a common need, it’s worthwhile to learn VLOOKUP’s Range Lookup just to have this in your back pocket.

{ 14 comments… read them below or add one }

Alfred January 5, 2016 at 7:29 am

This is very educative! More power to your elbow!

Reply

Anuradha February 11, 2016 at 4:40 am

Kindly help me with a formula or a process in excel. Will illustrate with an example. Eg:Sheet 1 has one lakh database and Sheet 2 has 45000 database of same excel. I need to remove 45000 database from one lakh database in Sheet 1. Therefore the balance should be 55000 database. CAN SOMEBODY HELP ME IN THIS REGARD??????

Reply

Ben September 28, 2016 at 12:32 pm

It looks like you need to use ‘remove duplicates’ function. Here’s what I would do:
copy and paste the 45000 in the same sheet as the 1 lakh and under the existing data. Then select the whole data (145000) and select ‘remove duplicates’ on the column that has the unique identifier for the record.
It is now September so you probably have already figured it out … if not, hope this work for you.

Reply

John April 25, 2016 at 8:07 am

This is an excellent site, informative, very clearly written and presented. Best summary of Excel lookup functions I have seen. Well done.

Reply

Shashi September 15, 2016 at 11:18 am

Thank you for this excellent post.

Reply

shenne October 16, 2016 at 7:09 am

Simple tutorial and a great refresher! Thanks

Reply

اکسل پیشرفته November 5, 2016 at 1:40 pm

Great. thanks.

Reply

Ayan Chakravarty November 9, 2016 at 8:20 pm

Thank you for giving me your best way to define the range lookup feature.
Thank u and i m really glad to u..

Reply

Anna November 29, 2016 at 12:15 am

Thanks a lot. Very good explanation.

Reply

Kylie Dale May 15, 2017 at 11:28 am

Can you explain if the value youre looking up is equivalent to the number that falls into 2 categories. In your example it would be 30 or 60. which category would 30 yield?

Thanks,

Reply

Sachin May 17, 2017 at 9:19 pm

This is very helpful and very well presented and simple to understand. Thanks a lot.

Reply

Sam Packwood May 30, 2017 at 10:26 am

This is a good explanation for people to whom approximate VLOOKUP is a mystery, but it’s important not to mis-educate them. There’s one part of your explanation that wasn’t 100% correct, the “specifics of what Excel does during an approximate match”. If there is more than one exact match in the sorted lookup column, approximate match will find the LAST exact match, whereas exact match VLOOKUP will stop at the FIRST exact match. So the rule for approximate VLOOKUP could be written:
–“Excel returns the largest value that is not larger than the lookup value”–
(versus exact’s “the first value that is equal to the lookup value”)
Obviously this doesn’t matter in the example you gave, or for any similar situation; but it could matter in the general case of using approximate VLOOKUP.

Reply

JimmyDee July 10, 2017 at 6:33 pm

Very good explanation of a curious function. This is almost what I needed. Unfortunately, the number ranges I have are not continuous. I am looking up a few hundred sets of a few hundred non-sequential numeric sequences.

I will end up having to use an array and Index and Match and Between or something similar.

Yech.

Reply

Max August 30, 2017 at 11:33 am

So is there a way to do this where the function, rather than looking up the next largest number still less than the lookup value, the function tries to see if the lookup value is within a specific range yet still not exact?

Reply

Leave a Comment

{ 2 trackbacks }

Previous post:

Next post:

\n