+ Reply to Thread
Results 1 to 11 of 11

Return multiple results based on multiple criteria

  1. 04-04-2017, 03:41 PM #1
    sthomay
    sthomay is offline
    Registered User
    Join Date
    04-04-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    14

    Return multiple results based on multiple criteria

    I have a spreadsheet (condensed version attached) where I am trying to return into Dashboard C14 and down all the results from rawdata (table) where week #=B2 (610) in dashboard and Dashboard type = C10 (eSpecials) in dashboard. I was able to return one result using this array formula:

    MATCH($B$2&$C$10,RawData[Week '#]&RawData[DashboardType],0)

    But I can't figure out how to make it return multiple results using this same idea. If necessary, I can add a column to rawdata where it combines the Week # and dashboard type together to match on. Any help is greatly appreciated!!

    Expected Result: (C14:C17)
    ES6C04
    ES6C04
    ES6C11
    ES6C17
    Attached Files Attached Files
    Last edited by sthomay; 04-04-2017 at 04:05 PM. Reason: Changed attachment
    Reply With QuoteRegister To Reply
  2. 04-04-2017, 03:47 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Return multiple results based on multiple criteria

    Hi, welcome to the forum

    there seems to be a problem with your file, I cannot open it. can you try to upload again?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 04-04-2017, 03:51 PM #3
    sthomay
    sthomay is offline
    Registered User
    Join Date
    04-04-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    14

    Re: Return multiple results based on multiple criteria

    Just uploaded again- I forgot to remove data validation!
    Reply With QuoteRegister To Reply
  4. 04-04-2017, 03:59 PM #4
    avk
    avk is offline
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,223

    Re: Return multiple results based on multiple criteria

    In "C14" what result required. plz mentioned manually.

    atul

    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
    Reply With QuoteRegister To Reply
  5. 04-04-2017, 04:05 PM #5
    sthomay
    sthomay is offline
    Registered User
    Join Date
    04-04-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    14

    Re: Return multiple results based on multiple criteria

    Expected Result: (C14:C17)
    ES6C04
    ES6C04
    ES6C11
    ES6C17
    Reply With QuoteRegister To Reply
  6. 04-04-2017, 04:19 PM #6
    avk
    avk is offline
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,223

    Re: Return multiple results based on multiple criteria

    Try with in "C14"
    Ensure this is Array formula, after written formula : Hold & press [SHIFT+CTRL+ENTER]
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 04-04-2017, 04:30 PM #7
    sthomay
    sthomay is offline
    Registered User
    Join Date
    04-04-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    14

    Re: Return multiple results based on multiple criteria

    That works in C14, so I changed your references to B2 and C10 to $B$2 and $C$10 and copied it down, it's only returning the first result over and over again in cells C14:C17
    Reply With QuoteRegister To Reply
  8. 04-05-2017, 02:51 PM #8
    JeteMc
    JeteMc is online now
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,556

    Re: Return multiple results based on multiple criteria

    Try pasting the following array entered formula* into the formula bar while cell C14 is selected:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Once the formula has been activated, *by simultaneously pressing the Ctrl, Shift and Enter keys, you may copy by dragging the fill handle of cell C14 down to C17.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  9. 04-06-2017, 01:14 PM #9
    sthomay
    sthomay is offline
    Registered User
    Join Date
    04-04-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    14

    Re: Return multiple results based on multiple criteria

    If I enter it as an array formula, I get an #N/A error. If I enter it as a regular formula, I get ES6C11 (Raw Data A14). If I copy it down, I continue getting the list- Raw Data A15, Raw Data A16, etc.
    Reply With QuoteRegister To Reply
  10. 04-06-2017, 01:23 PM #10
    JeteMc
    JeteMc is online now
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,556

    Re: Return multiple results based on multiple criteria

    The attached file shows that results that I get when array entered. They match the expected results from post #5.
    Let us know if you have any questions.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 04-06-2017, 01:30 PM #11
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,193

    Re: Return multiple results based on multiple criteria

    I add IFERROR but otherwise JeteMc's works fine for me

    =IFERROR(INDEX(rawdata[Campaign Id],SMALL(IF(B$2=rawdata[Week '#],IF(C$10=rawdata[EmailType],ROW(rawdata[Campaign Id])-1)),ROW(A1))),"")

    As an array-formula
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Multiple search criteria to return multiple results
    By johnmacs5 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-01-2016, 05:17 PM
  2. [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
  3. [SOLVED] Return multiple results when a criteria is met
    By kpodoh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2014, 11:49 AM
  4. Select multiple criteria and return multiple results
    By macquhele in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-03-2013, 08:15 AM
  5. User Form to Search multiple Criteria to return multiple Results
    By Calieth in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-16-2011, 11:57 AM
  6. Count Multiple Entries, Return Multiple Rows, Based On 3 Criteria
    By gtj_global in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-20-2008, 06:40 PM
  7. Return results based on multiple criteria
    By Bryce in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-28-2005, 08:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1