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. 


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.

49 thoughts on “Copying VLOOKUPs Across Multiple Columns

  1. MnM

    I’ve used helper cells fairly frequently. Never even thought of trying the COLUMN function; very handy. Thank you, sir.

  2. Tony Rose

    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?

  3. PAT

    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.

  4. datapig

    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.

  5. Paul Hollinger

    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?

  6. Sébastien Labonne

    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.

  7. Omar

    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.

  8. sam

    I never use vlookup, its slow.


    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

  9. Paul

    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.

  10. Victor


    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

  11. Michael

    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.

  12. Michael

    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.

  13. Ryan

    Almost three years after posting, this is still paying big dividends (at least for me!). Many thanks! This just saved me hours.

  14. Richard

    So that does work great to change the column reference….what about when you copy down the absolute $A$3…still a problem, any solution for both across and down?

  15. Keith

    So I have a quick question. I was so excited when I found this but what if the table array is on a different sheet. How do you use the column function for returning from a different sheet. Thanks

  16. Sean

    I find those solutions work well, another one i like to use is the Columns function, which counts the number of columns in an array. So what you can do is anchor the first cell and as you move the columns over the number changes. So Lets say my vlookup column index starts at 3 but i want it to go on till 100 the Formula will look something like this:

    Vlookup(lookup value, table array, COLUMNS($A6:C6), FALSE)

    and then drag this equation over how many columns you want. So column index for my first look up is 3 (number of columns from A6 to C6) but as i drag this equation over the number increases. Instead of dragging you can also control paste formulas as well.

    Just thought I’d help out. If anyone needs a little more clarification on this email me at

  17. Sam

    I really feel like a monkey working on this shit the hard way for soo long. !!!!!

    Awesome . thanks for the solution.

  18. Pete

    I find that I really like INDEX and MATCH better than VLOOKUP, but when I do use VLOOKUP and need more that one column returned I enter it as an array formula (CTRL+SHIFT+ENTER) with the column numbers entered in an array syntax. Like this: =VLOOKUP(lookup value, table array,{column1,column2,column3},FALSE) enter it with CSE to get {=VLOOKUP(lookup value, table array,{column1,column2,column3},FALSE)}. Just remember you have to highlight and all of the cells in the array to get all of your values returned. As a bonus, most of the people I work with have no idea what an array formula is and how to work with them, so they cannot change the structure of my workbooks. If they try they get the “You cannot change part of an array.” error.

  19. AJ

    I almost had an orgasm when the “COLUMN Function” solution actually worked !!!

    Thank you all !!!

  20. Mayra

    Omg! Yesterday a coworker show me the helper cell solution.

    But I thought , there must be a more elegant way to do this??!!!
    Thanks, love the columns solution!

  21. RhondaK

    Thank you for both tips. I found the COLUMN tip to be just what I needed. It saved me a lot of manual work. But in the screenshot above: both parts of the stringed formulas were absoluted: (((your example above))) =VLOOKUP($A$3,$H$3:$K$8,COLUMN(B1),FALSE) but the 2nd string of the formula needs to be non-absoluted, so that you can drag accross and down your spreadsheet. My example: =VLOOKUP($A$3,$H$3:K$8,COLUMN(B1),FALSE)
    thanks again!!!! now… what to do with all this extra time!!!

  22. patricia

    It works wonders using COLUMN (A1) i.e.
    but the problem is that IT IS ALSO changing the first column i.e. A3 was my start and now it moves it to B3 etc.
    How can I lock it?
    it looks like this and actually the COLUMN (F3) increments!! yeiii but look below the formula:
    =VLOOKUP(A3,'[SHARE POINT TEST ONLY ERASE AFTER PRACTICE —Kinsey 21-15-5 5H Final.xlsm]Well Totals’!$A:$AS,COLUMN(F3),0)

    This is what happneds when I drag:
    look at the first part:
    =VLOOKUP(K3,'[SHARE POINT TEST ONLY ERASE AFTER PRACTICE —Kinsey 21-15-5 5H Final.xlsm]Well Totals’!$A:$AS,COLUMN(R3),0)

    Notice how =VLOOKUP (K3 this should be A3 and it changes…. But the end of it COLUMN R3) is what I want.. did I explained my self?

    a geek wanna be… Thank you xoxo

  23. Manjari Goel

    Hi ,
    many thanks for this, however this is not working when I am trying to copy the formula down the rows in a column
    please help

  24. Bill Brooks

    How can you use helper cells (COLUMN) if the data you are looking up is in a different tab?
    In essence When I do a vlookup for an item in my open tab A to try and find it in a different tab B and return a value from tab B I would also like to get the other corresponding data in Tab B.
    Lets say in Tab A i want to find MA33145. The data related to that is in Tab B along with its description, cost, date.
    I want to extend the formula like COLUMN(B2) but that does not work when looking up in a different tab

  25. Hussein

    Kindly tell me how to put a comment with a screen shoot for one Excell sheet example, I have a question about it

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>