+ Reply to Thread
Results 1 to 15 of 15

Formula to Return Bottom 3 Values From a Table Based on a Criteria

  1. #1
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Formula to Return Bottom 3 Values From a Table Based on a Criteria

    I don't want to use a Pivot Table to do this, i really need a formula!

    I have a list of people, where they are based (region) and a score for their performance.

    I can use SMALL to return the bottom 3 scores no problem, and then use index/match to give me the name of the person who achieved that score.

    But i then have an issue returning the corresponding person if more than 1 person has achieved the same score.

    On top of this, i would like the user to be able to select which region to see the bottom 3 performers for.

    Any help would be greatly appreciated! As i'm stuck!

    Thanks in advance

    J
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    F18= =index($a$18:$a$27,match(small($c$18:$c$27,e18),c18:c27))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    This is better solution
    =INDEX(IF($C$18:$C$27=F18,$A$18:$A$27),E18)

  4. #4
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    Thanks very much, that seems to solve the issue of two people achieving the same score,

    but any help on allowing the user to select which region to see that bottom 3 engineers for?

  5. #5
    Forum Contributor
    Join Date
    09-11-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2013
    Posts
    103

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    @ eisayew

    Both of your formula have some error.

    Attached is the simulation.
    Attached Files Attached Files
    Click * to reward me...
    Thank you...

  6. #6
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    Good spot Crzu, obviously my real data is much larger and the scores are randomly spread!

  7. #7
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    Quote Originally Posted by JungleJme View Post
    Good spot Crzu, obviously my real data is much larger and the scores are randomly spread!
    I am sorry,
    I am trying to correct it

  8. #8
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    Quote Originally Posted by eisayew View Post
    I am sorry,
    I am trying to correct it
    No problems, i thank you for your help! I'm still banging my head against a brick wall in the background so all/any help is really appreciated!

  9. #9
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    I think the example will helps you.

    Let me know if you have any questions.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  10. #10
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    OK, so i've figured out how to filter the result based on region.

    I've added a helper column that populates the score depending on which region is selected. Then you can simply do the SMALL calculation on the helper column to return the 3 smallest values.

    However, i still have the same problem that if two engineers get the same score, it will only return the name of the 1st Engineer
    Attached Files Attached Files

  11. #11
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    Did you see post#9?

  12. #12
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    Sorry Fotis, missed it! Must have been posting myself!

    I also came up with a result using a helper column on the data to show the value if the region was selected, but yours is tidier!

    thanks a lot to all of you

  13. #13
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    You are welcome

    Thanks for the reb*

  14. #14
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    I know its a bit late
    I was trying to solve this formula without helper column. But with a helper column I have this formula
    =INDEX($A$18:$A$27,MATCH(1,($C$18:$C$27=F18)*(COUNTIF($F$18:F18,F18)=$D$18:$D$27),0)) CSE
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Formula to Return Bottom 3 Values From a Table Based on a Criteria

    Thanks Eisayew! I'll try yours out as well, just out of professional curiosity

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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