+ Reply to Thread
Results 1 to 11 of 11

Find a reference in column based on topfive value

  1. 08-13-2018, 12:02 PM #1
    sealpino
    sealpino is offline
    Forum Contributor
    Join Date
    11-10-2012
    Location
    Sweden, Stockholm
    MS-Off Ver
    MS Excel 365
    Posts
    175

    Find a reference in column based on topfive value

    I have made a top five list based on 50 rows.
    The top five is presentec in column A as name!
    The top five quantity in column B as a number.

    I want to find a startyear connected to the top 5


    DATABASE
    ALFA, 50, 2019, 2023
    BETA, 45, 2021, 2022
    DELTA, 40, 2019,2021


    Note that top five list is fixed already.
    Reply With QuoteRegister To Reply
  2. 08-13-2018, 12:05 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Find a reference in column based on topfive value

    Can't you just use MIN?

    =MIN(C1:C5)

    I'm not entirely clear about the layout of your data.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 08-13-2018, 12:10 PM #3
    sealpino
    sealpino is offline
    Forum Contributor
    Join Date
    11-10-2012
    Location
    Sweden, Stockholm
    MS-Off Ver
    MS Excel 365
    Posts
    175

    Re: Find a reference in column based on topfive value

    I hope that I can clear it up,
    I have a top 5 list, thx to our members here.
    Now In my so called database there is a value (startyear) I would like to connect that is written on the same row as each top five value.
    Reply With QuoteRegister To Reply
  4. 08-13-2018, 12:13 PM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Find a reference in column based on topfive value

    Yes, but how is this table laid out? Much easier if you attach a sample workbook, please.
    Reply With QuoteRegister To Reply
  5. 08-13-2018, 12:17 PM #5
    sealpino
    sealpino is offline
    Forum Contributor
    Join Date
    11-10-2012
    Location
    Sweden, Stockholm
    MS-Off Ver
    MS Excel 365
    Posts
    175

    Re: Find a reference in column based on topfive value

    here is the attazchment
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 08-13-2018, 12:26 PM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Find a reference in column based on topfive value

    Still not clear!

    Is this what you want?

    Excel 2016 (Windows) 32 bit
    G
    H
    I
    J
    1
    		 TOP FIVE
    2
    		 start year stopyear
    3
    		 HELLO
    87
    2019
    2022
    4
    		 CERK
    76
    2016
    2019
    5
    		 BRAVO
    45
    2015
    2019
    6
    		 ALFA
    23
    2015
    2020
    7
    		 DELTA
    13
    2018
    2021
    Sheet: Blad1
    Reply With QuoteRegister To Reply
  7. 08-13-2018, 12:31 PM #7
    sealpino
    sealpino is offline
    Forum Contributor
    Join Date
    11-10-2012
    Location
    Sweden, Stockholm
    MS-Off Ver
    MS Excel 365
    Posts
    175

    Re: Find a reference in column based on topfive value

    You are the one top five here,
    You are right on.
    Reply With QuoteRegister To Reply
  8. 08-13-2018, 12:35 PM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Find a reference in column based on topfive value

    OK. So, in G3 copied down:

    =INDEX($A$2:$A$6,MATCH(LARGE($B$2:$B$6,ROWS(G$3:G3)),$B$2:$B$6,0))

    and in H3 copied across and down:

    =INDEX(B$2:B$6,MATCH($G3,$A$2:$A$6,0))

    You may need to change commas to semi-colons for your locale:

    =INDEX($A$2:$A$6;PASSA(STÖRSTA($B$2:$B$6;RADER(G$3:G3));$B$2:$B$6;0))

    and:

    =INDEX(B$2:B$6;PASSA($G3;$A$2:$A$6;0))
    Reply With QuoteRegister To Reply
  9. 08-13-2018, 12:36 PM #9
    sealpino
    sealpino is offline
    Forum Contributor
    Join Date
    11-10-2012
    Location
    Sweden, Stockholm
    MS-Off Ver
    MS Excel 365
    Posts
    175

    Re: Find a reference in column based on topfive value

    I will t ry shortly,
    I will already now say THX.
    Your Formulas Always work.
    Reply With QuoteRegister To Reply
  10. 08-13-2018, 12:38 PM #10
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Find a reference in column based on topfive value

    No, they don't always work for everybody, but I am glad they work for you!!!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  11. 08-13-2018, 04:08 PM #11
    sealpino
    sealpino is offline
    Forum Contributor
    Join Date
    11-10-2012
    Location
    Sweden, Stockholm
    MS-Off Ver
    MS Excel 365
    Posts
    175

    Re: Find a reference in column based on topfive value

    ONCE AGAIN THANK YOU!
    WORKS GREAT
    Could not access to reputation.
    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] Find 2nd Smallest Value based on a Cell Reference
    By ajw089 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2015, 07:00 PM
  2. [SOLVED] find reference cell based off 2 criteria
    By rossg in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-31-2014, 01:34 AM
  3. Find Max and Min based on cell reference(s), display corresponding value
    By mpls in forum Excel General
    Replies: 6
    Last Post: 06-30-2009, 04:02 PM
  4. Find cell reference based upon value
    By Eric Stohr in forum Excel General
    Replies: 5
    Last Post: 01-20-2009, 05:30 AM
  5. Variable Find reference to a column?
    By crowdx42 in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 11-19-2006, 11:13 PM
  6. I need to find the Average from Column A - but Reference Column B
    By BAM718 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-15-2005, 11:06 AM
  7. Find Max and Min based on cell reference
    By gregork in forum Excel General
    Replies: 3
    Last Post: 02-20-2005, 09:06 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