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

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:

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

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

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

26. Fabien T says:

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

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