VLOOKUP Hack #2: Range Lookups
In the first VLOOKUP Hacks post, we talked about how the 4th argument impacts the sort order. But, there is more to uncover about this 4th argument. So, let’s pick up right where we left off.
So far, we understand that the 4th argument tells Excel whether we are looking for a value between a range of values or an exact matching value. Its official name is range_lookup, and now it is time to dig into what it actually means.
I have prepared a video demonstration as well as a detailed narrative below for reference.
When the 4th argument is TRUE, or omitted, it tells Excel to perform a range lookup. What exactly is a range lookup? It means you are looking for a value between a range of values. The fastest way to explain this is with an example and a picture.
Let’s suppose the sales manager created a sales incentive program for the month. He would like you to pay a bonus amount to the sales reps based on their sales for the month. He then provides you with the following table:
If a salesperson had sales of 1,200 for the month, you would easily find the bonus amount of 50. If a salesperson had sales of 12,345, you would be able to determine the bonus amount is 500.
When you are doing this manually, you aren’t looking for an exact matching sales amount. You are looking for a sales amount that falls between a start and end point.
That is EXACTLY what the 4th argument means! That is a “range lookup.”
So, when the 4th argument is TRUE, you are telling Excel to perform a range lookup. When the 4th argument is FALSE, you are telling Excel to find an exact matching value.
Note: You may see 0 used instead of FALSE in the 4th argument. Excel evaluates 0 as FALSE, and any non-zero number as TRUE.
Now that we have the overall concept down, let’s dig into the Excel details.
When we humans perform a range lookup, we love seeing both the start and end points. For example, in the sales bonus illustration above, there are From and To columns. Being able to see both sides of the range makes us feel warm and fuzzy. Content. Comfortable.
But, here is the hack: VLOOKUP only needs the From column!
The implications of this are important. So, let’s unpack them. First, here is an updated table that would work perfectly with VLOOKUP:
Here is how I like to think about VLOOKUP. I like to think about it operating in two stages. In stage one, it looks in the first column ONLY. It starts at the top, and goes down one row at a time looking for its matching value. Once it finds it match, then it enters stage two, where it shoots to the right to retrieve the related value.
So, when the 4th argument is TRUE (or omitted), it will look down the Sales column until it finds its row. Any sales amount that is >= 0 and < 1,000 will return a bonus amount of 0. And sales >= 1,000 and < 2,500 will return 50. And so on.
Now that we see how this works, it is easy to understand why the data must be sorted in ascending order when the 4th argument is TRUE. In order for VLOOKUP to return an accurate result when doing a range lookup, the table must be sorted in ascending order by the lookup column. Hopefully, this helps clarify the sort order issue, which we discussed at length in the first VLOOKUP hack post.
Let’s explore this capability with a few examples.
Example 1: Bonus
If we wanted Excel to determine the bonus amount based on sales, we would write the following formula in to cell C7 to retrieve the bonus amount from Table1:
When the sales amount is 12,345, VLOOKUP returns the expected bonus amount of 500, as shown below.
That is the basic operation of range lookups, but, we can apply this in many different ways. For example, we can do a lookup on date values.
Example 2: Fiscal Periods
Fiscal periods…you mean dates? Yes … VLOOKUP can even work with dates! For example, let’s say we need to create fiscal periods based on transaction dates. We could set up a fiscal period table (Table3), like this one:
Then, it would be easy to have VLOOKUP retrieve the corresponding quarter label for a set of transactions. For example, we could use VLOOKUP to populate column D shown below.
The formula written into D15 and then filled down, is:
Example 3: Single Column
We can even do a range lookup on a single-column lookup table. This technique provides an easy way to return the beginning point of a range. For example, if we need to find the pay period begin date, we could create a table of pay period begin dates, like this:
Then, we can use VLOOKUP to return the value from the 1st column of the table (Table4), like this:
We could write the formula into D18 and fill it down, as shown below:
So, that is what it means to perform a range lookup. In the next post, we’ll talk more about the implications of the 4th argument, and how we can use it to help us perform list comparisons, aka, reconciliations!