VLOOKUP with a Variable Column

Posted on by Dick Kusleika

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.

Posted in Uncategorized

27 thoughts on “VLOOKUP with a Variable Column”

  1. 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. 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. 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.)

  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).

  8. 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. 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. | 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. 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. 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. 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

  15. 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

  17. 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. 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. 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. 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.

  23. 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. 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. 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

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.