Copying VLOOKUPs Across Multiple Columns

November 10th, 2009 by datapig Leave a reply »

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.

Advertisement

39 comments

  1. MnM says:

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

  2. Tony Rose says:

    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 says:

    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 says:

    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 says:

    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. 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. PAT says:

    I like the idea of MATCH and the cell linked to the source label.

  8. Omar says:

    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.

  9. sam says:

    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

  10. Paul says:

    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.

  11. "New" to Excel :) says:

    The pictures are gone??

  12. Victor says:

    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

  13. datapig says:

    New to Excel: Not sure why the pictures are gone. I’ll try to get that fixed today.

  14. fan of the Pig says:

    Why didn’t I think of that?
    Great tip. thx

  15. Michael says:

    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.

  16. Sundeep says:

    Hi – This is a great tip!!! BTW all the pictures are gone. Can this be fixed?

  17. Michael says:

    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.

  18. teylyn says:

    Mike, the image links are broken …

  19. datapig says:

    Hey Everyone…the pictures are back.

  20. Max says:

    Brilliant tip… After two years after the last comment still very handy…

  21. Ryan says:

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

  22. Tammy says:

    You made my day – Wish I would have searched this out years ago!

  23. Richard says:

    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?

  24. Keith says:

    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

  25. sw1 says:

    Sweet ! Thanks for the tip. Works perfect.

  26. Rajesh says:

    awesome help on excel…i will visit this site often.

  27. srinivas says:

    Fabulous. Broke my head for years.

  28. Ratty says:

    You just saved me a gazillion hours with that column function tip. Thanks!

  29. Dave says:

    WOW!!!! an age old problem solved with a really simple and elegant solution!!! thank you so much

  30. Anna Maria Tzanetatou says:

    Thank you so much! You saved me a nervous breakdown!

  31. Sean says:

    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 seanizzle_17@hotmail.com

  32. Sam says:

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

    Awesome . thanks for the solution.

  33. Pete says:

    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.

  34. AJ says:

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

    Thank you all !!!

  35. Nitin says:

    It was very good formula, will be helpful in future to save my time and energy. Thanks to concerned.

  36. MARK says:

    Excellent! This answered what I needed to know.

  37. Mayra says:

    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!

  38. Suzanne says:

    AWESOME!!!! Just the answer I was looking for!

Trackbacks /
Pingbacks

  1. best wineries in Napa

Leave a Reply