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

 

[geno335]

Follow up question to this aged thread...

I have similar issue I'm looking to combat. Namely, multiple people are using a spreadsheet with exported data, however, depending on the column configuration on their system defaults the column that is important will be different.

Current formula returns a #NAME? result

=VLOOKUP(A2,'CE'!A:M,MATCH(Calculated Bal.,'CE'!1:1,0),FALSE)


Here's the goal:
Sheet 1: List of accounts
Sheet CE: List of accounts with multiple columns of data
Sheet 1: Return the result of the Column titled Calculated Bal. for the respective account

In essence, first performing a search of sheet CE for the column that Calculated Bal. happens to be in, second, performing the vlookup as normal.

Any advice? Also, would want to imbed the if(isna functionality at the beginning).

 

[shg]

Assuming that should be a literal string,

=VLOOKUP(A2, 'CE'!A:M, MATCH("Calculated Bal.", 'CE'!1:1, 0), FALSE)

 

[ssingh75]

Hi Expert,

I am using below formula to get the value to matched columns. but its giving me the wrong column data.
=VLOOKUP(C2,'ABC.xls'!$A:$XFC,MATCH('[DEF.xlsm]Sheet1'!$M$15,$A$1:$XFC$1,0),FALSE)

pls suggest.

 

[JORGEMELS]

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

Hello Experts

I have a similar issue but the tricky part is that i want to Look for the header in the other file without knowing the exact starting column

Example: some times the file has the headers in column 4 and sometimes in column 2 and so on

Any ideas?

Thanks in advance

This is what i have but returns #N/A

=VLOOKUP($C11,Sheet2!C:AD,MATCH($I$8,Sheet2!$B$4:$AC$4,0),0)

 

[dosdan]

VLOOKUP - column index numbers as named ranges

I came up with this idea because it was doing my head in working with multi-column VLOOKUP tables. I don't know if it's widely known, (excuse me if it's obvious), but it seems useful.

When working with VLOOKUP tables with multiple lookup column, instead of using column index numbers, make each index number value a named cell. Then you can use these named values in the VLOOKUP formula to greatly improve readability.

For example, in a Solar PV spreadsheet, I have a VLOOKUP table, which I've named "Tariffs". The columns with the red header labels are the values I make use of. These values have GST and any discount applied. In row 5 I've placed a list of column index numbers and named each one.


https://dl.dropbox.com/s/pwt<wbr>aekijcqbas4u/PV%20Tariffs%20Table.jpg

When I want to lookup the current T.31 tariff value, I use

=VLOOKUP(Date_2way,Tariffs,T.31)

instead of

=VLOOKUP(Date_2way,Tariffs,13)

"Date_2way" is a named range of consecutive dates, starting from when 2-way electricity metering was activated on 9/03/12.

I compute a daily electricity bill:


https://dl.dropbox.com/s<wbr>/lio7s6va3ap0fm2/Energy_log.jpg

"Export Credit":
=IF(Generated<>0,Exported*VL<wbr>OOKUP(Date_2way,Tariffs,FIT),"")

"Generated Credit":
=IF(Generated<>0,(Ge<wbr>nerated-Exported)*VLOOKUP(Date_2way,Tariffs,T.11) +Export_Credit,"")

"Today's Bill":
=IF(Generated<>0,
-Export_Credit
-VLOOKUP(Date_2way,Tariffs,Pensioner_Rebate)
-VLOOKUP(Date_2way,Tariffs,Daily_POT_disc)
+ (Imported*VLOOKUP(Date_2way,Tariffs,T.11))
+ VLOOKUP(Date_2way,Tariffs,Serv<wbr>ice_Metering_Charge_with_Solar)
+ (HW*VLOOKUP(Date_2way,Tariffs,T.31)),
"")

Dan.

 

[dosdan]

Re: VLOOKUP - column index numbers as named ranges

. In row 5 I've placed a list of column index numbers and named each one.

By the way. I don't hardwired the column index number values so, if, I ever move the table, I don't stuff things up. Instead I use a formula in each of these named cells to work out the column number the index number is situated in. And since I started the "Tariffs" table in col B, I need to account for this column offset. So the formula I use in these cells is

=COLUMN() -1

Dan.

 

[dosdan]

Re: VLOOKUP - column index numbers as named ranges

By the way. I don't hardwired the column index number values so, if, I ever move the table, I don't stuff things up. Instead I use a formula in each of these named cells to work out the column number the index number is situated in. And since I started the "Tariffs" table in col B, I need to account for this column offset. So the formula I use in these cells is

=COLUMN() -1

Didn't work the way I expected when I inserted a new column to the LHS of the "Tariffs" table. This will work, where the table originally starts in col B:

=COLUMN()-COLUMN($B:$B)+1

Dan.