by With this tutorial, you will learn how to find top 3, 5, 10 or n values in a dataset and retrieve matching data.
Want to identify the highest or lowest N values in a column or row? That sounds like a pretty easy thing to do. Need to return not only the values themselves but their names too? Oh, that could be a challenge. There are one or more criteria to be met. Hmm, is that even possible?! Yep, all this can be done with Excel formulas and this tutorial shows how.
Excel formula to find top 3, 5, 10, etc. values
To get the highest N values in a list, use the LARGE and ROWS functions together in this way:
For example, to find top N values in B2:B12, you enter the below formula in the topmost cell where you want the results to appear (D2), and then drag it through as many cells as needed.
=LARGE($B$2:$B$12, ROWS(A$2:A2))
To pull top 3 values, copy the formula to 3 cells.
To get top 5 values, copy the same formula to 5 cells.
To find top 10 values in a column, copy the formula to 10 cells.
How this formula works:
The LARGE function compares all the numeric values in a range supplied for the 1st argument (array) and returns the largest value based on the position specified in the 2nd argument (k).
The ROWS function with an expanding range reference like ROWS(A$2:A2) generates the k values automatically. To create such a reference, we lock the row coordinate in the first cell with an absolute reference (A$2) and use a relative reference for the last cell (A2).
In the topmost cell where you enter the formula (D2 in this example), ROWS(A$2:A2) generates 1 for k, telling LARGE to return the max value. When copied to the below cells, the range reference expands by 1 row causing the k argument to increment by 1. For example, in D3, the reference changes to A$2:A3. The ROWS function counts the number of rows in A$2:A3 and returns 2 for k, so the LARGE function outputs the 2nd largest value.
Please note that we used A$2:A2 just for convenience because our data begins in row 2. You can use any column letter and any row number for the expanding range reference, say A$1:A1 or C$1:C1.
Excel formula to get bottom 3, 5, 10, etc. values in Excel
To find the lowest N values in a list, the generic formula is:
In this case, we use the SMALL function to extract the k-th smallest value and the ROWS function with an expanding range reference to generate the k number.
For example, to find bottom N values in the table below, use this formula:
=SMALL($B$2:$B$12, ROWS(A$2:A2))
Enter it in the topmost cell, and then drag down through as many cells as many values you want to get.
Excel formula to find top N values in a row
If your data is organized horizontally in rows, then you can use the following generic formulas to find the highest or lowest values:
Get top values in a row:
Get bottom values in a row:
The formulas' logic is the same as in the previous example with the difference that you use the COLUMNS function rather than ROWS to "feed" the k value to LARGE and SMALL.
Let's say your table lists the results of 5 rounds for each participant like shown in the image below. You aim to find top 3 values in each row.
To have it done, enter the following formula in the upper-right cell (B10 in our case), and then drag it down and to the right:
=LARGE($B2:$F2, COLUMNS($A1:A1))
For the formula to copy correctly, we use a mixed reference for the array argument of LARGE that locks only the column coordinates ($B2:$F2).
To find bottom 3 values in each row, you can use an analogous SMALL formula:
=SMALL($B$2:$H$2, COLUMNS($A2:A2))
How to get matches of largest N values
In situation when you want to retrieve data relating to top values, use the classic INDEX MATCH formula together with LARGE as the lookup value:
Where:
- Return_array is a range from which to extract associated data (matches).
- Lookup_array is a range where to search for the largest values.
- K is position of the highest value to look for.
In the table below, you can find top 3 values by using the following approach.
To extract top 3 results, the formula in E3 is:
=LARGE($B$2:$B$12, D3)
Because the ranks are typed in separate cells, the ROWS function is not needed in this case - we simply reference the cell containing the k value (D3).
To retrieve the names, the formula in F3 is:
=INDEX($A$2:$A$12, MATCH(LARGE($B$2:$B$12, D3), $B$2:$B$12, 0))
Where A2:A12 are the names (return_array), B2:B12 are the results (lookup_array) and D3 is the position from largest (k).
In a similar manner, you can get matches of bottom N values. For this, just use the SMALL function instead of LARGE.
To get bottom 3 results, the formula in E3 is:
=SMALL($B$2:$B$12, D3)
To pull the names, the formula in F3 is:
=INDEX($A$2:$A$12, MATCH(SMALL($B$2:$B$12, D3), $B$2:$B$12, 0))
How this formula works:
The LARGE function gets the k-th largest value and passes it to the lookup_value argument of MATCH.
For instance, in F3, we are looking for the 1st largest value, which is 5.57. So, after replacing the LARGE function with its output, the formula reduces to:
=INDEX($A$2:$A$12, MATCH(5.57, $B$2:$B$12, 0))
The MATCH function determines the relative position of 5.57 in B2:B12, which is 9. This number goes to the row_num argument of INDEX, simplifying the formula further:
=INDEX($A$2:$A$10, 9)
Finally, the INDEX function returns the value from the 9th row in the range A2:A12, which is "Nick".
XLOOKUP formula
Microsoft 365 subscribers can achieve the same results by using the new XLOOKUP function:
=XLOOKUP(LARGE($B$2:$B$12, D3), $B$2:$B$12, $A$2:$A$12)
In this case, LARGE returns the k-th largest number directly to XLOOKUP as the lookup value.
Compared to the INDEX MATCH formula, this syntax is much simpler. However, please keep in mind that XLOOKUP is only available in Excel 365. In Excel 2019, Excel 2016 and earlier versions, this formula won't work.
How to find top values with duplicates
The approach used in the previous example works fine for a dataset that has only unique numbers in the lookup column. Duplicates may lead to wrong results. For example, if the 1st and 2nd largest numbers happen to be the same, the LARGE function will return the same value for each, which is expected by design. But because MATCH returns the position of the first found match, the formula will output the first match twice like shown on the image below:
To "break ties" and fix the problem, we need a more sophisticated MATCH function:
=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))
In all versions except Excel 365, it only works as an array formula. So, please remember to press Ctrl + Shift + Enter to complete the formula correctly.
How this formula works:
Here, the MATCH function is configured to search for the number 1, which is the lookup value. The lookup array is constructed using the following logic:
Firstly, all the numbers are compared against the value returned by LARGE:
$B$2:$B$12=LARGE($B$2:$B$12, D2)
Secondly, the COUNTIF function with an expanding range reference checks if a given item is already in the top list. Please pay attention that the expanding range begins on the above row (F1) to avoid a circular reference.
COUNTIF(F$1:F1, $A$2:$A$12)=0
The result of the above operations are two arrays of TRUE and FALSE values, which are multiplied by each other. For example, in F3, we have the following arrays:
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE} * {TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
In the first array, there are two TRUE values that correspond to 5.57 (rank 1 and 2) - items 3 and 9. But in the second array, item 3 is FALSE because this name (Brian) is already in the list. The multiplication operation changes the logical values TRUE and FALSE into 1 and 0, respectively. And because multiplying by zero always gives zero, only item 9 "survives":
{0;0;0;0;0;0;0;0;1;0;0}
The MATCH function searches for "1" in this array, and returns its relative position (9), which is Nick.
Note. This solution implies that the return column (Name in our case) contains only unique values.
Tip. In Excel 365, you can use a lot simpler FILTER formula that handles ties automatically.
How to find top values in Excel with criteria
To get top values that match certain condition, express your criteria with the help of the IF function and nest them inside the formulas discussed in the previous sections.
As an example, let's find top 3 results in a given group. To have it done, we input the target group in F1 and type the ranks 1 to 3 in E5:E7 (please see the image below).
To extract top 3 results, enter the below formula in F5 and drag it though F7:
=LARGE(IF($B$2:$B$12=$F$1, $C$2:$C$12), E5)
To ensure that the LARGE function is only processing the results within the target group, we build an IF statement that compares a list of groups against F1.
To get the names, copy this formula in G5 through G7:
=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))
Here, we use the already familial INDEX MATCH LARGE combination but extend it with two logical tests:
LARGE(IF($B$2:$B$12=$F$1, $C$2:$C$12), E5) - IF checks if a group matches the target one in F1. The matches get into the array from which the LARGE function picks the highest value based on the rank in E5. The result becomes the lookup value for MATCH.
IF($B$2:$B$12=$F$1, $C$2:$C$12) - IF filters out irrelevant groups again, so only the results that belong to the target group make it into the lookup array, where MATCH searches for the lookup value.
These are array formulas that should be entered by pressing Ctrl + Shift + Enter simultaneously. Due to the ability of Excel 365 to handle arrays natively, it's sufficient to press the Enter key in this version.
In Excel 365, you can perform the same logical tests inside XLOOKUP and get the identical results:
=XLOOKUP(LARGE(IF($B$2:$B$12=$F$1, $C$2:$C$12), E5), IF($B$2:$B$12=$F$1, $C$2:$C$12), $A$2:$A$12)
Tips:
- To find bottom values with criteria, simply replace LARGE with SMALL in the above formulas.
- If your dataset contains duplicates, and you want to extract top values and all the names associated with them, here's a working solution for Excel 365: Filter top n values with condition.
- To get top records with multiple criteria, use the FILTER function together with LARGE IF as shown in Filter top n values with multiple criteria.
- In earlier Excel versions, you can extract top/bottom values using advanced filter.
How to FILTER top or bottom values in Excel
In Excel 365, there is a simpler way to find largest N values by using new dynamic array functions such as SORT and FILTER.
Where:
- Data is the source table excluding column headers.
- Numbers are the numeric values to rank.
- N is the number of top entries to extract.
- Sort_index the number of the column to sort by.
For example, to filter top 3 records in our set of data, the formula goes as follows:
=SORT(FILTER(A2:B12, B2:B12>=LARGE(B2:B12, 3)), 2, -1)
In this case, we set n to 3 because we're extracting top 3 results and sort_index to 2 since the numbers are in the second column.
The beauty of this formula is that you only need to enter it in one cell, and Excel automatically spills the results into as many cells as needed (this concept is called a spill range).
To extract the lowest 3 results, the formula is:
=SORT(FILTER(A2:B12, B2:B12<=SMALL(B2:B12, 3)), 2, 1)
How these formulas work:
Here, we use the FILTER function to filter the source data based on the criteria included in the 2nd argument.
To get top values, we construct the logical expression that checks if a given number is greater than or equal to the Nth highest number in the list (the 3rd largest number in our case): B2:B12>=LARGE(B2:B12, 3).
To get bottom values, we check if a number is less than or equal to the 3rd lowest number: B2:B12<=SMALL(B2:B12, 3).
The result of the logical test is an array of TRUE and FALSE values, which is used for filtering - only the entries corresponding to TRUE get into the final array:
=SORT({"Aiden",5.51;"Brian",5.57;"Nick",5.57}, 2, -1)
As you can see above, the FILTER function outputs the items in the same order they appear in the source range. To sort the results, we place the FILTER formula inside the SORT function and set the sort_order argument to either -1 (descending) or 1 (ascending).
That's how to find top values in Excel. I thank you for reading and look forward to seeing you on our blog next week!
Practice workbook for download
Excel formulas to find top or bottom values (.xlsx file)
165 comments
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.
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.
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?
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.
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!
Hello!
I have not been able to reproduce your error. I recommend reading this article: Why Excel XLOOKUP not working.
Also, check if you would have changed the range in the formula correctly. This must be done 5 times.
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.
Hello!
I recommend reading this guide: How to highlight top or bottom 3, 5, 10 values in Excel.
I hope it’ll be helpful.
Thanks for the help, I'll definitely try it and let you know.
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!!
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.
Hello!
You can find the examples and detailed instructions in this article. If something is still unclear, please feel free to ask.
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
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.
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??
Hi!
I am not sure I fully understand what you mean.
The IF function does not work with arrays and data ranges.
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?
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})
u explained this so poorly i cried like 4 times trying to understand. disappointing
Sorry columns went awry after being sent. Looks a mess, I hope it's possible to understand it.
Ros
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
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.
Thank you, will give it a go.
Ros
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
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
Hi,
It is a pity that you did not immediately explain the whole problem.
Use the following formula
=SMALL(INDEX(B2:E11,,MATCH(G2,B1:E1,0)),1)
=SMALL(INDEX(B2:E11,,MATCH(G2,B1:E1,0)),2)
G2 - month
Please have a look at this article: SMALL IF in Excel: get Nth smallest value with criteria
Hope this is what you need.
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
Hello!
Please check out the following article on our blog, it’ll be sure to help you with your task: INDEX MATCH to lookup in rows and columns
I hope I answered your question. If something is still unclear, please feel free to ask.
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.
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 :)
Hi,
You can learn more how to get matches of largest N values with duplicates in Excel in this article on our blog.
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
To ensure clear understanding of your task, can you provide an example of the source data and the desired result you are aiming for?
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
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.
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!
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.
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.
Hello!
Please use the following formula
=SUM(LARGE(A1:A50,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18}))
You can learn more about LARGE function in this article: Excel LARGE function to get n-th highest value.
I hope I answered your question. If something is still unclear, please feel free to ask.
Hi, that returned the top score only, not the top 18?
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})
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.
Don't worry. Found the answer.
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
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!
Thank you for your positive feedback, Yulia! It's the best incentive for us to keep up and improve :)