VLOOKUP with Structured Reference to Table Header

J

Julien Bouvier

Hi, I am trying to use VLOOKUP to find data in a table named: PartsTable. I
want to reference the column using a structured reference. I want to look in
column 4, which is the Weight column:

this works fine: VLOOKUP(//a part number//,PartsTable,4,FALSE)

this wont work: VLOOKUP(//a part number//,PartsTable,PartsTable[Weight],FALSE)

it returns #VALUE! and the VLOOKUP is on the same sheet as the PartsTable

I find it strange that this doesnt work as the automatic filling tool shows
all column headers when I write "PartsTable[". The syntax seems quite
logical. Thank you for your help!
 
R

Roger Govier

Hi Julien

I take it you are using XL2007.

The same syntax does not apply to Pivot Tables, as it does to Tables.
Tale a look at the GetPivotdData function.
 
J

Julien Bouvier

Are you suggesting I should use a pivot table instead of an ordinary table?

Roger Govier said:
Hi Julien

I take it you are using XL2007.

The same syntax does not apply to Pivot Tables, as it does to Tables.
Tale a look at the GetPivotdData function.

--
Regards
Roger Govier

Julien Bouvier said:
Hi, I am trying to use VLOOKUP to find data in a table named: PartsTable.
I
want to reference the column using a structured reference. I want to look
in
column 4, which is the Weight column:

this works fine: VLOOKUP(//a part number//,PartsTable,4,FALSE)

this wont work: VLOOKUP(//a part
number//,PartsTable,PartsTable[Weight],FALSE)

it returns #VALUE! and the VLOOKUP is on the same sheet as the PartsTable

I find it strange that this doesnt work as the automatic filling tool
shows
all column headers when I write "PartsTable[". The syntax seems quite
logical. Thank you for your help!
 
H

Herbert Seidenberg

VLOOKUP fails because it expects an integer.
Table1[Weight] is an array of data.
In place of the column index number (4), you can use this:
COLUMN(Table1[Weight])-MIN(COLUMN(Table1))+1
(if you are desperate)
Otherwise you might consider this formula:
=INDEX(Table1[Weight],MATCH(Target,Table1[PN],0))
 
R

Roger Govier

Sorry Julien
I totally misread your posting.
Ignore my response

--
Regards
Roger Govier

Julien Bouvier said:
Are you suggesting I should use a pivot table instead of an ordinary
table?

Roger Govier said:
Hi Julien

I take it you are using XL2007.

The same syntax does not apply to Pivot Tables, as it does to Tables.
Tale a look at the GetPivotdData function.

--
Regards
Roger Govier

message
Hi, I am trying to use VLOOKUP to find data in a table named:
PartsTable.
I
want to reference the column using a structured reference. I want to
look
in
column 4, which is the Weight column:

this works fine: VLOOKUP(//a part number//,PartsTable,4,FALSE)

this wont work: VLOOKUP(//a part
number//,PartsTable,PartsTable[Weight],FALSE)

it returns #VALUE! and the VLOOKUP is on the same sheet as the
PartsTable

I find it strange that this doesnt work as the automatic filling tool
shows
all column headers when I write "PartsTable[". The syntax seems quite
logical. Thank you for your help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top