A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

VLOOKUP with Structured Reference to Table Header



 
 
Thread Tools Display Modes
  #1  
Old December 17th 08, 09:59 PM posted to microsoft.public.excel.worksheet.functions
Julien Bouvier
external usenet poster
 
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!

Ads
  #2  
Old December 17th 08, 10:27 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_3_]
external usenet poster
 
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  
Old December 18th 08, 12:33 AM posted to microsoft.public.excel.worksheet.functions
Julien Bouvier
external usenet poster
 
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  
Old December 18th 08, 01:34 AM posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
external usenet poster
 
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  
Old December 18th 08, 11:34 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_3_]
external usenet poster
 
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!
>> >

>>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 01:57 PM
Structured references & Quickbooks data Coco Excel Worksheet Functions 0 March 3rd 07 03: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:02 PM.


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