Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 17th 08, 10:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2008
Posts: 2
Default 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   Report Post  
Old December 17th 08, 11:27 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,480
Default 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   Report Post  
Old December 18th 08, 01:33 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2008
Posts: 2
Default 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   Report Post  
Old December 18th 08, 02:34 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,180
Default 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   Report Post  
Old December 19th 08, 12:34 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,480
Default 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
news
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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Making a structured reference absolute? Ted M H Excel Worksheet Functions 5 April 14th 08 04:13 AM
Replacing a Table-array with a cell reference in vlookup Allan Excel Worksheet Functions 4 January 15th 08 02:57 PM
Structured references & Quickbooks data Coco Excel Worksheet Functions 0 March 3rd 07 04:34 AM
use cell reference,whose contents= a table array name for Vlookup Sonic Excel Worksheet Functions 2 March 27th 06 08:29 AM
Using single cell reference as table array argument in Vlookup CornNiblet Excel Worksheet Functions 3 September 22nd 05 09:15 AM


All times are GMT +1. The time now is 11:19 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017