Copying VLOOKUPs Across Multiple Columns

Nothing makes you feel like more of a monkey than manually adjusting formulas while, all along, you have the sense that you’re doing it the hard way.  This is the feeling you are likely to get when manually adjusting VLOOKUP formulas. 

.

Today’s post is about VLOOKUPS -specifically, how to copy them across multiple columns without having to adjust the formulas manually.

.

The Problem:

In this example, I am trying to get some information on each Product ID.  I have a mapping table that will get me the Description, Segment and Price for each Product ID.  I’ll use VLOOKUPS. 

If you look in the formula bar in this screenshot, you’ll notice I have a standard VLOOKUP formula. 

 .VLOOKUP1

But as you can see here, although I am using absolute references, the VLOOKUP doesn’t change when I copy it across multiple columns.

 VLOOKUP2

.

This is because I need to change the column index in the VLOOKUP in order to reference a different part of my mapping table.  For example, the Segment field shown here will need to reference column 3, while the Price field will need to reference column 4.    

Most of us make these adjustments manually.  This may seem like no big deal, but when you have to do this across 10+ columns, this can become quite tedious, often invoking thoughts of suicide.

 VLOOKUP3

.
.

The Solution 1:  Use helper cells.

A simple solution to this problem is to use helper cells.  As you can see, I place a number above each column where I will need a VLOOKUP formula. The numbers I use represent the column references I will need in the VLOOKUP.  So instead of hard-coding the column reference as in: VLOOKUP($A$3, $H3$:$K8, 2, FALSE),  I can reference the helper cell.  Like this:   VLOOKUP($A$3, $H3$:$K8, C2, FALSE).

This way, the VLOOKUP will automatically readjust as I copy it across multiple columns.

 VLOOKUP4

.
.

The Solution 2:  Use the COLUMN function.

If you’re a complete nerd, constantly looking for the more elegant solution, you can use the COLUMN function.  This method avoids the need to create and maintain helper cells.

For those of you who don’t know, the COLUMN function translates a cell address to a column number.  For instance, COLUMN(D1) would return the number 4 because column D is the fourth column in your spreadsheet.

In this formula, I need to reference the 2nd column in my mapping table. So instead of hard-coding the number 2, I can use COLUMN(B1). 

When I copy this VLOOKUP across, the COLUMN function automatically shifts along with the other references.  This basically allows you to copy the VLOOKUP across without having to readjust your column references manually.

 VLOOKUP5

.

There you have it.  I’m sure there are other clever ways to do this, but these are the two methods I use.

52 thoughts on “Copying VLOOKUPs Across Multiple Columns

Leave a Reply

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