Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup Table Array (98)

    My question pertains to the second element in the parenthesis within the VLOOKUP formula, VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) ... "TABLE_ARRAY." Instead of actually typing in the table array such as 'Sheet1.xls'!$B$2:$AI$471, I have a cell reference with this Sheet1 table_array reference (i.e., A1). However, when I create the formula VLOOKUP(lookup_value,A1,col_index_num,range_lookup ), the result returned is #VALUE!.

    The main question is ... can I use a cell reference that indicates a table_array instead of typing in the actual table_array name directly into the formula???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup Table Array (98)

    Use INDIRECT(A1) instead of A1.

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup Table Array (98)

    Care to post your formula? You've got quite a few columns in your table. Instead of A1 why not name the range AA and avoid using indirect

  4. #4
    New Lounger
    Join Date
    Mar 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup Table Array (98)

    I want cells G3 and G4 to obtain their values using a VLOOKUP with the Table_Array name designated in cell G2 which comes from the tab labelled Daily Trending.

  5. #5
    Lounger
    Join Date
    Feb 2003
    Location
    Bournemouth, Dorset, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup Table Array (98)

    Try this...
    Just name the range, and then reference it in the vlookup formula.
    See the attached file...

  6. #6
    New Lounger
    Join Date
    Mar 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup Table Array (98)

    I don't think it's that easy. The table I'm referencing is another excel worksheet in another external file. I tried to recreate the name of the file in the worksheet that I am referencing from. Take a look at the attachment again.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Vlookup Table Array (98)

    Your lookup range in cell [G2] has an error in it.
    Remove the ' ,14 ' at the end of the reference.

    zeddy

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vlookup Table Array (98)

    INDIRECT will work with external files ONLY if the external file is also open.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •