Microsoft Excel has provided numerous ways to determine the top 5 values or names or both from a huge range of data. In this article, you’ll get to learn all of the fruitful techniques to find the top 5 values as well as names in Excel.

The above screenshot is an overview of the article which represents the dataset & an example of how you can extract the top 5 values along with names from a range of data. You’ll get to learn more about the dataset along with all suitable functions in the following methods in this article.

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

**8 Suitable Ways to Find Top 5 Values and Names in Excel without or with Duplicates**

**1. Finding the Top 5 Values & Names without Duplicates**

In our first criteria, we’ll deal with the values where no duplicates are lying.

**1.1 Getting Top 5 Values by Using LARGE & ROWS Functions Together**

Let’s get introduced to our dataset now. **Column B** represents random names of 10 students and **Column C** shows the CGPA of each student of a term final in their university.

Now we’ll find out the top 5 CGPA only by using** LARGE** along with **ROWS** functions. The **LARGE** function will find out the largest value from a range of cells based on the serial number defined by the **ROWS** functions. We’ll get the names as well in the next method.

**📌**** Steps:**

➤ Select **Cell E7** & type:

`=LARGE($C$5:$C$14,ROWS($E$7:$E7))`

➤ Press **Enter** & you’ll get the 1st highest CGPA among all from **Column C**.

➤ Now use **Fill Handle** to fill down 4 more cells to get the next largest 4 CGPA.

**Read More:** **Top 10 Values Based on Criteria in Excel (Both Single and Multiple Criteria)**

**1.2 Pulling Out the Top 5 Names by Combining INDEX & MATCH Functions**

Now we’ll find out the names who got top 5 CGPAs. We have to incorporate **INDEX, MATCH, LARGE & ROWS** functions together. Here, in** Column F**, the top 5 values from **Column C **have to be determined first by following the previous method. Then we have to move to **Column E** for extracting the related names based on their CGPA.

**📌**** Steps:**

➤ In **Cell E7**, our necessary formula will be:

`=INDEX($B$5:$B$14,MATCH(LARGE($C$5:$C$14,ROWS($E$7:$E7)),$C$5:$C$14,0))`

➤ After pressing **Enter**, you’ll get the 1st name ‘Jonas’ who got the highest CGPA- 4.00.

➤ Now use **Fill Handle** to get the next 4 names in that column & you’re done.

**🔎 How Does This Formula Work?**

➤ **ROWS** function inputs the serial number for the **LARGE** function.

➤ The **LARGE** function finds out the largest value from the array or range of cells selected based on the serial number.

➤ **MATCH** function looks for the obtained largest value in the array of values & returns with the row number of that value.

➤ **INDEX** function finally pulls out the name from the column of Names based on that row number found by the **MATCH** function.

**Read More:** **How to Find Value in Column in Excel (4 Methods)**

**1.3 Extracting the Top 5 Names by Using XLOOKUP Function**

If you want to avoid the **INDEX-MATCH** formula then you can replace it with the **XLOOKUP **function searches for a range of cells or array & then returns values based on the selected conditions from the selected column or row.

**📌**** Steps:**

➤ In **Cell E7**, our related formula with **XLOOKUP** to find the names with be:

`=XLOOKUP(LARGE($C$5:$C$14,ROWS($E$7:$E7)),$C$5:$C$14,$B$5:$B$14)`

➤ Press **Enter** & use **Fill Handle** to get the other 4 names.

In the 1st argument of the **XLOOKUP** function, the largest value has been inputted. 2nd argument is the **Range of Cells C5:C14** where the selected largest value will be looked for. And the 3rd argument is another range of cells **B5:B14** from where the particular data or name will be extracted based on the row number found by the 1st two arguments.

**Read More:** **Lookup Value in Column and Return Value of Another Column in Excel**

**1.4 Finding the Top 5 Names & Values under Multiple Criteria**

Let’s think of another dataset now which comprises multiple criteria. If you notice, we have now names & CGPA in **Columns B & D** respectively. **Column C** represents the departments of the students.

We’ll find out first the top 5 CGPA from the Computer Science department & the output results will be achieved in **Column H**.

**📌**** Steps:**

➤ To find the top 5 CGPAs, the related formula in **Cell H12** will be:

`=LARGE(IF($C$5:$C$23=$G$9,$D$5:$D$23),$F12)`

➤ Press **Enter**, use **Fill Handle** to get the other 4 largest values & you’re done.

Here, with the** IF** function, we’re finding out all the CGPAs of the students from the Computer Science department only. Then the **LARGE **function extracts the top 5 CGPA like before.

Now, we’ll determine the names who got these top 5 CGPA’s and we’ll use **INDEX-MATCH **functions here.

**📌**** Steps:**

➤ In the output **Cell G12**, we have to type:

`=INDEX($B$5:$B$23,MATCH(LARGE(IF($C$5:$C$23=$G$9,$D$5:$D$23),$F12), IF($C$5:$C$23=$G$9,$D$5:$D$23),0))`

➤ Press **Enter** & use** Fill Handle** to fill down the rest of the 4 cells. You’ll get all the names at once.

**Read More:** **How to Find Value in Column Using VBA in Excel (4 Ways)**

**2. Finding Top 5 Values & Names Including Duplicates**

Now we’ll find the top 5 values as well as names including duplicates. We’ll have a bit of a modified dataset here.

**2.1 Getting Top 5 Values Only by Using Large & ROWS Functions Together**

So, in the picture below, **Column B** represents the names of 5 students and **Columns C to J** are showing the CGPA of each semester for those students. In the bottom table, we’ll find out the output data.

So, at first, we’ll find out the highest 5 CGPA for Andrew among all CGPA from 8 semesters.

**📌**** Steps:**

➤ Select **Cell C13** & type:

`=LARGE($C6:$N6,COLUMNS($C:C))`

➤ Press **Enter** & use **Fill Handle** to fill the next 4 cells along the row for Andrew.

If you notice, you’ve got the value 4.00 twice as the output since Andrew’s CGPA was 4.00 in two terms. So, the** LARGE** function doesn’t omit duplicate values while searching for the largest ones from the range of data or cells.

Now, to get similar results for the other 4 students, we have to select the **Range of Cells- C13:G13** first. Then at the right bottom corner of the selected cells, you’ll find the** Fill Handle** option. Now use that option to drag down to the selected row for Harry & you’re done. You’ll get all the top 5 CGPA for all students at once.

**Read More:** **How to Find Highest Value in Excel Column (4 Methods)**

**2.2 Pulling Out Top 5 Names with Duplicates by Combining INDEX, MATCH & COUNTIF Functions**

Now, we’ll find out the names of the top 5 students with their CGPA where duplicate CGPA can exist. If you notice in our modified dataset, Sam & Mike both have similar CGPA- 3.94. But we want to find out the top 5 names including those two names who got similar CGPA’s as well with Excel functions.

**📌**** Steps:**

➤ Select **Cell F7** & type:

`=INDEX($B$5:$B$14, MATCH(1, ($C$5:$C$14=LARGE($C$5:$C$14, $E7))*(COUNTIF(F$6:F6, $B$5:$B$14)=0), 0))`

➤ Press **Enter**, use **Fill Handle** to get the other 4 names & you’re done. You’ve just got both names- Sam & Mike who got similar CGPA.

**🔎 How Does This Formula Work?**

➤ Here inside the **MATCH** function, two logical functions are presented which are multiplied by each other. These combined logical functions will search for the top 5 CGPA from **Column C** & will assign the number 1 for the top 5 & 0 for the rest of the values.

➤ **MATCH** function then searches for 1 only from the previous results found & returns with the row numbers for all matches.

➤** INDEX** function finally shows the names serially based on those row numbers found through all **MATCH** functions in **Column F**.

**Read More:** **How to Find First Occurrence of a Value in a Column in Excel (5 Ways)**

**2.3 Extracting Top 5 Names with Duplicates by Incorporating SORT & FILTER Functions**

By using **SORT & FILTER** functions, you can find the top 5 names along with the values more easily. You won’t have to extract the top 5 values here anymore before finding out the names.

**📌**** Steps:**

➤ In **Cell F7**, our related formula with **SORT & FILTER** functions will be:

`=SORT(FILTER(B5:C14, C5:C14>=LARGE(C5:C14, 5)), 2,-1)`

➤ Press** Enter** & you’ll get the top 5 CGPA’s along with the names of the students at once. You even don’t have to use Fill Handle here to get the rest of the values as the formula itself is doing all calculations for you.

So what’s happening here is the **FILTER **function with the **LARGE** function inside extracts all the largest values from the **Range of Cells- C5:C14.** The **SORT** function then shows all the values or CGPA in descending order along with the names from the array of **B5:C14.**

**Read More:** **How to Find Lowest Value in an Excel Column (6 Ways)**

**2.4 Find Top Names & Values with Duplicates by Merging INDEX, SORT & SEQUENCE Functions Together**

This is another great option & almost similar to the previous one. We’ll use** INDEX, SORT & SEQUENCE** functions together here.

**📌**** Steps:**

➤ Select **Cell F7 **& type:

`=INDEX(SORT(B5:C14,2,-1),SEQUENCE(5),{1,2})`

➤ Press **Enter** & you’re done. You’ll get the top 5 CGPA along with the names right away.

The concept is too simple here. The **SORT** function shows all the CGPA’s in descending order but the** SEQUENCE** function tells it to choose only the first 5. Then the **INDEX** function shows the final results with names & CGPA in an array.

**Read More:** **How to Find Last Occurrence of a Value in a Column in Excel (5 Methods)**

**Concluding Words**

I hope all of these methods to find the top 5 values and names will now prompt you to apply them in your regular Excel chores. If you have any questions or feedback, please let me know through your comments. Or you can check out our other interesting articles related to Excel functions on this website.

Fantastic! What I was looking for.

Great examples. I am however looking for a way for the example in “1.4 Finding the Top 5 Names & Values under Multiple Criteria” to work with duplicate CGPA values. As it stands, if two or more NAMES in the same DEPARTMENT have the same CGPA values in the top 5, the solution in 1.4 will just repeat the first NAME in the list that appears with the duplicate CGPA value as many times as there are duplicate values. eg. If the highest CGPA value is 3.99 and shared by Andrew, Sam and Bob, the table will just list Andrew with 3.99 in Positions 1, 2 and 3 and never mention Sam or Bob. Is there a workaround?

I think your query should meet the requirements in methods 2.2 to 2.4. You can use any of them while dealing with similar numeric values. If it yet does not fulfill your criteria, then let me know. I’ll catch you up as soon as possible!

Hi,

First of, your solution really helped me. I ended up using the Index Match CountIF solution 2.2 for my worksheet and it worked for the queries I had upto 11 rows. However, I have an issue when i do a data set more that 11 rows as mentioned in your formulas in 2.2, it returns either an N/A error

Is there a way to increase the range of the row range?

The table range is with names in rows B2:B260, corresponding to a quantity column C2:C260 and a Net Column D2:D260. I have to pull out the best 5 and the worst 5 from the dataset.

Hello, we are glad to hear that our article has helped you. After seeing your comment, I have extended the dataset to 260 rows and the formulas have worked perfectly from my end. Can you please recheck whether you have changed the formula text corresponding to your dataset? For example, in the dataset of 11 rows the formula for largest 5 values will be like

`=LARGE($C$5:$C$14,ROWS($G$7:$G7))`

but when you extended the dataset till 260th row then the formula will be like

`=LARGE($C$5:$C$260,ROWS($G$7:$G7))`

.If you still face the problem then inform us in the reply. Thank you!

I used the Vlookup (1.3) method and it has worked, however it only shows the first column of my data not the second. Using Fill just repeats the same data. On 1.3 above i’m getting the right order for the names, but the CPGA isn’t showing. Any idea on how to correct it?

Thanks!

Hello RJ LENNOX!

In method 1.3m there have been shown only the formula for the names of the right order and in previous methods, there has been shown formula to get the value of CGPA in the right order. Please insert this formula into cell F7:

`=LARGE($C$5:$C$14,ROWS($F$7:$F7))`

and, drag the fill handle to get the top 5 cgpa values.

I hope, your problem will be solved in this way. If not, please share the Excel file and send us the problem with a little more explanation in an email at

[email protected]Thank You!