+ Reply to Thread
Results 1 to 11 of 11

Return multiple results based on multiple criteria

  1. #1
    Registered User
    Join Date
    04-04-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    5

    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

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,366

    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

  3. #3
    Registered User
    Join Date
    04-04-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    5

    Re: Return multiple results based on multiple criteria

    Just uploaded again- I forgot to remove data validation!

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,894

    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".

  5. #5
    Registered User
    Join Date
    04-04-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    5

    Re: Return multiple results based on multiple criteria

    Expected Result: (C14:C17)
    ES6C04
    ES6C04
    ES6C11
    ES6C17

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,894

    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.

  7. #7
    Registered User
    Join Date
    04-04-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    5

    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

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    12,888

    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.

  9. #9
    Registered User
    Join Date
    04-04-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    5

    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.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    12,888

    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

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

    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 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] 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1