VLOOKUP - using column name instead of column number

[m24jones]

I would like to do a VLOOKUP (or similar operation) to grab information from a column based on a column name instead of column number.

For example, instead of:

=vlookup(a2,t5:z10,4,FALSE)

I would like the formula to read:

=vlookup(a2,t5:t10,"COLUMN_NAME",FALSE)

Thanks

 

[Aladin Akyurek]

What is "COLUMN_NAME" ?

 

[m24jones]

"COLUMN_NAME" is where I would like to put a column header name instead of column number into the vlookup (or similar formula).

 

[just_jon]

Something like this, I intuit ... :D

Book3
	A	B	C	D	E
1		2002	2003	2004	2005
2	AAA	1	2	3	4
3	BBB	8	7	6	5
4	CCC	9	10	11	12
5	DDD	16	15	14	13
6
7	10
Sheet1

 

[Aladin Akyurek]

"COLUMN_NAME" is where I would like to put a column header name instead of column number into the vlookup (or similar formula).

Let T4:Z4 house these headers...

=VLOOKUP(A2,$T$5:$Z$10,MATCH("COLUMN_NAME",$T$4:$Z$4,0),0)

0 means the same thing as FALSE both in MATCH and VLOOKUP.

 

[m24jones]

All I get is "#N/A" - any idea why?

All I get is "#N/A" - any idea why?

my formula:
=VLOOKUP(J8,$D$2:$G$5,MATCH("2003",D2:G2,0),0)

 

[Aladin Akyurek]

Re: All I get is "#N/A" - any idea why?

Remove double quotes around 2003...

=VLOOKUP(J8,$D$3:$G$5,MATCH(2003,$D$2:$G$2,0),0)

 

[PaddyD]

for a little more on usiong column labels as names see:

http://www.mrexcel.com/board2/viewtopic.php?p=102895&highlight=create#102895

& the other linked thread.

 

[m24jones]

PROBLEM - formula keeps pulling data from bottom of set

My Formula:
VLOOKUP($A$5,'FREEDOM'!$A$1:$R$15,MATCH("Revenue-1997",'FREEDOM'!$A$1:$R$1,0))

Result:
On the Freedom tab there are 10 lines of data
-the formula above pulls the result from the correct column, but not from the correct row (in the case of the formula above, it should pull from row 3)
-if i delete row 10 then the formula pulls data from row 9


Any ideas?

 

[Aladin Akyurek]

Re: PROBLEM - formula keeps pulling data from bottom of set

My Formula:
VLOOKUP($A$5,'FREEDOM'!$A$1:$R$15,MATCH("Revenue-1997",'FREEDOM'!$A$1:$R$1,0))

Result:
On the Freedom tab there are 10 lines of data
-the formula above pulls the result from the correct column, but not from the correct row (in the case of the formula above, it should pull from row 3)
-if i delete row 10 then the formula pulls data from row 9


Any ideas?

The formula

=VLOOKUP($A$5,'FREEDOM'!$A$1:$R$15,MATCH("Revenue-1997",'FREEDOM'!$A$1:$R$1,0))

does an approximate match. Try exact match...

=VLOOKUP($A$5,'FREEDOM'!$A$1:$R$15,MATCH("Revenue-1997",'FREEDOM'!$A$1:$R$1,0),0)

Why do your ranges change with every post? :D