+ Reply to Thread
Results 1 to 6 of 6

Extracting Bottom 3 Values From a List Selected from a Drop-Down List

  1. #1
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    44,143

    Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    Assuming I have a drop-down box in A1 with a list of the subjects in the grid below, how would I return the bottom three values for that subject in B1, B2 and B3? I'm not able to use a pivot table for this - it needs to be a formula, please. If there are duplicate values, they should not be ignored. My table is longer with values ranging from 3 to 8.

    Excel 2016 (Windows) 32 bit
    JC
    JD
    JE
    JF
    JG
    JH
    JI
    JJ
    JK
    JL
    JM
    JN
    3
    Academic PE
    Art
    Biology
    BTEC IT
    Business Studies
    Chemistry
    Classical Civilisation
    Drama
    Design Technology
    Economics
    English Literature
    English Language
    4
    5
    5
    5
    5
    6
    7
    8
    8
    7
    9
    7
    10
    4
    11
    5
    5
    12
    4
    4
    13
    4
    14
    5
    6
    15
    7
    6
    7
    16
    5
    17
    6
    3
    18
    7
    5
    19
    4
    4
    20
    6
    6
    6
    21
    7
    7
    Sheet: Pupils Dashboard
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,990

    Re: Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    I hate to say this to a moderator but you really should post a sample.

    Paste this formula in B1 and fill down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    44,143

    Re: Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    I did post a sample in the body of my post. There is no need for an attachment in this case, but thank you for your reply. I'll try your suggestion in the morning.

  4. #4
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    Hi,

    I admit that I played football during English classes.
    So if I'm wrong please excuse me but I understood that the last three items are required, not the smaller ones.
    If so, try this array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I defined a name "rng" with
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see attached
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    44,143

    Re: Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    Thanks for your reply. It was indeed the smallest three values that I required.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    44,143

    Re: Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    Quote Originally Posted by mehmetcik View Post
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thank you - works perfectly.

+ 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] Active X drop down list - always return to the item in the list that was selected.
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-14-2017, 09:41 AM
  2. [SOLVED] Pop up box appearing when certain values are selected from drop down list
    By messime in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2016, 06:23 PM
  3. Replies: 5
    Last Post: 03-10-2016, 09:32 AM
  4. Replies: 4
    Last Post: 09-09-2015, 03:49 PM
  5. Replies: 2
    Last Post: 02-19-2014, 05:14 PM
  6. [SOLVED] Sum The Values As per the Selected in Drop Down List
    By get2mandar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-11-2012, 03:09 AM
  7. [SOLVED] Drop Down starts at bottom of list
    By John Jahn in forum Excel General
    Replies: 0
    Last Post: 07-19-2011, 11:09 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