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

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

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

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

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

Wouldn’t a DSUM function work just as well?

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

It helped me, greatly, millions thanks

Raj

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.

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.

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

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.

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

Sam, why not just use sumif?

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

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

Hi Anand,

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

Regards

Kanwaljit

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

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.

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.

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

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.

Hi ATIF

i have a solution ;)

=vlookup($A$2;sheet1!$A:$T;column(C3);false)

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.

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

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

http://www.dailydoseofexcel.com/archives/2010/09/22/case-sensitive-lookup/

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