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 2^{nd} 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.

A thousand times thank you!!!

awesome, thanks a lot

At the same time i want drag in rows also…how to do that?

Awesome.

@Rakesh – If you change his formula with helper cells from VLOOKUP($A$3, $H3$:$K8, C2, FALSE) to VLOOKUP($A3, $H3$:$K8, C$2, FALSE) it should work in both directions. I am able to drag both directions and it copies everything over via vlookup. I was able to bring over a lot of material very easily in my work using this method. This may also work with the COLUMN function idea making sure to use the $ before the A at the beginning, but not before the number (ie. $A3, not $A$3). Hope this helps.

Thank you so much…you have saved lot of my time 🙂

what if i don’t need all the columns from the reference table, how do i use the column function in that case?

Oh my I love you!! So much faster than doing this manually. And great writing style — I really relate to these feelings…. “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.”

What you said about feeling like a monkey made me lol because that was exactly right. I knew there had to be a better way to change the column index number than doing it manually for the 10 columns I needed to change.

Thank you very much for the showing the easy way to do this!!!!!

Saved me a thousand amendments … literally.

Thanks

Thanks a ton!! As it saved hell lot of time.

Thanks to “valk” as well as it worked for both rows & columns!!

Thank you Walk, you helped me a lot in my work and work time also.

This is a great start! But there is one thing I think could make this even better.

Aside from making it easier to add the formulas in the first place, the main problem with VLOOKUP is that if you ever add a column, hardcoded column reference numbers like “2” or the “2” that you might put in your C2 helper cell may become incorrect if someone adds, deletes, or moves a column. The COLUMN solution does fix this; however, it requires that your table be situated at the left-most column in the worksheet. (That is, the COLUMN function won’t give you the right number if your VLOOKUP table starts on Column F.)

Another way to fix this that allows for VLOOKUPs to tables that aren’t glued to the left side of the worksheet is the COLUMNS function, which counts the number of columns in a range. So instead of COLUMN(B1), it would be COLUMNS($A1:B1). Both give the same answer of 2. Then, of course, when you drag it across, it will, instead of turning into COLUMN(D1), turn into COLUMNS($A1:D1). Voila!

I thought up this trick myself, and this is the closest to it I’ve seen anywhere on the Internet.

What if the reference cell is not part of the same spreadsheet. I am getting an error because my base file for vlookup is based in another sheet in the same workbook.

please help what should be done in this case?

hi

i have in one excel sheet 100 branches with month wise sales, and in next sheet the 100 names are differently arranged with month wise sales.

now when i v look up with the column name reference, it is not giving me correct result. coz in first sheet one branch lets say “A” in cell B1 but in next sheet this is in B23.

How to resolve

Game changer – thanks so much.

Works like a charm !!.thanks a lot for sharing the methods..