In many cases we may want to find the maximum values that satisfy one or more conditions of aovercomeRecord. There are manyovercomeFunctions to complete the task. In this article, I will show you how to get to the top.10Values by criteria inovercomecom2ideal examples. In addition, I also show the procedure to find the maximum values without specific criteria.
Index to hide
download exercise book
2 ideal examples to obtain the 10 best values according to criteria
1. Find the top 10 values based on a single criteria in Excel
1.1 Insert combination of BIG, IF and ROW functions
1.2 Apply the XLOOKUP, LARGE, and FILTER functions together
1.3 Using the INDEX-MATCH combination
2. Get Top 10 Values Based on Multiple Criteria in Excel
More examples of how to find the top 10 values in Excel
4. Use the INDEX and MATCH functions to find names that contain the top 10 values
How to identify the top 10 values with duplicates in Excel
Diploma
Related Posts
download exercise book
You can download the workbook here.
Top 10 Values by Criteria.xlsx
2 ideal examples to obtain the 10 best values according to criteria
In this section, I'll show you how to get to the top.10Criteria-based values with2ideal examples. One deals with individual criteria, the other with multiple criteria. For the demonstration I used a data set with some students.Name, sign ofPhysically,ChemicalmiHistory.
1. Find the top 10 values based on a single criteria in Excel
We can find the top10Values based on a single criteria using different functions and their combinations. I show below 3 ways to calculate the peak.10Values with unique criteria.
1.1 Insert combination of BIG, IF and ROW functions
Let's follow the step by step procedure given below on how to use it.GROSS,SEmiLINEwork hard to get to the top10Values according to clear criteria.
- choose firstcell G6and write the following formula there.
=BRUTO(SE(D6:D20>60,C6:C20,""),LINHA(A1:A10))
- then pressTap.
- One by one we will see the top10traces ofphysicallyno cell rangeG6:G15.
in the formula
- LINE (A1:A10)This part creates an array of numbers.1for10.
- MI,SE(D6:D20>60, C6:C20''")This part checks the statusD6:D20>60, if the condition is met, is generated as a cell in the range ofC6:C20, if the criteria are not met, an empty value is returned.
oGROSSThe function takes the above parts as arguments and returns the output array consisting of top10PayaPhysically.
For more information, see: How to get cell value by row and column in Excel VBA
1.2 Apply the XLOOKUP, LARGE, and FILTER functions together
we can also useXVERWEIS,GROSSmiFILTER Functions together to determine the top10Values according to clear criteria. Let's follow the steps below.
- choose firstcell G6and write the following formula there.
=BUSCARX(GRANDE(FILTRO(C6:C20,D6:D20>60),LINHA(A1:A10)),C6:C20,B6:B20)
- so hitTap.
- As a result we will seeNamethe students who performed best10PayaPhysically.
The formula contains
- GRANDE(FILTRO(C6:C20,D6:D20>60),LINHA(A1:A10))which provides the lookup value for theXVERWEISCharacteristic. ISLANDFilterThe inner function works like thisSEFunction.
- C6:C20and thelookup_arraymiB6:B20and thereturn_matrix.
1.3 Using the INDEX-MATCH combination
We can do the same task withThe indexmithe gameworks together I show the steps for the following procedures.
- choose firstcell G6and write the following formula there.
=ÍNDICE(B6:B20,COINCIDIR(BRUTO(FILTRO(C6:C20,D6:D20>60),LÍNEA(A1:A10)),C6:C20,0),1)
- then pressTap.
- in the end we will see themNameof students who receivedTop 10 grades in physics.
in the formula
- LINE (A1:A10))This part returns an array of1for10.
- FILTRO(C6:C20,D6:D20>60)This part checks the statusD6:D20>60and leavesC6:C20.
- oGROSSThe function takes the above parts as arguments and gives the output10Pay.
oFITis inINDEXThe function takes all of the above parts as arguments and returns theNameup accordingly10physics grades.
For more information, see: How to find the highest value in Excel column (4 methods)
2. Get Top 10 Values Based on Multiple Criteria in Excel
Instead of a criterion, we can find the top10Values that meet multiple criteria. For the procedures, please follow the steps below to get the best10Values based on multiple criteria in Excel.
- start with selectioncell G6and write the following formula there
=GRANDE(SE((--D6:E20>60)*(--E6:E20>50),C6:C20,""),LINHA(A1:A10))
- so hitTap.
- One by one we will see the top10Physics notes that meet the requirements (more than50brands in history and more than60marks in chemistry).
in the formula
- LINE (A1:A10)This part creates an array of1for10.
- SE((–D6:D20>60)*(–E6:E20>50),C6:C20””)and this part checks the conditions and outputs the cell rangeC6:C20.
Finally, thatGROSSThe function takes the previous parts as arguments and returns the final result as an array.
For more information, see:How to find value in column with VBA in Excel (4 ways)
More examples of how to find the top 10 values in Excel
Now I will show you some examples of how to find the best ones.10Numbers without criteria.
1. Use the BIG and ROW functions to get the top 10 numbers
Follow the given steps to use theGROSSis inLINEFeatures to get you to the top10Pay.
- choose firstcell G6and write the following formula there.
= GROSS ($C$6:$C$20, LINES(A1:A10))
- so hitTap.
- One by one we will see the result one by one.
Supervision:TThe formula contains theGROSSis inLINEFunctions that work as described in the previous methods.
For more information, see: How to Find Top 5 Values and Names in Excel (8 Useful Ways)
2. Apply the LARGE AND COLUMN functions to find the top 2 consecutive values
we can change thatLINEfunction withthe column functionto get the best numbers in a row.
- choose firstcell G6and write the following formula there.
=BRUTO($C6:$E6,COLUNA(A1:B1))
- so hitTap.
- So let's take a look at the top2student numbersName.
Supervision:TThe formula is very similar to the previous method except that it uses theTO DIVIDEfunction (works along the line) instead of functionLINEFunction.
3. Identify the top 10 numbers using the SORT and FILTER functions
Principal10You can find the numbers with theORGANIZEis inFILTERFunction. To do this, let's follow the steps below.
- choose firstcell G6and write the following formula there.
=ORDENAR(FILTRO(C6:C20,C6:C20>=GRANDE(C6:C20,10)),1,-1)
- then pressTap.
- We immediately see a series of results.
in the formula
- FILTRO(C6:C20,C6:C20>=BRUTO(C6:C20,10))This part finds the top numbers.
- So heORGANIZEThe function rearranges the numbers in descending order.
For more information, see:Find value in column and return value from another column in excel
4. Use the INDEX and MATCH functions to find names that contain the top 10 values
We can also find the respective names of the main numbers. we will use thoseINDEXis inFITfunctions for procedures. Let's follow the steps.
- choose firstcell G6and write the following formula there.
=ÍNDICE(B6:B20,VERGLEICH(BRUTO(C6:C20,LÍNEA(A1:A10)),C6:C20,0),1)
- also beatTap.
- One by one we will see the results.
in the formula
- B6:B20is the reference number ofINDEXFunction.
- Payment in advance,KORRESPONDENZ(BRUTO(C6:C20,LINEA(A1:A10)),C6:C20,0)This part specifies the line number and1is the column number of theINDEXFunction.
For more information, see: How to find the last occurrence of a value in a column in Excel (5 methods)
5. Use the XLOOKUP and LARGE functions to get the top 10 numbers
We can use those tooXVERWEISis inGROSSFeatures to get you to the top10Numbers without criteria. Follow the steps below for this method,
- choose firstcell G6and write the following formula there.
=XVERWEIS(BRUTO(C6:C20,LINHA(A1:A10)),C6:C20,B6:B20)
- then hitTapand we will see the result.
Supervision:The formula uses theXVERWEIS, oGROSSis inLINEfunctions We have already used them in the previous methods. These functions work similarly to find the top10Results.
For more information, see: How to find value in column in Excel (4 methods)
How to identify the top 10 values with duplicates in Excel
Sometimes our data set may contain repeated values. If we want to find the top10Numbers without repetitions, follow the steps given.
- choose firstcell G6and write the following formula there.
= LARGE (SINGLE (E6: E20), LINE (A1: A10))
- so hitTap.
- Finally we will see the results.
in the formula
- SINGLE (E6:E20)This part finds the unique values.
- Which in turn serves as an argument for theGROSSFunction.
Diploma
We can find the top10Criteria-based numbers in various ways. In this article I showed2ideal examples that meet the top10Criteria-based numbers inovercome. If you have any questions or suggestions, please let us know by leaving a comment.
Related Posts
- How to Find the Lowest Value in an Excel Column (6 Ways)
- Find the first occurrence of a value in a column in Excel (5 ways)