VLOOKUP with Structured Reference to Table Header
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! |
VLOOKUP with Structured Reference to Table Header
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" wrote in 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! |
VLOOKUP with Structured Reference to Table Header
Are you suggesting I should use a pivot table instead of an ordinary table?
"Roger Govier" wrote: 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" wrote in 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! |
VLOOKUP with Structured Reference to Table Header
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)) |
VLOOKUP with Structured Reference to Table Header
Sorry Julien
I totally misread your posting. Ignore my response -- Regards Roger Govier "Julien Bouvier" wrote in message ... Are you suggesting I should use a pivot table instead of an ordinary table? "Roger Govier" wrote: 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" wrote in 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! |
All times are GMT +1. The time now is 12:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com