+ Reply to Thread
Results 1 to 3 of 3

CONCATENATE as table_array in VLOOKUP

  1. #1
    Registered User
    Join Date
    02-14-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    18

    CONCATENATE as table_array in VLOOKUP

    To identify the last row with data in the table on Sheet2 I have this formula in cell D1 of Sheet1 as a reference:
    =SUMPRODUCT(MAX((Sheet2!$A:$A<>"")*(ROW(Sheet2!A:A))))

    I thought to add the above formula to a CONCATENATE formula to be indirectly reference in the VLOOKUP formula in cell B2 of Sheet1, like this:
    =CONCATENATE("Sheet2!$A$2:$B$",SUMPRODUCT(MAX((Sheet2!$A:$A<>"")*(ROW(Sheet2!A:A)))))

    This VLOOKUP formula, in cell B2, with the INDIRECT reference, works fine:
    =VLOOKUP(A2,INDIRECT($D$2),2,FALSE)

    !!!BUT!!! When I try to combine the formulas, it does not work, see cell B3:
    =VLOOKUP(A3,CONCATENATE("Sheet2!$A$2:$B$",SUMPRODUCT(MAX((Sheet2!$A:$A<>"")*(ROW(Sheet2!A:A))))),2,FALSE)

    How can I get the information in cell D2 (CONCATENATE formula) to work as the table_array in the VLOOKUP formula?


    *I reviewed the thread with a very similar title but I did not see how it related to my specific issue.
    Attached Files Attached Files
    Last edited by chrisboughter; 03-23-2017 at 09:28 AM. Reason: Add Attachment

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,714

    Re: CONCATENATE as table_array in VLOOKUP

    Try

    =VLOOKUP(A3,INDIRECT("Sheet2!$A$2:$B$"&SUMPRODUCT(MAX((Sheet2!$A:$A<>"")*(ROW(Sheet2!A:A))))),2,0)

  3. #3
    Registered User
    Join Date
    02-14-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: CONCATENATE as table_array in VLOOKUP

    Hi John,

    That seems to work perfectly!!! Not that I thought of much, but, I certainly did not think to indirectly reference the string combined with "&". Flipping brilliant!!!

    Thank You!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] vlookup using multiple table_array
    By iamhsn in forum Excel General
    Replies: 12
    Last Post: 07-18-2012, 03:24 PM
  2. table_array:VLOOKUP help
    By gigiw1986 in forum Excel General
    Replies: 3
    Last Post: 08-28-2010, 02:22 PM
  3. VLOOKUP With static Table_Array
    By clong in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2008, 08:12 PM
  4. dynamic table_array in vlookup
    By kathy32181 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2007, 07:12 PM
  5. [SOLVED] Vlookup with function in table_array
    By JoshuaEyer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-15-2005, 11:05 AM
  6. vlookup, table_array in several sheets
    By stakle in forum Excel General
    Replies: 4
    Last Post: 04-17-2005, 03:36 PM
  7. VLOOKUP TABLE_ARRAY
    By veljo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-31-2005, 08:06 PM
  8. [SOLVED] Insert Vlookup into table_array of Vlookup with named range
    By Denise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-24-2005, 07:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1