VLOOKUP - using column name instead of column number

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 24, 2003

• #2

What is "COLUMN_NAME" ?

 

Upvote 0

M

m24jones

Board Regular

Joined

May 19, 2002

Messages

61

• Mar 24, 2003

• #3

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

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Mar 24, 2003

• #4

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

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 24, 2003

• #5

m24jones said:

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

Click to expand...

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.

 

Upvote 0

M

m24jones

Board Regular

Joined

May 19, 2002

Messages

61

• Mar 24, 2003

• #6

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)

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 24, 2003

• #7

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)

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Mar 24, 2003

• #8

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.

 

Upvote 0

M

m24jones

Board Regular

Joined

May 19, 2002

Messages

61

• Mar 24, 2003

• #9

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?

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 24, 2003

• #10

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

m24jones said:

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?

Click to expand...

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

 

Upvote 0