Comments on: How to find top or bottom N values in Excel

Want to identify the highest or lowest values in a column or row? Need to return not only the values themselves but their names too? With this tutorial, you will learn how to find top 3, 5, 10 or n values in a dataset and retrieve matching data. Continue reading →

Comments page 2. Total comments: 165

  1. Hi,
    I am fighting for a couple of hours already on how to get top values with 2 criteria. I tried with IF(AND() but getting #N/A. I tried with your practice workbook, I added additional data into column D, to match criteria in cell G1. Can you please look at my formula and help me
    =INDEX($A$2:$A$12, MATCH(LARGE(IF(AND($B$2:$B$12=$F$1,$D$2:$D$12=$G$1), $C$2:$C$12), E5), IF(AND($B$2:$B$12=$F$1,$D$2:$D$12=$G$1), $C$2:$C$12), 0))

    1. Hello!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =INDEX($A$2:$A$12, MATCH(LARGE(IF(($B$2:$B$12=$F$1)*($D$2:$D$12=$G$1), $C$2:$C$12), E5), IF(($B$2:$B$12=$F$1)*($D$2:$D$12=$G$1), $C$2:$C$12), 0))

      1. Dear Alexander, you got it. This is exactly what I need 💪 Thanks a lot!

        1. Hello, I hope you could help me as well.

          For a few good hours I try to get a formula which will give me the 3 most repeatable words from the row A2:A12. Lets say I have words: 1 4 3 2 3 4 3 4 4 4 4 4 .
          I get the result in a A13 for 4, as most recent with formula: =INDEX(A2:12;MODE(IF(A2:A12"";MATCH(A2:A12;A2:A12;0))))
          But it gives me #N/A when the number appears only once :( like in my case number 1 or 2. What do I do wrong? Could you please have a look and correct me or give me the formulas for this?

          Thank you in advance!
          Monika

          1. Hello!
            MODE function does not detect a number that occurs only once and returns an error. To determine the three numbers that occur most often, write the formula in cell D2 and copy it 2 lines down:

            =IFERROR(MODE(IF(ISERROR(MATCH($A$2:$A$12,D$1:D1,0)),$A$2:$A$12)),"")

            I hope this will help.

  2. Hello!
    I have a data like the following
    Name test 1 test 2 test 3 test4 test 5 test 6
    Peter 12 14 16 11 11 17
    Arun 21 19 15 8 14 17
    Claire 20 7 31 23 17 14
    John 15 12 18 14 16 12

    In this I would like to find the sum of large 4 test values amongst 6 tests for each row. Could You help me how to find this using formula in excel?

  3. Hi,

    Regarding formula for 'How to find top values in Excel with criteria' that uses formula =SORT(FILTER(A2:B12, B2:B12>=LARGE(B2:B12, 3)), 2, -1).

    What needs to be added to =SORT(FILTER(A2:B12, B2:B12>=LARGE(B2:B12, 3)), 2, -1) under 'How to FILTER top or bottom values in Excel' to only return the top 3 values when more than one person has the same result? For instance, change Jacob's Result from 5.49 to 5.51. Just using =SORT(FILTER(A2:B12, B2:B12>=LARGE(B2:B12, 3)), 2, -1) will return top 4 when only want to return "top 3":

    Brian 5.57
    Nick 5.57
    Aiden 5.51
    Jacob 5.51

    Top 3 would be:
    Brian 5.57
    Nick 5.57
    Aiden 5.51
    OR
    Brian 5.57
    Nick 5.57
    Jacob 5.51

    Thank you!

  4. Here is my data and I would like to get only the number every 5 such as 5, 10,15,20,25,30,35 ......
    5.000
    5.500
    6.000
    6.500
    7.000
    7.500
    8.000
    8.500
    9.000
    9.500
    10.000
    10.500

      1. This works only when my data is not moving. But my data is moving every minute. So how do I know what the high and low points were for the whole day today?

  5. Hi,
    I have a data column, it's live data. I want to today's highest and lowest value. This problem solved with max & min formula but my data is live. So its changed every time. How can i solve this issue?

    Exp.

    Data
    30.00 High 31.00 Today High ?
    29.05 Low 29.05 Today Low ?
    31.00
    30.80
    29.30
    30.14
    30.17 This data is live
    30.21
    30.24
    30.28
    30.31
    30.35
    30.38
    30.42

  6. Hi,

    I have 2 columns, one has names including duplicates, the other values including duplicates. Is it possible to total the values for each name and filter the results by VALUE high to low to give a top 10?

    Example:

    NAME VALUE
    Tom Smith 2
    Bill Jones 3
    Colin Brown 1
    Bill Jones 1
    Colin Brown 2
    David Small 2
    Tom Smith 2
    etc.

    Output:

    Name Value
    Bill Jones 5
    Tom Smith 4
    Colin Brown 3
    David Small 2

    Thanks in advance.

  7. Solutions described above list the top values on a separate table. Is there a function that could be used in a column in the original table which would return TRUE if value in a row belongs to top5 and FALSE if it doesn't?

  8. =INDEX($A$2:$A$12, MATCH(1, ($B$2:$B$12=LARGE($B$2:$B$12, D2)) * (COUNTIF(F$1:F1, $A$2:$A$12)=0), 0))

    This formula does not work as it returns #N/A in my spreadsheet. I've typed it exactly as shown and used the same data as shown above.

    Please correct this. Thank you.

    1. So now the cntl-shift-enter is working. okay, I stand corrected. There is no problems with this formula.

      =INDEX($A$2:$A$12, MATCH(1, ($B$2:$B$12=LARGE($B$2:$B$12, D2)) * (COUNTIF(F$1:F1, $A$2:$A$12)=0), 0))

    2. Never mind my comment above. The cntrl-shift-enter is working now. Yes, I am a dummy sometimes.

    3. Hi!
      Download the practice workbook from the link at the end of this article. There you will see that this formula is working.

  9. hello
    i want to get values in descending order but only unique values
    for example
    data list is- 5,2,1,2,7,3,2,7,4,7
    output 7,5,4,3,2,1
    values from top to bottom but only
    thanks

      1. the article which you suggested is for generating random numbers,,,,, and I'm looking to get numbers in descending order
        data list is- 5,2,1,2,7,3,2,7,4,7
        LARGE(a1:a10,1) will give largest value in the given range i.e. 7
        LARGE(a1:a10,2) will also return 7 ( here I want to get 5 because 5 is 2nd largest value in data range)
        LARGE(a1:a10,2) will also return 7 (here I want to get 4 because 4 is 3rd largest value in data range)

          1. NO...
            I just want to sort numbers in descending order while no number should repeat.

            for example,,,numbers are- 5,1,3,1,7,5,1,3,8,1
            (output in descending order) 8,7,5,3,1
            output has unique numbers only

        2. LARGE(a1:a10,1) will give largest value in the given range i.e. 7
          LARGE(a1:a10,2) will also return 7 ( here I want to get 5 because 5 is 2nd largest value in data range)
          LARGE(a1:a10,3) will also return 7 (here I want to get 4 because 4 is 3rd largest value in data range)

  10. HELLO, HOW TO SOLVE THESE PROBLEMS IN EXCEL?

    1. Five girls are in bakery business, and they want to check how they have done sales in every month.
    2. They have different products like Cake, Pies, Sandwich, Bread, Burger, and Donuts.
    3. Please create pie chart to understand which product is sold high and low by all the ladies
    4. If the sales of cakes are more than 100, that lady is announcing 10% discount for the next month
    5. Ladies with only 5 letters in their name are allowed to use data validation for their cake products

  11. Hello... I have a data speed distance and time... I want to filter data.. I want to search speed at 50 metre behind when speed is zero...

    1. Hi!
      Your explanation does not make it clear what you want to find. Try using the FILTER function to get data by condition. If this is not what you wanted, please explain in more detail.

  12. Hi i want rank large number in the list
    The list have duplicait value and same name
    Like

    Name gender price
    Emad male 1900
    Hoda female 2000
    Sevda female 2500
    Hoda female 2000

    After the rank by price this we have

    Name gender price
    Sevda female 2500
    Hoda female 2000
    Hoda female 2000
    Emad male 1900

    I want ignor second or several name :hoda with 2000"

    What should I do? Tell me the easiest way. Thank you If I send the link of the my excell file, can you check it and write the answer?

    2. My office ver 2019 and i can update yet
      This issue should be solved with Office 2019 formulas tanks again
      I read other article for uniqe value but plz send example formula for me
      And do not refer to another page I can simulate the formula for my table

  13. Hi,
    I need to add multiple logical test in the formula.
    When I try to put IF(AND(..), it returns me N/A value.
    How can I solve?

    Thanks in advance!

  14. Hello, Thank you for the formula!

    I have question: how to in sort choose separate cells? I want to make sort index one cell, and show top 3 with with 2 different cells.

    i.e.
    I have table:
    ID Coffee Country Name1 Name2
    1 Brasil 7.0 5.5
    2 Indonesia 7.0 6.5
    3 Egypt 3.0 4.5
    4 Colombia 4.0 9.0

    For Name1 i can run:
    =SORT(FILTER(B2:C5, C$2:C$5>=LARGE(C$2:C$5, 3)), 2, -1)

    and get:
    Brasil 7
    Indonesia 7
    Colombia 4

    But how do I have to do for next person? And how to auto make for others n person.

    i.e. for Name2 I have to get:
    Colombia 9
    Indonesia 6.5
    Brasil 5.5

    Thank you!

  15. if i have 100 values like 5.00,5.01,5.02,.........,5.99. And now I want to find out that, from 5.00 to 5.99, I take any one value, such as 5.20, now Excel will give me two types of values, namely 5.20 to 2% up value and 5.20 to 2% down value. So whatever value I have out of a total of 100 cells, all the values ​​that come in 5.20 to 2% up and down criteria will be filtered. So can this happen?

    1. Hello!
      You can use the FILTER function to get an array of data according to conditions. Here is an example for your task:

      =FILTER(A1:A100,(A1:A100>C1*0.98)*(A1:A100<C1*1.02))

      С1 = 5.2
      I hope my advice will help you solve your task.

  16. Hi,

    I'm trying to sort/filter a dataset to display rows with the 5 largest values from column G ("size") for a unique set of value in column B ("type"). Need to see 5 rows where type=1, 5 rows where type =2, 5 rows where type =3 etc. Values for the Type range from 1 to 10 and in Size from 15 to 200, data set is 1000 rows.

    Thanks

  17. Hi All,

    I have an issue in the way that my data is structured. I am trying to get the top keyword from a weighted list. However the issue of duplicate weightings exists.

    The current formula I am using is as follows:
    =IFERROR(INDEX($F2:$HM2,MATCH(LARGE($F2:$HM2,1),$F2:$HM2,0) -1),"")

    The data carries across columns as follows:
    F2 G2 H2 I2 j2 k2 l2 m2
    bought 0.157044 day 0.157044 days 0.128621 front 0.269925

    The formula above works fine when extracting a top weighted word but when I wish to get the second weighted word 'bought' will again be returned.

    Any ideas how I can handle this? I am unsure how I can get the countif to work based on the way the data is structured...

    Thanks,

    Brent

    1. Hi!
      Your formula returns "front". If you change LARGE($F2:$HM2,2) then returns "bought".
      What is the problem?

      1. Sorry. I didn't realise I had front at the end their weighted higher.

        If you were to pretend bought and day were the highest weighted. (OR say front wasn't in the list). The results for LARGE($F2:$HM2,1) and LARGE($F2:$HM2,2) will both be bought.

        Ideally, I'd want the next value in the list where there are two values where the first value displayed is ranked higher.

        1. Hello!
          This problem cannot be solved with a single formula. 1. Get a list of unique values using the UNIQUE function. 2. For each of these values, determine the maximum weight using the MAXIFS function. 3. From this data, select the Nth maximum value (1st, 2nd, etc.)

  18. Here is the issue I have and I need help. I have four columns of data. The first is a location, the second is a brand code, the third is the brand name and the fourth is the total units sold. I want to filter the data somehow so all I get is the top five codes, names and units sold per location. Some have more than five codes, some have less. Here is a sample data set:

    Location Brand Code Brand Name Units Sold
    Pitt 085 Pirelli 27
    Pitt 027 Goodyear 25
    Pitt 036 BFG 19
    Pitt 082 Cooper 18
    Pitt 039 General 18
    Pitt 010 Hughes 17
    Pitt 017 Hankook 16
    Green 085 Pirelli 18
    Green 036 BFG 11
    Erie 085 Pirelli 31
    Erie 036 BFG 28
    Erie 039 General 23
    Erie 082 Cooper 21
    Erie 017 Hankook 20
    Erie 010 Hughes 17

    ... and so on. Can you help? Please?

    1. Hi!
      I need to know more details about your task to help you. Give an example of the result you want to get.

      1. The result set would look like this:

        LOCATION CODE NAME UNITS
        PITT 085 Pirelli 27
        PITT 027 Goodyear 25
        PITT 036 BFG 19
        PITT 082 Cooper 18
        PITT 039 General 18
        GREEN 085 Pirelli 18
        GREEN 036 BFG 11
        ERIE 085 Pirelli 31
        ERIE 036 BFG 28
        ERIE 039 General 23
        ERIE 082 Cooper 21
        ERIE 017 Hankook 20

        PITT has more than five codes, products, sales, etc. but I only need the top 5.
        GREEN has less than five so all can be included.
        ERIE has more than five but I only need the top 5.

        ... and so on for the remaining locations.

        1. Hello!
          You can get the top 5 results for one location using the formula:

          =FILTER(A2:D20,(A2:A20="PITT")*($D$2:$D$20>=LARGE((A2:A20="PITT")*$D$2:$D$20,5)))

          You can learn more about FILTER function in Excel in this article on our blog.
          I hope my advice will help you solve your task.

        2. sorry... just realized my result set example for ERIE has six (I copied and pasted and didn't delete the last line). It should only have five.

          Thanks!

  19. Thanks for this tutorial, very helpful...i found that you can simplify the top or bottom n with a simpler formula:
    =LARGE(values, SEQUENCE(n))
    =SMALL(values, SEQUENCE(n))

    This will spill the result down to the cells below, very simple way to have a dynamic formula, specially if you have your n definition in another cell, so you can change as needed

    hope this helps :)

  20. Hi, I have 3 columns data, I need average of best two columns data.
    Please suggest how to calculate it in excel.

  21. Hello, I am trying to find the top three values from a list of 20 and return the associated name. I am also trying to find the bottom three values and the associated name of which it has duplicate values but I need to return two different names. Downloading your sheet I am unable to match the names. The Small and Large functions are working correctly to provide the values correctly. The name is returning incorrect values when I am switching my formula to Index with the imbedded Countif function for both top and bottom.
    {=INDEX($P$5:$P$24;MATCH(1;($Q$5:$Q$24=LARGE($Q$5:$Q$24;R5))*(COUNTIF(T$5:T5;$P$5:$P$24)=0);0))}

    Goodride -3,30%
    Rotalla -0,80%
    Goodyear -2,00%
    Continental -1,50%
    Hankook -1,60%
    Nankang -2,10%
    Michelin -1,10%
    Pirelli -0,50%
    Nokian -1,40%
    Bridgestone -3,00%
    Nexen -1,50%
    Falken -2,70%
    Star Performer -3,30%
    Dunlop -1,30%
    Kleber -1,60%
    Maxxis -3,20%
    Toyo -2,90%
    Barum -0,70%
    Uniroyal 1,30%
    Vredestein -0,10%

    I receive the top three values as 1,3%, -0,1%, -0,5% with the return names as Uniroyal, Vredestein Pirelli
    the bottom three values are -3,3%, -3,3%, -3,2%. However without the countif function added I receive Goodride as the name for values 1 and 2, when I add the countif function in I am receiving not a name value but a 0.

    How can I set this as a standard formula to follow in multiple data reviews to avoid a hard sort each time. (FYI, the sort formula is not a function that I am able to utilize , nor can I use the filter in a formula)

    1. Hello!
      Use the recommendations given in the section: How to find top values with duplicates.

      Cell S5 --

      {=INDEX($P$5:$P$24, MATCH(1, ($Q$5:$Q$24=LARGE($Q$5:$Q$24, R5)) * (COUNTIF($S$4:S4, $P$5:$P$24)=0), 0))}

      Cell T5 --

      =INDEX($P$5:$P$24, MATCH(1, ($Q$5:$Q$24=SMALL($Q$5:$Q$24, R5)) * (COUNTIF($T$4:T4, $P$5:$P$24)=0), 0))

      I hope I answered your question. If you have any other questions, please don’t hesitate to ask.

      1. Hello Alexander,
        Unfortunately, I am still receiving an error. Could it be because the data I am using has been created in an older version of excel and I am working in the data in "compatibility mode"

        1. Hi!
          Please describe in detail the error you are getting. Make sure your percentages are written as numbers, not text.

          1. I am having the same issue. The first name of two on the same value/time is shown for 1st & 2nd.

  22. Hi

    I need code wise top 3 amount and need to remove the extra values i.e. not top 3

    Sample file:

    Code Amount
    1 100000
    1 74777
    1 52000
    1 20000
    1 15000
    1 13000
    2 50000
    2 25000
    3 80000
    3 60000
    3 44000
    3 30200
    3 10000
    4 88000
    4 72000
    5 96000
    5 40000
    5 20000
    6 150000
    6 30000
    6 22000
    6 11000

    1. Hi!
      You can use this formula:

      =SUM(INDEX($A$2:$A$23,MATCH(LARGE($B$2:$B$23,{1;2;3}),$B$2:$B$23,0)))

      I think you know that Excel formulas do not delete values in cells.

  23. Hi, Iam facing difficulty when using the formula:-

    =INDEX($A$2:$A$12, MATCH(LARGE(IF($B$2:$B$12=$F$1, $C$2:$C$12), E5), IF($B$2:$B$12=$F$1, $C$2:$C$12), 0))

    When iam trying to get the name from my data to get top 3, one of 2 names having same value and it returns to me the same value .How to resolve it.

  24. Dear Ablebits,

    Many appreciations for your support on this. would u pls help me to find top 10 with column criterias which have the most sum from the below table consisting multiple columns combined with both common and unique data of numerous values either text or number for instance-

    names item no list of items source value
    rick 221144 apple italy 33475
    mina 231673 soap greece 32197
    ranta 371582 steel uae 4583199
    rick 221144 apple china 13626 rick 221144 apple italy 45178
    mina 231673 soap greece 89135

    now, i want find which will showcase as below format-

    Top 10 names list of items value
    1 rick* apple* 92,279*
    2
    3
    4
    5
    6
    7
    8
    9
    10
    what will be the way to find * cells mentioned above?
    Thanks for your persisting support and cooperation

    Gratitude
    Ronoy.

  25. Dear Ablebits,

    Many appreciations for your support on this. would u pls help me to find top 10 with column criterias which have the most sum from the below table consisting multiple columns combined with both common and unique data of numerous values either text or number for instance-

    names item no list of items source value
    rick 221144 apple italy 33475
    mina 231673 soap greece 32197
    ranta 371582 steel uae 4583199
    rick 221144 apple china 13626 rick 221144 apple italy 45178
    mina 231673 soap greece 89135

    now, i want find which will showcase as below format-

    Top 10 names list of items value
    1 rick* apple* 92,279*
    2
    3
    4
    5
    6
    7
    8
    9
    10
    what will be the way to find * cells mentioned above?
    Thanks for your persisting support and cooperation

    Gratitude
    Ronoy.

  26. Hi Svetlana, Thank you for you helpful post! I was hoping you can help, I have two columns setup like this:

    Name Grade
    Abby 8
    Abby 8
    Abby 7
    Bob 6
    Bob 9
    Frank 8

    I need this data to be converted into columns like this:

    Abby 8 8 7
    Bob 6 9
    Frank 8

    I'm using this expression =INDEX($A$2:$A$112, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$112), 0)) to express the unique names which works.

    I'm using this: =IFERROR(INDEX($B$2:$B$116, MATCH(0, COUNTIF($D2:D2,$B$2:$B$116)+IF($A$2:$A$116$D2, 1, 0), 0)), 0) to transpose the information but it only work for unique number. Do you know a way I can turn the unique part off because then I think it will work.

    Grateful for any help.

    Thanks
    Rich

  27. Hello

    I'm using your Top Values Duplicate. I have a sheet with the following columns:

    A - Category (general text with repeats)
    B - Acceleration (3 digit number with repeats)

    I'm trying to extract the top 5 highest numbers and output them in other cells. I would like the returned values from column B with the matching value from column A output in the cells. When I adapt your formula I can get the correct value output from column B, however the text output from column A will return an #N/A value. I've even copied my data in to your example spreadsheet and the same occurs.

    =INDEX(A15:A23, MATCH(TRUE, (B15:B23=LARGE(B15:B23, J15)) * (COUNTIF(L$1:L14, A15:A23)=0), 0))

    J15 refers to the cell with '1' text (top 1st). L14 refers to header for column of extracted A column data.

    What am I doing wrong?! Thanks.

    1. Hello!
      I can't see your details, so I can't verify for sure. This is not exactly the formula in the article. Replace TRUE with 1.

  28. Hi...I tried your formula and I am encountering a problem. It seems like it only worked if I limited the scope to about 110 cells but anything more, "#VALUE" comes out as the result. I am sure there is nothing wrong with your formula but would you know how I can resolve this? Is there a formatting issue I need to check?

    1. Hi!
      I can't guess which formula you used. There are a lot of formulas in this article. I can't see your data. Describe the problem in more detail and I'll try to help.

      1. Hi,

        Sorry for being vague in my question. I tried to find the top values given a criteria. I have a set of data in Column A with Account Names, Column B with Booking Month, Column C with Status and Column D with Amounts. I need to get the Top 5 Accounts with its corresponding Status and Amount depending on the month indicated on the sheet. The current month is specified on cell F1. I used the formula as follows:

        =XLOOKUP(LARGE(IF($B$5:$B$116=$F$1, $D$5:$D$116), E5), IF($B$5:$B$116=$F$1, $D$5:$D$116), $A$5:$A$116)

        The above formula works. However, I already have entries up to row 280. If I change the formula to include row 280 instead of only up to 116, the result shows: "#VALUE!". Even if I change it to include row 117, the same results come out.

        Thanks in advance!

  29. I have a list of about 100 or so numbers, which is also expected to grow larger. I know how to find the ten highest values, but I really need to know which rows contain the values since some are duplicates. Is that possible? It would save a lot of time and possible mistakes when try to manually look through the entire list. Hope that I explained that clear enough to understand. If I need to clarify anything please let me know. Thanks for any help you can provide.

      1. Thanks for the help, I'll definitely try it and let you know.

  30. This is one of the best help articles I've ever read for defining a somewhat complex set of functions. I'm proficient, but very un-expert, and this was the key to creating the sheet I was trying to create! Thank you so much for your quality work!!

  31. Hi,

    I have an excel file with several columns comprising list as below:

    Column D - Name of Equity Instrument (text)
    Column Q - Amount of P&L (number)
    Column Z - Name of Month (text)
    Column AA - Profit or Loss (text)

    I want to be able to extract / get top 5 equity (column D) with highest profit (column AA) and also top 5 equity (column D) with max loss (column AA), based on Profit or Loss specified in column AA. Kindly help me with the formula. Also please advise what addition do I make in the formula, just in case if I wish to extract these details month-wise (column Z).

    Thank you so much for your help.

    1. Hello!
      You can find the examples and detailed instructions in this article. If something is still unclear, please feel free to ask.

  32. Hi,

    I'm getting #N/A values in my list as I drag the formula down and not sure what I am doing incorrectly. Formula is as follows:

    {=INDEX($J$3:$J$2554, MATCH(1, ($R$3:$R$2554=LARGE($R$3:$R$2554, $A9)) * (COUNTIF(AG$8:AG8, $J$3:$J$2554)=0), 0))}

    Appreciate any help you can provide!

    Thanks,

    Evan

    1. Hello!
      An N/A error means the formula

      MATCH(1, ($R$3:$R$2554=LARGE($R$3:$R$2554, $A9)

      did not find the desired value.
      Check if cell reference $A9 should change.
      I also recommend replacing 1 with TRUE.

      MATCH(TRUE, ($R$3:$R$2554=LARGE($R$3:$R$2554, $A9)

      I hope it’ll be helpful.

  33. HI Alexander, Thanks for wonderful explanation. I am very much able to corelate your example with my data. However I am facing one issue, I want to return SMALL function only if the value in specific cell is not equal to -100%... (This is the column from where I need to publish 5 worst performing segments). I tried to put a condition SMALL(IF(POWER($B$2:$B$27,2)1,$C$2:$C$27),ROW(F4)-ROW(F$3)), even if it is array formula, the result is #value.....it seems excel is not able to decipher POWER formula in SMALL...
    This is basically to exclude the WORST values with -100% values.......
    Can you please help??

    1. Hi!
      I am not sure I fully understand what you mean.
      The IF function does not work with arrays and data ranges.

  34. How would you go about finding the top three most referenced input in a column if it is a name? For example, if in column C you have the following list:

    James
    Tina
    Rachel
    James
    Tim
    Jorge
    Manuel
    Emily
    Rachel
    Josephine
    James

    What formula would I utilize to find the top three names in that list?

    1. Hi Nathan,

      You can extract the top three names by using the below formula, where C1:C11 is the list of names:

      =INDEX(SORTBY(UNIQUE(C1:C11), COUNTIF(C1:C11, UNIQUE(C1:C11)), -1), {1;2;3})

  35. u explained this so poorly i cried like 4 times trying to understand. disappointing

  36. Sorry columns went awry after being sent. Looks a mess, I hope it's possible to understand it.

    Ros

  37. Hi, Starting with a grid setup as below.

    A B C D E
    name | round 1 | round 2 | round 3 | round 4
    position position position position
    _______________________________________________________
    bill 2nd 4th 2nd 1st
    fred 3 2 1 4
    rod 4 3 3 3
    dude 1 1 4 2

    Here goes! Match fishing, 12 rounds/matches. 35+ people taking part (column A)
    Column B will show the position each person attained in round 1, 1st through to 35th position. (and so on for each round)

    Firstly, can a formula take away each persons lowest 2 positions?

    1st places | 2nd places | 3rd places | 4th places

    Bill 1 1 0 0
    fred 1 1 0 0
    rod 0 0 2 0
    dude 2 0 0 0

    and ultimately rank them in position order

    dude 1st
    bill =2nd
    fred =2nd
    rod 4th

    I have tried but I'm still a novice in excel. Hope this makes sense.
    Thanks
    Ros

    1. Hello!
      An Excel formula can only change the value of the cell in which it is written.
      You can rate each place with a certain number of points. Then, for each person, calculate the total score. Then use this sum to sort with the SORT function.
      I hope it’ll be helpful.

      1. Thank you, will give it a go.

        Ros

  38. Hi,
    Column A names | Columns B to M - rounds 1 to 12

    The columns will show what position each participant attained in that round.

    Is it possible to rank by each participants top 10 rounds positions and have the lowest 2 disregarded?

    Many thanks in advance

  39. To clarify I am looking for bottom 2 customers per month. Ie Jan is 3 and 10 feb is 8 and 16 Mar is 15 and 20

  40. Hi,

    I am running into an issue where my Index/Match to find the top 10 items in my file only pulls data for one single month.

    Example:
    Lets say I want to pull the top 2 shortages customers per month

    My current formula is below, only points at March since column L is for March. How can I get this formula to change which column it is pulling the top two sales qty from without re entering the entire formula?
    I am thinking something similar to using a vlookup where you can change the column to lookup by using a number ie: =vlookup(a3,D:E,2,false)

    =INDEX('Shorts by Item'!A:A,MATCH(1,('Shorts by Item'!L:L=LARGE('Shorts by Item'!L:L,Dashboard!A38))*(COUNTIF(Dashboard!B$36:Dashboard!B37,'Shorts by Item'!A:A)=0),0))

    Below is an example with 3 different customers a/b/c and how many units we short shipped the customer each month. I am looking to change the column it pulls from by typing Jan/Feb/Mar/April into a cell.

    Customer | Jan | Feb | Mar | April
    a: | 10 | 20 | 15 | 10
    b: | 18 | 16 | 22 | 12
    c: | 3 | 8 | 20 | 6

    Thanks,
    Danny

  41. How do I find top values with criteria with duplicates?

    e.g. If the long jump had two 5.57m in Group A and I want both unique names?

    Many thanks.

      1. I am trying to combine the two formulas of duplicates and criteria & it doesn’t work. I have check both of these articles and it isn’t working. Can you share with me a formula that comes the two because I can’t find it anywhere

        1. To ensure clear understanding of your task, can you provide an example of the source data and the desired result you are aiming for?

    2. Hi Thabo,

      For this, you can use a combination of FILTER LARGE and IF functions as demonstrated in this example: Filter top n values with criteria

      This solution only works in Excel 365, hope it is the version you use :)

  42. I have just found your site and am really enjoying the content.
    In regards to the "Top Values Duplicates" example, is it possble to set the crieria so that the output is a list of all people who scored 5.48 and above which would mean that of the 11 entries on the name list, 6
    would show up in the results including the top 2 and bottom 2 duplicates.
    Would appreaciate your help with this.

    Rob

    1. Hello!
      You can use these two formulas:
      For values —

      =LARGE(IF($B$2:$B$12>5,$B$2:$B$12), D2)

      For names —

      =INDEX($A$2:$A$12, MATCH(1, ($B$2:$B$12=LARGE($B$2:$B$12, D2))*($B$2:$B$12>5) * (COUNTIF(F$1:F1, $A$2:$A$12)=0), 0))

      I hope I answered your question. If something is still unclear, please feel free to ask.

  43. Hi, I have a list of names and next column I lave a list of the number of orders they have made for that month. How can I make another column next to it which can show their rank in who's made the most orders. Like next to the person with the most orders to have 1, second best to have 2 in the Rank column. And if second best makes more orders, the formula to automatically change into the correct ranking. Any ideas? Thank you very much in advance!

    1. Hello!
      If your data is written in columns A and B, then in column C write the formula

      =COUNTIF($A$2:$A$1000,">="&A2)

      Copy it down along the column.
      Hope this is what you need.

  44. Hi,
    I have a column of 50 numbers. I want to find the highest 18 numbers, add them together and return a total. Is there a formula that can do all this in one cell please?
    Cheers.

      1. Hi, that returned the top score only, not the top 18?

        1. My bad. That works perfectly in Excel. I've been trying to use it in Google Sheets. By any chance do you have a solution for that?
          Kind regards, Ian.

          1. Don't worry. Found the answer.

        2. Hello!
          I wrote this formula based on the description you provided in your original comment. The formula calculates the sum of the 18 largest numbers. If you want to get these numbers, then you need to do it in another cell.

          =LARGE(A1:A50,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})

  45. I have a list as follows.

    CLASS-NAME-GRADE
    A-TOD-50
    A-BEN-80
    B -JOHN- 70
    B-PAT - 90

    How do I find the first student name who's score is greater than 45 but in class B.

    Thanks

  46. Hello Svetlana,

    Thank you very much for the posts! I have just started exploring the website looking for some personal tips for work with excel, and I must say that the format of the posts is very nice! Especially, thank you for taking time and posting screenshots with the clear explanations!

    1. Thank you for your positive feedback, Yulia! It's the best incentive for us to keep up and improve :)

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)