VLOOKUP use column name not number

[kathy]

Is there a way to use VLOOKUP to search for a column name and return the
value in that column, rather search for a column number.

 

[Glenn]

Is there a way to use VLOOKUP to search for a column name and return the
value in that column, rather search for a column number.

Use INDEX/MATCH. Look here:

http://www.contextures.com/xlFunctions03.html#IndexMatch2

 

[T. Valko]

Something like this...

=VLOOKUP("this",A:J,MATCH("column_name",A1:J1,0),0)

 

[Gord Dibben]

One method.

Assuming names are titles in row 1 of a lookup table of range A1:F10

Insert>Name>Define

Type the title name from A1.....gord

Refers to =1

Add........type the title name from B1.......kathy

Refers to =2

Do for each name in A1:F1

Formula in H1 =VLOOKUP(G1,$A$1:$F$10,kathy,FALSE)

Note: if spaces in names like gord dibben, use gord_dibben


Gord Dibben MS Excel MVP

 

[kathy]

=VLOOKUP(A4,History1!B1:BU218,MATCH('Income
Variance'!B3,History1!B1:BU197,0),0)
I tried this and get #N/A.

A4 is the row name that is to be looked up.
history1 B1:bu218 is the worksheet name to find the data on
'income variance'! B3 is the column title to be looked up('income variance'
is the name of the worksheet that is looking for the information)
history1 B1:bu218 is the worksheet with the data on it again

Am I missing something?

 

[T. Valko]

...MATCH('Income Variance'!B3,History1!B1:BU197,0)...

The lookup_array argument in MATCH must be a one dimenonsional array. Try it
like this...

=VLOOKUP(A4,History1!B1:BU218,MATCH('Income
Variance'!B3,History1!B1:BU1,0),0)

 

[kathy]

Thank you, that worked!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[T. Valko]

Good deal. Thanks for the feedback!