VLOOKUP with a Variable Column

The VLOOKUP() function finds a value in the first column of a range and returns the corresponding value from another column. The return column number can be hard-coded in the third argument to VLOOKUP, like in this example which returns the value in column 3

=VLOOKUP(D10,A1:C5,3,FALSE)

I have a health insurance spreadsheet where I need to do a VLOOKUP to get an employee’s health insurance deduction for a particular month. Every month is represented in the spreadsheet, and I don’t want to have to write 12 different formulas.

To get a return column based on another cell, I use a MATCH function for the column argument. Here’s an example:

Vlmatch1

I can use the column headings in this table as part of my MATCH formula to get the proper month. If I have an input cell for the name and one for the month, the formula would look like this:

Vlmatch2

Because my match range B1:D1 has one less column than the lookup table, I add one to the result of the MATCH. MATCH returns the position of the matching value, in this case 1 as Month1 is the first value in B1:D1. Of course I’d never want the column number in my VLOOKUP to be 1, because that would just return the name.

27 Comments

  1. Frank Kabel says:

    Hi Dick
    I would use a INDEX/MATCH combination instead (just personal taste). e.g.
    =INDEX(A1:D5,MATCH(H16,A2:A5,0),MATCH(I16,A1:D1,0))

    To avoid the ‘+1′ I used the complete range.
    But as said: just personal taste :-)

    Frank

  2. Jon Peltier says:

    Frank -

    I prefer Index(Match,Match) also, but I have read that the Index function is slower than VLookup.

    I usually don’t have many of each, so the user probably cannot tell the difference.

    - Jon

  3. Frank Kabel says:

    Hi Jon
    I run some tests of both formula combinations (using FastExcel) and did not discover any siginificant difference. I even found the INDEX/MATCH combination was even slightly faster (5%) on my machine (But I would consider this as not significant…)

    Frank

  4. Unless there’s a lot of them, I usually don’t worry about speed as much as transparency. The Index(…,Match(),Match()) is, in my experience, hard for others (or myself after 6 months!) to maintain, especially with hard-coded references (for instance, Frank got it wrong above by not including A1 in the first Match range reference). Instead, I would normally name the table (dynamically), say, Table, then use something like:

    =VLOOKUP(H16, Table, MATCH(I16, OFFSET(Table,,,1,), FALSE), FALSE)

    That way there are only three references to keep track of – the two inputs and the table.

    (For some clients, I even define the name FirstRow = 1 and use

    =VLOOKUP(H16, Table, MATCH(I16, OFFSET(Table,,,FirstRow,), FALSE), FALSE)

    to add a bit more clarity.)

  5. Steve Farrar says:

    Wouldn’t a DSUM function work just as well?

  6. DSUM might work even better.

    Based on my experience, though, if you want to guarantee that a workbook cannot be maintained by the average user, include a D-function (granted, including a VLOOKUP isn’t all that much better).

  7. Raj says:

    It helped me, greatly, millions thanks

    Raj

  8. prashanthy says:

    Hi everyone,

    How to compare 3 or more Excel Sheets using Vlookup I dont have acess to MS Acces for creating a Database.
    Pls Help!!!!!!!!!!!!!!!!The files have the same format.

  9. Gary says:

    Hi,

    Is there any way to return more than 1 value of a reference that occurs more than once. For example I am trying to return 5 values for a person who’s name occurs 5 times.

  10. Randy Harmelink says:

    | Is there any way to return more than 1 value of a
    | reference that occurs more than once. For example I
    | am trying to return 5 values for a person who’s name
    | occurs 5 times.

    You could use MATCH() and OFFSET() combinations instead of VLOOKUP(). For example, suppose you have data in columns A and B and you want to look up a value in I1 in column A to find the value)s_ in column B. Your VLOOKUP() to find a single item would look like this:

    =VLOOKUP($I$1,$A$1:$B$500,COLUMNS($A:$B),FALSE)

    Now, suppose I have these formulae:

    H1: =0
    H2: =MATCH($I$1,OFFSET($A$1,H1,0,500-H1,1),0)+H1
    …and copy down as often as needed

    I2: =OFFSET($B$1,H2-1,0)
    …and copy down as often as needed

    The value in I2 should match the VLOOKUP() result, but each successive value in column I is the next match in the database range.

  11. ATIF says:

    could any buddy help me wid, how can we use vlookup if the column number in third comment of formula is changing its place. i mean , more column gets added but the contents and column heading remains same.
    Bundles of kisses and hugs if any buddy cud plz help me wid dat.

    Thanks

  12. Sam says:

    I got a table with matching multiple lookupvalues, like below
    Column A Column B
    100 10
    100 20
    200 40
    300 50
    400 60
    500 5
    500 55

    I am doing a vlookup a value in column A, I need the result to add the values in column B, like for 100 I need a result of 30(10+30). Can some one can help me.

  13. Kanwaljit` says:

    Hi Sam,

    Enter this formula in Column C and Drag Down.
    =IF(COUNTIF($A$1:$A1,$A1)=1,SUMPRODUCT(($A$1:$A$7=$A1)*($B$1:$B$7)),””)

    Regards
    Kanwaljit

  14. Jazzer says:

    Sam, why not just use sumif?

    =SUMIF(A1:A7,100,B1:B7)

  15. Anand M. Bohra says:

    for solution of
    Gary says:
    January 08, 2006 at 7:31 pm

    Hi,

    Is there any way to return more than 1 value of a reference that occurs more than once. For example I am trying to return 5 values for a person who’s name occurs 5 times.

    use this function

    Function Manylookup(lookup_Value As Variant, lookup_range As range, column_no As Integer, delimeter_val As Variant) As Variant
    Dim xVal As Variant
    Dim myColl As New Collection

    On Error Resume Next
    For Each xVal In lookup_range
    If CStr(xVal.Value) = CStr(lookup_Value.Value) Then
    myColl.Add Item:=xVal.Offset(0, column_no – 1)
    End If
    Next xVal
    On Error GoTo 0

    For Each xVal In myColl
    Manylookup = Manylookup & delimeter_val & xVal
    Next xVal
    Manylookup = Right(Manylookup, Len(Manylookup) – Len(delimeter_val))
    End Function

  16. Kanwaljit` says:

    Hi Anand,

    How to use the function. Can you please email me a sample sheet at kanwalno1@gmail.com

    Regards
    Kanwaljit

  17. Sievert,L. says:

    Looking for help with formula for 3 variables. I’ve used Index(match, match) to pull from a table with 2 variables but having no luck with 3. The columns have two lables, a name (apparel, cosmetics, jewelry) and date (1/1/09, 1/2/09, ect… (trying to paste in an example without it getting messed up

    So what can I used to find cosmetics, 1/18/09 12:00 am?

    ApparelCosmeticsJewelryApparelCosmeticsJewerly
    1/18/20091/18/20091/18/20091/19/20091/19/20091/19/2009
    12:00 AM123456
    12:30 AM123456
    1:00 AM123456
    1:30 AM123456
    2:00 AM123456
    2:30 AM123456
    3:00 AM123456
    3:30 AM123456
    4:00 AM123456
    4:30 AM123456
    5:00 AM123456

  18. Make the ‘reference’ argument of the INDEX function an OFFSET function which excludes the columns you don’t want. Put in cell:

    B18: 1/19/2009
    B19: Cosmetics
    B20: 1:30

    and this formula will return the correct value as long as your dates ascend from left to right.

    =INDEX(OFFSET(A1,2,MATCH(B18,$B$2:$G$2,FALSE),11,7),MATCH(B20,A3:A13,FALSE),MATCH(B19,B1:G1,FALSE))
  19. Sievert,L. says:

    Perfect – Thank you! Just one more if you wouldn’t mind…
    How would I modify this if I wanted to use this on something similar but horizontal (using the data from before for ease..)
    now the dates in ascending order are in column A, a data lable in column B, and times of day are now across the top in row 1.

  20. Alison S says:

    Hi,

    I have a table with costs in by stationary type and volume, so there two variables to look up to bring back a value. I want a cost to be returned from the table once someone enters the volume and the stationary type. I was trying a combination of VLOOKUP and IF formula, but I can’t get it too work, maybe I am doing the wrong thing or have too many/not enough brackets?

    Can anyone help?

    =IF(C32=C9,VLOOKUP(C31,A10:G15,2)),E25(C32=D9,VLOOKUP(C31,A10:G15,3)),IF(C32=E9,VLOOKUP(C31,A10:G15,4)),IF(C32=F9,VLOOKUP(C31,A10:G15,4)),IF(C32=G9,VLOOKUP(C31,A10:G15,5))

  21. Sievert,L. says:

    Alison – Since you only have two variables (type and volume) have you tried using an INDEX(MATCH,MATCH)?
    The Lookup Wizard under Tools works great if you are unfamiliar – you just have to alter the formula once it is created for your needs.

  22. HoKe says:

    Hi ATIF
    i have a solution ;)
    =vlookup($A$2;sheet1!$A:$T;column(C3);false)

  23. Dilip says:

    Can someone help me solve this problem? I have show sheets. Monthly sheet looks like this:

    Name 8/1 8/2 8/3 8/4 8/5 …
    John A B D C
    Smith B A C D
    Bob D C A B
    Sam C D B A

    Weekly sheet looks like this:

    Shift Sunday Monday Tuesday Wednesday
    8/1 8/2 8/3 8/4
    A John Smith Bob Sam
    B Smith John Sam Bob

    What I want to do is populate names of Weekly sheet when I update monthly sheet. So date in weekly should match with date of monthly sheet and name from monthly sheet should be populated based on shift (e.g. A shift on 8/2 should result in Smith)

    Appreciate your help.

  24. John Franco says:

    Hi Dilip,

    Here’s a solution…

    Assuming the dates 8/1, 8/2, etc. are corresponding in “monthly” and “weekly” sheets.

    You can write this formula at the “weekly” sheet: =INDEX(monthly!$A$2:$E$5,MATCH($A3,monthly!B$2:B$5,0),1)

    The above formula would retrieve “John” from “monthly” sheet

    Pay special attention to the semi-absolute references: $A3 and B$2:B$5 in the formula. This allows you to copy the formula in the “weekly” sheet.

    “Weekly” sheet looks like this:

    A,B,C,D,E
    1) Shift Sunday Monday Tuesday Wednesday
    2) 8/1 8/2 8/3 8/4
    3) A John Smith Bob Sam
    4) B Smith John Sam Bob

  25. Griff says:

    Hello,

    I have a problem using VLOOKUP that I hope someone here can help with. I’m doing a VLOOKUP to a source range that has alphanumeric values which are sometime differentiated only by by upper/lower case which VLOOKUP cannot handle.

    Example: 0014000000RZBAx and 0014000000RZBAX. VLOOKUP sees these as exact matches even thought the last value is different ‘z’ versus ‘Z’.

    Any suggestions? Would I be better off using a different function?

    Thanks very much.

    Griff Jacobsen

  26. Fabien T says:

    Awesome, this saved me a lot of typing and headache, thanks!

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: