+ Reply to Thread
Results 1 to 4 of 4

Looking to return multiple cell values match multiple criteria

  1. #1
    Registered User
    Join Date
    04-14-2017
    Location
    Plymouth, minnesota
    MS-Off Ver
    2016
    Posts
    32

    Looking to return multiple cell values match multiple criteria

    I'm looking for a formula to find multiple matches that match multiple criteria.

    Attached is what I'm looking to do, in column G I'd like it to return anyone from Column C that has a zero is column D and where column A includes the number 47.
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,931

    Re: Looking to return multiple cell values match multiple criteria

    If you don't mind a helper column, this in some column, for example E, =IF(AND(LEFT(A2,2)="47",D2=0),C2,"") dragged down.
    then this in G =IFERROR(INDEX($E$2:$E$12,MATCH(0,INDEX(COUNTIF($G$1:G1,$E$2:$E$12&""),),0))&"","") dragged down until comes back blank.
    adjust the ranges to suit your data.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    195

    Re: Looking to return multiple cell values match multiple criteria

    Another option without helper :

    In G2, copied down :

    =IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(A$2:A$12)/(IMREAL(A$2:A$12&"i")=47)/(D$2:D$12=0),ROW(A1))),"")

    Regards
    Bosco

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    New learning curve! Office 365
    Posts
    25,384

    Re: Looking to return multiple cell values match multiple criteria

    ...or if you don't understand "complex numbers" (IMREAL)...

    =IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(A$2:A$100)/(ISNUMBER(SEARCH(47,A$2:A$100)))/(D$2:D$100=0),ROWS(G$2:G2))),"")

    see sheet.
    Attached Files Attached Files
    Glenn



+ 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. Replies: 5
    Last Post: 05-27-2019, 03:46 PM
  2. Match 3 criteria and return multiple values
    By embether13 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 01-01-2019, 09:16 AM
  3. Match multiple criteria and return multiple values.
    By lodewyj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-06-2017, 07:57 PM
  4. [SOLVED] Array formula to return list of values that match multiple criteria
    By TFiske in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-30-2016, 01:57 PM
  5. [SOLVED] Index/Match with Multiple Criteria and Multiple return values
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2015, 10:27 AM
  6. [SOLVED] How to return multiple values to a single cell based on multiple criteria
    By lwallace in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 06:32 AM
  7. [SOLVED] How to return multiple values that match a single search criteria?
    By JSallen in forum Excel General
    Replies: 4
    Last Post: 11-28-2012, 11:49 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