Creative Excel Idea #2

I may have mentioned this idea before in another blog post but it’s worth repeating again because it is simple enough. It has to do with VLOOKUP. The layout for this function is:

VLOOKUP(lookup value, table array, column index number, true/false)

Example: =VLOOKUP(C5,Table!B2:I53,2,FALSE)

Column index number is usually hard coded in as a number and true stands for approximate match whereas false is for exact match. Most of us have used this function in some fashion so I won’t go further into the use of this function. There are plenty of other sites on the web that can explain VLOOKUP better.

What I want to write about is a creative way of getting around a problem that can come up when using this function and that is when one has to insert a new column in a table of data that you are applying the VLOOKUP against. Usually the need to insert a column does not happen but when it does, your formula does not work as it should. Me, I have encountered this issue often enough that I have missed correcting the formula for the inserted column.

Here’s a screenshot of a population table that I’m going to apply the VLOOKUP against. The population table sits in a tab called “Table”.

The next screenshot shows my formula set ups: one deployed in the usual fashion, another using a creative solution, and third using INDEX/MATCH combo. I included the third option because it is available and is rather simple but I find that most people don’t apply these function pairs. It is just easier to use VLOOKUP. I am going to make the image full width so the formulas are more legible.

In my creative solution, I replaced the hard coded number 2 with a COLUMN function.

Watch what happens after I insert a column into the population table.

The creative solution and the INDEX/MATCH combo adjusted accordingly when a new column was inserted into the population table; the usual VLOOKUP failed.

Using the COLUMN function or the INDEX/MATCH combo will be useful, especially if you forget that you have VLOOKUPs lying somewhere in your file that you need to update for the newly added column.

So, yeah, this could save you some heartache.

By the way, HLOOKUP works in a similar fashion although I almost never use this function.