ExcelBanter - Referencing Table Data

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Referencing Table Data (https://www.excelbanter.com/excel-worksheet-functions/194990-referencing-table-data.html)

Referencing Table Data

In Excell 2007, I have a Table defined as "Scores" with Column's Named
"Name", "Front", "Back", and "TotalScore".

I want to do a VLOOKUP to find the total score for Mike. When I do this:

=VLOOKUP("Mike",Scores,4,FALSE) it will return Mike's total score for me.

But, if I do this:

=VLOOKUP("Mike",Scores,Scores[TotalScore],FASLE) it will give me a #REF!
error.

How can I use VLOOKUP along with the Column names to do a lookup? It is
possible, that as I make changes to the sheet, the columns may move around,
and I don't want to hard code anything.

Thanks,
Steve

Referencing Table Data

Assuming column names are in row 1, and your table beings in column A:

=VLOOKUP("Mike",Scores,MATCH("TotalScore",1:1,0),0 )
--
John C

"Steve Haack" wrote:

In Excell 2007, I have a Table defined as "Scores" with Column's Named
"Name", "Front", "Back", and "TotalScore".

I want to do a VLOOKUP to find the total score for Mike. When I do this:

=VLOOKUP("Mike",Scores,4,FALSE) it will return Mike's total score for me.

But, if I do this:

=VLOOKUP("Mike",Scores,Scores[TotalScore],FASLE) it will give me a #REF!
error.

How can I use VLOOKUP along with the Column names to do a lookup? It is
possible, that as I make changes to the sheet, the columns may move around,
and I don't want to hard code anything.

Thanks,
Steve

Referencing Table Data

Yeah, but that is again making me hard code things. I was looking to use the
naming convention that is already present in tables, that way when things
move around (columns, or even the whole table), things won't break.

Steve

"John C" wrote:

Assuming column names are in row 1, and your table beings in column A:

=VLOOKUP("Mike",Scores,MATCH("TotalScore",1:1,0),0 )
--
John C

"Steve Haack" wrote:

In Excell 2007, I have a Table defined as "Scores" with Column's Named
"Name", "Front", "Back", and "TotalScore".

I want to do a VLOOKUP to find the total score for Mike. When I do this:

=VLOOKUP("Mike",Scores,4,FALSE) it will return Mike's total score for me.

But, if I do this:

=VLOOKUP("Mike",Scores,Scores[TotalScore],FASLE) it will give me a #REF!
error.

How can I use VLOOKUP along with the Column names to do a lookup? It is
possible, that as I make changes to the sheet, the columns may move around,
and I don't want to hard code anything.

Thanks,
Steve