VLOOKUP Hack #7: Different Columns
In this post, we’ll continue hacking the 1st argument. Here is the basic setup. We are building a report. The report structure uses multiple columns to store the report labels, such as sales, cost of sales, selling expenses, and so on. So, can we write a formula that works when the lookup values are stored in different columns? Yes, of course we can. Let’s explore the hack. I’ve included a video demonstration as well as a written narrative for reference.
Instead of jumping right into the hack, let’s take a moment to visualize the issue. Let’s say we are building a formula-based report that uses multiple columns for report labels. Something like the report shown below.
And let’s say the source data is stored in a table named Table1, as shown below.
Then, we use VLOOKUP to retrieve the value from Table1 with a formula like this:
=VLOOKUP(B6, Table1, 2, 0)
It works great for the first one, as shown below.
Then, we copy and paste for the remaining cells, and run into a problem. Note the errors in E11 and E12:
Why do we get this error? It is because the lookup values are not in B11 and B12. Instead, they are in a different column now…column C. When we inspect the formula in E11, sure enough, we see that the formula is trying to find the value in B11 instead of C11 as shown below.
One option available to us is to change the formula in E11 to reference cell C11. And, if this were a one-time project, that would work just fine.
However, we prefer to use consistent formulas in recurring-use workbooks. That way, formulas are easier to update and maintain over time because we can change the first one, and then just copy it down throughout the report. So, how do we do that here, when the lookup values are in different columns? That brings us to our hack.
With a simple modification to the first argument, we can have VLOOKUP work even when the lookup values are in different columns.
Hack: use concatenation in the 1st argument
In summary, concatenation combines, or joins, values to create a single text string. One way to do this is by using the concatenation operator, the ampersand &.
So, we would rewrite our formula, and use concatenation in the first argument, like this:
=VLOOKUP(B6&C6, Table1, 2, 0)
When we copy this formula down, it works for all rows, as shown below.
If you’d like to check out the formulas, just download the sample file below.
And if you have any other fun VLOOKUP hacks, please share by posting a comment below…thanks!
- Sample file: Hack 7