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.

Video

Written Narrative

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.

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!

This article was written by Jeff Lenning

6 comments:

  1. Pete Hill
    Reply

    Hi Jeff, I’m a retired CFO. Excellent hack. I would like to share a couple that I used to teach my staff – they are very simple but sometimes the simple things don’t occur to everyone. Firstly the first argument. Like this example you need to be able to drag formulas across many rows and columns to preserve consistency. Judicious use of references can be very helpful here by switching between relative, mixed and absolute but the mixed reference in the first argument is the most powerful even if a bit of experimentation is required. I taught to drag just one or two cells then double click on the formula to check the references, then toggle the first argument until it worked. The second hack was where I had many columns of source data and didnt know then about using names. I would add a row above the source data headings and auto fill with corresponding column nos.
    In my report page I would copy and paste the 2 rows – numbers and headings. Then in my vlookup I would simply reference the column no in the 3rd argument ie =vlookup(B6,table1,A3,0). To improve the layout you can then hide the number row and rename the headings if you want and delete any unwanted columns in the report. Finally if you want to get real smart you can also mix up the references in the 3rd argument also to move data to different locations on the report, but this hack is best effective when outputs are in rows. The other beauty of it is that you can move columns around on the report page and the source data reference comes with it.

    1. Jeff Lenning Post author
      Reply

      Hi Pete … thanks for sharing … I REALLY appreciate it 🙂
      Thanks
      Jeff

  2. Paul K Dick
    Reply

    I suggest you briefly show the Table1 in the video so the viewer can understand why it doesn’t work with out the concatenation. Also, I would like an explanation of why the concatenation fixes the problem.

    1. Jeff Lenning Post author
      Reply

      Howdy Paul … happy to help!
      The table is shown at about 0:16 in the video, and the reason concatenation fixes the problem is because it combines the values from B and C into a single lookup value for VLOOKUP. For report rows 6 and 7, the lookup value is in column B. These are the labels Sales and COGS. But, as we move down the report, the lookup values for rows 11 and 12 (Selling expenses and Administrative expenses) shift one column to the right, column C. The fact that these report labels are stored in different column prevents us from writing a consistent formula that can be filled down. The hack is to use concatenation for the first argument so that the first argument is the combined values from B and C. This enables us to write a consistent formula that can be filled down, and work for all report rows.
      Thanks,
      Jeff

  3. Juan
    Reply

    Excellent tip, thank you very much Jeff, you explore all the mysteries of Lookup as I have never seen before, awesome!

  4. Allan David
    Reply

    This is very helpful tips. It made my report very easy and fast. Thanks to you sir Jeff.

Leave a Reply

Your email address will not be published. Required fields are marked *