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.
.
But as you can see here, although I am using absolute references, the VLOOKUP doesn’t change when I copy it across multiple columns.
.
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.
.
.
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.
.
.
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.
.
There you have it. I’m sure there are other clever ways to do this, but these are the two methods I use.
RELATED STUFF
- Using Arrows to Edit a Named Range
- Outta My Way Formula Thingy!
- Find and Highlight Specific Values
- Choosing Quarters in Excel
- Creating Subtotals on Many Columns
- Creating a Frequency Distribution with a Pivot Table
- Limiting Movement within Your Worksheet
- Protect Worksheet Structure with Array Formulas
- Padding Numbers with Zeros
- Protecting VLOOKUPS from Expanding Data
I’ve used helper cells fairly frequently. Never even thought of trying the COLUMN function; very handy. Thank you, sir.
This is such a good tip Mike! the vlookup formula is one of the best in Excel and you probably just made many people’s lives that much better by saving them a bunch of time. I like the column feature a bit better because you don’t add extra data to the sheet. Also, does the helper row mess up sorting by thinking the helper row is the top?
The problem I have with both thoses solutions is that the index is related to the destination position and not to the source. So in that example, if you add a column in the source, everything get messed up. May be with a formula in the helper cells with MATCH that looks for the column title in the source, you’ll be more robust.
For the sorting part, you’ll be ok if you select the range you want.
PAT: There are still some caveats to using the MATCH function. First, the labels between the two tables must be the same (which isn’t always the case). Second, the labels can’t change in either table or the MATCH formula will break.
But your point is a good one and well taken. The structure of your tables must remain constant or your VLOOKUPS will break – even in a standard VLOOKUP where no clever tricks are applied.
I’ve used the helper cell version in the past, but with the helper cells above the source table, which usually reminds me to modify them if I add or delete a column. I think I like your COLUMN() solution better, but again referring to the columns in the source table, assuming the source table starts in Column A. Or if the source table starts in cell I18 as above, maybe COLUMNS($I$18:J$18). How nerdy is that?
I also use the MATCH function on occasion when labels are not an issue. If I use an helper row, I might have an extra one with the labels linked to the source table.
Another option I like to use is the COLUMNS (notice the extra S at the end). The fomula would look like this in the example above example.
=VLOOKUP($C$18, $I18$:$L$23, COLUMNS($I:J), FALSE)
The caveat is that the increment is always 1 when you copy the formula.
It also has the advantage of being robust to column insertions.
I like the idea of MATCH and the cell linked to the source label.
Sometimes,I’ll use COLUMN in conjunction with range names that refer to the location of the label for each column in the source table. Say:
=VLOOKUP($C$18, $I18$:$L$23, COLUMN(Heading1), FALSE)
This method is still dependent on the first column in the table being column A.
For some reason, I completely overlooked the idea of a helper cell. I’ve done them as labels to help me figure out the column numbers, but never coded them into the lookup formulas. I guess I’ve danced around this solution.
I never use vlookup, its slow.
=Index(B$1:B$10,Match($A20,$A$1:$A$10,0))
Drag the formula down and across
Also match/Index has several advantages compared to VLookup
a) The Column to Pick can be to right or left of the column to be searched
b) It works for both horizontal and vertical tables
In case of horizontal tables the row to be picked can be above or below the row to be searched
c) A Single match column and Multiple Index columns will be significantly faster compared to Vlookup
d) Match has 3 optional parameters 1,0,-1
Vlookup has 2 : 0 and 1
e) Match/Index can be used to search based on multiple criteria – Vlookup can search on only one
f) Index along with a few other formulas can be used to find and pick multiple instances of a search item
This post is a COMPLETE disappointment. Not that it is lacking in substance, only that you posted it four months too late to save me COUNTLESS hours.
Seriously, excellent tip. The Column function will be a life saver for me. I can’t believe I never thought of it. Thank you.
The pictures are gone??
THANK YOU!
I’ve always felt dumb doing my tracking (I work at Procter & Gamble).
You’ve just made my work a little better.
Added your blog to my RSS
New to Excel: Not sure why the pictures are gone. I’ll try to get that fixed today.
Why didn’t I think of that?
Great tip. thx
datapig -
The graphics are still coming up blank in both Chrome and IE. I look forward to the resolution. More than that though, I just wanted to say THANK YOU for the great content, especially the videos.
Hi – This is a great tip!!! BTW all the pictures are gone. Can this be fixed?
The first section, sans pictures, combined with the Column syntax is extraordinary. I’m new to the lookup functions but your site is extremely helpful. Thanks again, the results have been spectacular for my organization. If you’re hanging in Dallas sometime, drop a line, I’d love to discuss pork over brews.
Mike, the image links are broken …
Hey Everyone…the pictures are back.
Brilliant tip… After two years after the last comment still very handy…