+ Reply to Thread
Results 1 to 8 of 8

Excel Table lookup Named Range

  1. #1
    Registered User
    Join Date
    09-22-2015
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    9

    Excel Table lookup Named Range

    I am working with an Excel file that someone else created (attached). The Formula in column F (Table4) does a vlookup to the named range Goal and brings back values from Column R, based on a match between values in E and P. In some cases it is updating the values in column F correctly and other cases it doesn't, for instance the value in F3. In column F, It is an array formula, so I understand you need to do CTRL + SHIFT +ENTER to add { }. I am not that familiar with working with array formulas, and named ranges, so not sure if that might an issue as well. I can't figure out why Column F isn't always updating correctly. Unfortunately, I cannot change the formula or how the spreadsheet is setup, as used for other functions. Thanks for any info to assist.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Excel Table lookup Named Range

    The value in D does not match. You have "G oal" with a space and "Goal" without in the lookup table.

    What VALUE do you want to see in F3?

    I don't see why you think you need to enter the formula as CSE ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    09-22-2015
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    9

    Re: Excel Table lookup Named Range

    AliGW, thanks for the fast reply. Good catch on the error in Column D with the Space in G oal. That was actually a typo on my part, I updated that column D for privacy reasons (it has another word besides goal, but they do match on the original sheet between the Table 1 and Named Range Goal), but it still doesn't work after fixing Column D. Attached is an updated copy.
    As for the CTE, I just mentioned because i noticed the { }. I am just fishing for ideas because this one has me stumped.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Excel Table lookup Named Range

    Try this instead (NOT CSE):

    =LOOKUP(2,1/((Goal[Project]=[@Project])*(Goal[Goal]=[@Goal])*(Goal[Phase]=[@Phase])),Goal[Threshold])
    Attached Files Attached Files
    Last edited by AliGW; 06-27-2020 at 05:46 AM.

  5. #5
    Registered User
    Join Date
    09-22-2015
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    9

    Re: Excel Table lookup Named Range

    Thanks for sending the updated formula. It definately works. Unfortunately, as I mentioned I need to try and figure why the original is not working as it is used for other functions. Thanks.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Excel Table lookup Named Range

    It isn't working because the syntax is completely wrong, so you are going to have to change it wherever it is used because it will be returning incorrect results, I am afraid. If it isn't, it's just luck.
    Last edited by AliGW; 06-27-2020 at 06:30 AM.

  7. #7
    Registered User
    Join Date
    09-22-2015
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    9

    Re: Excel Table lookup Named Range

    thanks again. I appreciate the help.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Excel Table lookup Named Range

    No worries. Sorry to be the bearer of bad news!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. lookup value in a named range (table) and give header information as result
    By cmmchick in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2019, 09:18 AM
  2. Replies: 2
    Last Post: 02-27-2016, 12:18 PM
  3. [SOLVED] Help! Copy named range from excel to access table.
    By Jo2710 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-23-2014, 09:23 PM
  4. Excel Table using a named range
    By ManUBlueJay in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2014, 11:17 AM
  5. [SOLVED] Lookup an entire named table
    By Phily915 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-03-2014, 02:15 PM
  6. Array formulas referencing a named range or named table
    By anrichards22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-03-2013, 11:59 PM
  7. [SOLVED] Lookup in named range
    By JaB in forum Excel General
    Replies: 3
    Last Post: 09-26-2005, 09:05 AM

Tags for this Thread

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