+ Reply to Thread
Results 1 to 5 of 5

Create "Hot List" That Meets Multiple Criteria From Master List

  1. #1
    Registered User
    Join Date
    04-09-2017
    Location
    Gilbert, AZ
    MS-Off Ver
    365 (Subscription)
    Posts
    20

    Question Create "Hot List" That Meets Multiple Criteria From Master List

    I have a large master list of baseball players and their associated stats. What I am trying to do is to create a shorter "Hot List" of only those players which meet all four of the minimums I have set for these stats.

    I have attached a sample of what I'm trying to do:
    • Hot List: Highlighted in YELLOW is the expected result
    • Hot List: Minimum = minimum threshold for each stat (must be >= to these values)
    • Master List: Complete list of players and their statistics; GREEN are >= the minimum for each stat on Hot List

    The expected result is the list of players that meet ( are >=) all four of the minimum values defined on the Hot List tab.

    Any ideas? Thank you for your help!

    Best regards,
    Eric
    Attached Files Attached Files
    Last edited by Eric Alan; 05-08-2017 at 01:12 PM. Reason: Typos FTW

  2. #2
    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,001

    Re: Create "Hot List" That Meets Multiple Criteria From Master List

    In E2

    =IFERROR(INDEX('Master List'!$B$2:$B$21,SMALL(IF(('Master List'!$C$2:$C$21>='Hot List'!$B$2)*('Master List'!$D$2:$D$21>='Hot List'!$B$3)*('Master List'!$E$2:$E$21>='Hot List'!$B$4)*('Master List'!$F$2:$F$21>='Hot List'!$B$5),ROW('Master List'!$B$2:$B$21)-ROW($B$2)+1,""),ROWS($B$2:B2))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  3. #3
    Registered User
    Join Date
    04-09-2017
    Location
    Gilbert, AZ
    MS-Off Ver
    365 (Subscription)
    Posts
    20

    Re: Create "Hot List" That Meets Multiple Criteria From Master List

    That's awesome! Thanks a ton.

  4. #4
    Registered User
    Join Date
    12-17-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Create "Hot List" That Meets Multiple Criteria From Master List

    would you mind submitting a sample portion of the working spreadsheet?

  5. #5
    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,001

    Re: Create "Hot List" That Meets Multiple Criteria From Master List

    See the attached.
    Attached Files Attached Files

+ 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] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  2. Create a "Pop-Up" list from which I can select from a list of macro's to run?
    By Clinno in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-02-2015, 08:49 PM
  3. If meets criteria in multiple columns then place "X"
    By ajeffres in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-21-2014, 04:20 PM
  4. How to generate from larger list a short list that meets multiple criteria
    By ablacksheep in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-28-2014, 09:37 AM
  5. Create a Drop Down list if another cell meets certain criteria
    By rini11 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-18-2013, 03:18 AM
  6. Replies: 8
    Last Post: 03-07-2011, 01:43 PM
  7. problem with tip "Retrieving an Item from a List that Meets Multiple Criteria"
    By filoberto in forum ExcelTip.com Feedback / Comments / Suggestions
    Replies: 1
    Last Post: 02-25-2006, 08:15 PM

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