Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a structured reference absolute? | Excel Worksheet Functions | |||
Replacing a Table-array with a cell reference in vlookup | Excel Worksheet Functions | |||
Structured references & Quickbooks data | Excel Worksheet Functions | |||
use cell reference,whose contents= a table array name for Vlookup | Excel Worksheet Functions | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions |