+ Reply to Thread
Results 1 to 11 of 11

Index and Match formula using an Array Criteria List

  1. #1
    Registered User
    Join Date
    08-22-2012
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    27

    Index and Match formula using an Array Criteria List

    Hi Guys,

    I am really struggling to get my formula to work and am hoping someone can lend me a hand.

    I've attached the file to give a better idea of what I'm trying to achieve but please find the background below:

    What I'm trying to do is to see if a DUO GCID exists in the same GUO GCID as in the Current GUO GCID List. To do this I need to lookup the DUO GCID in the GCID list to get it's GUO GCID.

    So for example:
    [B]

    GCID GUO GCID DUO GCID RESULT
    1 1 1 Exists - as in GCID List and Criteria GUO GCID List
    2 1 1 Exists - as in GCID List and Criteria GUO GCID List
    3 5 3 Exists - as in GCID List and Criteria GUO GCID List
    4 2 4 Not Exists - as not in Criteria GUO GCID list though in GCID List


    Criteria GUO GCID: 1, 5

    I tried using the below formula but due to the blanks in my criteria list it's calculating them and returning a TRUE result.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I'm not honestly sure if SUMPRODUCT is the best route so all recommendations welcome. Please let me know if any questions and I'll try answer them best I can.
    Attached Files Attached Files
    Last edited by lewny1983; 05-25-2020 at 08:00 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and Match formula using an Array Criteria List

    Hello, neighbour!

    Not exactly sire, but maybe this:

    =IF([@[Client Name]]="","",SUMPRODUCT(--(INDEX(tblData,MATCH(C7,[GCID],0),3)=$D$3:$H$3))>0)

    Are you still using Excel 2010?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    08-22-2012
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    27

    Re: Index and Match formula using an Array Criteria List

    Sorry need to update my profile, I'm on 365 ProPlus. For your suggestion, it's no issue about the Client Name being blank, it's the blanks in the criteria list at the top as we would need to encompass all non blanks as part of the criteria.
    Last edited by AliGW; 05-25-2020 at 07:36 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and Match formula using an Array Criteria List

    OK - so please provide a better set of sample data with expected outcomes mocked up.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and Match formula using an Array Criteria List

    Can you explain in WORDS why number 4 does not meet the criteria? Where do we find the lookup data? Sorry - but none of this is clear to me. Remember, it's obvious to you, but not to anyone unfamiliar with your type of data.

  6. #6
    Registered User
    Join Date
    08-22-2012
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    27

    Re: Index and Match formula using an Array Criteria List

    Hopefully the attached provides some clarity.

    I've also amended my first post with better wording.
    Attached Files Attached Files
    Last edited by lewny1983; 05-25-2020 at 08:02 AM.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and Match formula using an Array Criteria List

    Gotcha!

    Try this:

    =IF(ISNA(HLOOKUP(VLOOKUP(E8,tblData,2,0),$D$4:$H$4,1,0)),"Does Not Exist","Exists")

  8. #8
    Registered User
    Join Date
    08-22-2012
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    27

    Re: Index and Match formula using an Array Criteria List

    Ah so simple when I read this through. Amazing. Thank you for helping a fellow Ipswich Townian (pretty sure no one says that)
    Last edited by AliGW; 05-25-2020 at 08:10 AM. Reason: Please don't quote unnecessarily!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and Match formula using an Array Criteria List

    No problem!

    I'm from Derby originally, but moved down here 9 years ago. I'm afraid Sir is a Tottenham supporter ...

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

  10. #10
    Registered User
    Join Date
    08-22-2012
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    27

    Re: Index and Match formula using an Array Criteria List

    Will do.

    I won't mention my Liverpool allegiance.....

    Thanks again

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index and Match formula using an Array Criteria List

    Red or blue variety?

    I know more about footy than I'd like as I am (usually) subjected to Soccer Saturday and Sunday every weekend!!!

+ 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] Issue with array formula index and match giving N/A WHEN 3 CRITERIA is involved
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-08-2020, 12:53 PM
  2. Replies: 6
    Last Post: 12-13-2017, 10:08 AM
  3. Need an array possibly INDEX/MATCH formula with multiple lookup criteria.
    By TheClaw2323 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-25-2015, 09:59 AM
  4. Array Formula - Index / Match - Dedupe & filter criteria
    By rtiger in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-29-2014, 03:18 PM
  5. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  6. Replies: 4
    Last Post: 03-27-2014, 01:09 PM
  7. [SOLVED] Comparing list using INDEX MATCH etc. in an array-formula
    By Saturn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-14-2013, 09:02 AM

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