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
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?
Hi!
Define the top5 list as described in the article above. Then compare each cell to this list using the 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))
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.
Hi!
Download the practice workbook from the link at the end of this article. There you will see that this formula is working.
Never mind my comment above. The cntrl-shift-enter is working now. Yes, I am a dummy sometimes.
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))
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
Hello!
You can find the answer to your question in this article: How to generate random numbers in Excel without duplicates.
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)
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)
Hi!
If I understand correctly, try using this instruction: Get a list of unique values sorted in alphabetical order. Hope this is what you need.
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
Hi!
To sort in the order you want, see the SORT function tutorial.
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
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...
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.
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?
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
Hi!
Excel 2019 cannot extract and sort unique rows using formulas. Try to use Duplicate Remover tool as described in this article: Extract unique and distinct rows with Duplicate Remover.
Hello!
Try to use the recommendations described in this article: Find unique values in specific columns. Then use the SORT function to put in order the result.
=SORT(UNIQUE(CHOOSE({1,2},A2:A5,C2:C5)),2,-1)
This should solve your task.
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!
Hi!
The N/A! error indicates that the value you wanted to find was not found.
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!
Hello!
Unfortunately, the FILTER function can only work with a single range of values.
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?
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.
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
Hello!
To see 5 rows with the 5 largest values from column G where type=1, try this formula:
=INDEX(SORT(FILTER(B2:G200,B2:B200=1),6,-1),{1;2;3;4;5},{1,2,3,4,5,6})
You can find the examples and detailed instructions here: How to limit the number of rows returned by FILTER function.
You can read more about sorting with the SORT function in this article.
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
Hi!
Your formula returns "front". If you change LARGE($F2:$HM2,2) then returns "bought".
What is the problem?
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.
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.)
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?
Hi!
I need to know more details about your task to help you. Give an example of the result you want to get.
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.
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!
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.
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 :)
Hi, I have 3 columns data, I need average of best two columns data.
Please suggest how to calculate it in excel.
Hello!
If I understand your task correctly, I recommend reading this guide: Excel LARGE function with formula examples to get n-th highest value.
Determine the top 2 values and find the average.
I hope this will help, otherwise please do not hesitate to contact me anytime.
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)
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.
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"
Hi!
Please describe in detail the error you are getting. Make sure your percentages are written as numbers, not text.
I am having the same issue. The first name of two on the same value/time is shown for 1st & 2nd.
Hi!
I do not have a detailed description of the problem, so I can not advise anything.
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
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.
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.
Hello!
Please have a look at this article — LARGE IF formula in Excel: find highest values with criteria.
I hope my advice will help you solve your task.
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.
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.
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
Hello!
To get a list of unique names uses this example: How to get distinct values in Excel (unique + 1st duplicate occurrences)
You can get a list of grades for each name using these guidelines: Vlookup multiple matches and return results in a row.
I hope my advice will help you solve your task.