How to Find Top 5 Values and Names in Excel (14 Useful Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this Excel tutorial, we will learn how to find the top 5 values and names in Excel. We will discuss:

  • Finding the top 5 values and names with duplicates or ties
  • Getting top 5 values and names without duplicates
  • Extracting top 5 values and names based on single or multiple criteria
  • Highlighting top 5 values and names using Conditional Formatting
  • VBA to find the top 5 values and names in Excel

Sometimes you may need to find the top 3, 5, or 10 values and names from a dataset. While finding only the top value and name from a dataset is easy, finding the top 5 values and names is challenging. And, if we assign one or multiple criteria on top of that, finding the top 5 values and names becomes very complex. Don’t worry. By the end of this article, you will have complete knowledge of how to find the top 5 values and names in Excel.

In Microsoft Excel 365, we have various dynamic array formulas. These formulas are very helpful in finding the top 5 values and names. In Excel 2019 or earlier versions, we have to use array formulas, press Ctrl + Shift + Enter instead of the Enter key, and drag down the Fill Handle icon. In this tutorial, we will cover both types of formulas.

Overview of Finding Top 5 Values and Names in Excel


1. How to Find Top 5 Values and Names with Duplicates in Excel

Duplicates or ties are often present while finding the top 5 values and names. In this section, we will present 6 ideal methods to find the top 5 values and names with duplicates in Excel.

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. If you notice our dataset, Sam & Mike both have a similar CGPA- 3.94. We want to find out the top 5 names including those two names who got similar CGPA’s as well with Excel functions.

Dataset for Finding Top 5 Values and Names with Duplicates


1.1 Using Combinations of LARGE, ROWS, INDEX, MATCH, LARGE & COUNTIF Functions

In this section, we will use the LARGE and ROWS functions to find the top 5 values and combine the INDEX, MATCH, LARGE, and COUNTIF functions to find the top 5 values. Follow the steps below to learn more about this.

 

📌 Steps:

  • In Cell G7, insert the following formula.
=LARGE($C$5:$C$14,ROWS($G$7:$G7))
  • Then, press the Enter key and drag down the Fill Handle icon to copy the formula in the remaining cells. This will calculate the top 5 values.

Using LARGE and ROWS functions to Find Top 5 Values

  • 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.

find top 5 values and names with duplicates

🔎 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.

1.2 Joining SORT, FILTER & LARGE Functions

By using the SORT & FILTER functions, you can find the top 5 names along with the values more easily. We will extract the top 5 values and names at a time using this formula.

📌 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 the calculations for you.

find top 5 values and names with duplicates by sort filter

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.

Note: This formula will work only in Excel 2021 or Excel for Microsoft 365 as the SORT function is not available in earlier versions.

1.3 Combining CHOOSEROWS, SORT & SEQUENCE Functions

This formula is also only applicable in Excel for Microsoft 365 as the CHOOSEROWS function isn’t available in other versions. We can specify the rows with the top 5 values and names using a combination of the SORT and SEQUENCE functions. Then, the CHOOSEROWS function will return these rows for us.

📌 Steps:

  • Insert the following formula in Cell F7.
=CHOOSEROWS(SORT(B5:C14,2,-1),SEQUENCE(5))
  • Then, press the Enter key. The top 5 values and names will appear in the range F7:G11.

Using CHOOSEROWS, SORT & SEQUENCE functions to find top 5 values and names in Excel for Microsoft 365

Note: Combine the CHOOSECOLS function if you want to return specific columns.

1.4 Using 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.

find top 5 values and names with duplicates by index sort sequence

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.


1.5 Finding Top 5 Values and Names By Creating Unique Identifiers

If you don’t want to use an array formula then, you can handle duplicates or ties by creating unique identifiers using the COUNTIF function.

Except for the first value in duplicates, we will multiply the remaining duplicates with a very small number and then add/subtract the result from the previous value. As a result, each duplicate value will change an insignificant amount but become unique as well.

You can follow the steps below to learn more about this.

Step 1: We require an additional column Unique Identifier (column D) for this method. In Cell D5, insert the following formula.

=C5+(COUNTIF(C5:$C$14,C5)-1)*0.000001

Then, press the Enter key and drag down the Fill Handle icon.

Using COUNTIF function to create Unique Identifiers for the Duplicate Values

It may seem that there hasn’t been any change, but if you use the Increase Decimal button on the duplicates, you will notice that they are different now.

Created unique identifiers for the duplicated values

Step 2: Type in the formula below in Cell G7 => press the Enter key => use the Fill Handle tool to copy the formula.

=INDEX($B$5:$B$14,MATCH(LARGE($D$5:D$14,ROWS($D$5:D5)),$D$5:$D$14,0),)

This will find us the Top 5 names.

Using Unique Identifiers to Find Top 5 Names

Step 3: Finally, for the top 5 values, insert the following formula in Cell H7 => press the Enter key => drag down the Fill Handle icon.

=INDEX($C$5:$C$14,MATCH(LARGE($D$5:D$14,ROWS($D$5:D5)),$D$5:$D$14,0),)

Using Unique Identifiers to Find Top 5 Values


1.6 Using LARGE & COLUMNS Functions Together

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

find top 5 values and names with duplicates by large function

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.

find top 5 values and names with duplicates by large function

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.

find top 5 values and names with duplicates by large function

Read More: How to Create Top 10 List with Duplicates in Excel


2. How to Extract Top 5 Values and Names without Duplicates in Excel

If duplicates or ties are not present in your present, then you can find the top 5 names and values using the following two steps.


2.1 Getting Top 5 Values by Using LARGE & ROWS Functions Together

Here we have a similar dataset as the previous section. However, no duplicates or ties are present in the dataset. Now we’ll find out the top 5 CGPA only by combining LARGE and 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.

find top 5 values and names by large rows functions

📌 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 the Fill Handle to fill down 4 more cells to get the next largest 4 CGPA.

find top 5 values and names by large rows functions


2.2 Find Top 5 Names by Matching Top 5 Values

We can apply the following two methods to find the top 5 names by matching the top 5 values calculated in the previous step.


Method 1: Using INDEX, MATCH, LARGE & ROWS Functions Together

Now we’ll find out the names who got the 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 to extract 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.

find top 5 values and names by index match

🔎 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.
  • The 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.

Method 2: Combining XLOOKUP & LARGE Functions

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 arrays & 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.

find top 5 values and names by xlookup

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


3. How to Find Top 5 Values and Names With Criteria in Excel

We can impose one or multiple criteria while finding the top 5 values and names in Excel. We can find the top 5 names and values with formulas or a Pivot Table (no formula) in such cases. Read the following examples to learn more about this.


3.1 Finding Top 5 Values and Names Based on Single Criterion

If you have only a single criterion, then you can apply the following two methods to find the top 5 values and names in Excel.


Method 1: Using INDEX, MATCH, LARGE & IF Functions

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.

find top 5 values and names with multiple criteria

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.

find top 5 values and names with multiple criteria

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 the Fill Handle to fill down the rest of the 4 cells. You’ll get all the names at once.

find top 5 values and names with multiple criteria

Read More: How to Get Top 10 Values Based on Criteria in Excel


Method 2: Applying INDEX, SORT, FILTER & SEQUENCE Functions

If you are using Excel for Microsoft 365, then you can use a combination of INDEX, SORT, FILTER, and SEQUENCE functions to extract the top 5 values and names based on a single criteria with only one formula.

📌 Steps:

  • Insert the following formula in Cell G12.
=INDEX(SORT(FILTER(B5:D23,C5:C23=G9),3,-1),SEQUENCE(5),{1,3})
  • Press the Enter key to get the list of the top 5 values and names.

Using INDEX, SORT, FILTER & SEQUENCE function to find top 5 names and values based on criteria in Excel


3.2 Finding Top 5 Values and Names Based on Multiple Criteria

In this section, we will impose another condition while finding the top 5 values and names. These criteria can be AND/OR type. Here, we will consider the criteria as OR type. So, we want the top 5 names and values with Computer Science department criteria or CGPA more than 3.9 criteria.

Dataset to Find Top 5 Values and Names based on multiple criteria in Excel

Step 1: Insert the following formula in Cell H12 => press the Enter key => drag down the Fill Handle icon.

=LARGE(IF(($C$5:$C$23=$H$8)+($D$5:$D$23>$H$9),$D$5:$D$23),F12)

This will calculate the top 5 values.

Finding Top 5 Values based on Multiple Criteria

Step 2: Then, type in the following formula in Cell G12 => press the Enter key => drag down or double-click the Fill Handle icon to get the top 5 names.

=INDEX($B$5:$B$23,SMALL(IF(($D$5:$D$23=H12)*(($C$5:$C$23=$H$8)+($D$5:$D$23>$H$9)),ROW($D$5:$D$23)-ROW($D$4)),COUNTIF(H12:$H$12,H12)))

Finding Top 5 Names based on Multiple Criteria

Note: With Excel, the Plus (+) symbol is used for OR logic, and the Asterisk (*) symbol is used for AND logic. Therefore, if you want multiple AND type criteria, then use the Asterisk symbol instead of the Plus symbol.

For the top 5 values with AND criteria, in Cell H12 apply the following formula:

=LARGE(IF(($C$5:$C$23=$H$8)*($D$5:$D$23>$H$9),$D$5:$D$23),F12)

For the top 5 names with AND criteria, apply the following formula in Cell G12:

=INDEX($B$5:$B$23,SMALL(IF(($D$5:$D$23=H12)*(($C$5:$C$23=$H$8)*($D$5:$D$23>$H$9)),ROW($D$5:$D$23)-ROW($D$4)),COUNTIF(H12:$H$12,H12)))

3.3 Inserting Pivot Table Slicer to Find Top 5 Values and Names Based on Criteria (Without Formula)

If you don’t want to apply any formula to filter the top 5 values and names based on criteria, then you can insert a Pivot Table => apply a Value Filter for the Row Labels => insert Slicers. Read the following steps to learn more about this.

Step 1: Select the range B4:D23 => go to the Insert tab => click the PivotTable dropdown => you will find the From Table/Range option here.

Inserting a Pivot Table from Insert tab

Step 2: After clicking the From Table/Range option, a dialog box like the following will appear. Select the radio button of the Existing Worksheet option and set the Location to Cell F4.

Selecting Location for the Pivot Table

Step 3: After clicking the OK button, a Pivot Table will appear in the selected location. The PivotTable Fields right-side pane will also appear.

Pivot Table and right-side pane of Pivot Table Fields

Click the image for a detailed view

Step 4: Enable the checkboxes of Name and CGPA fields so that they appear in Rows and Σ Values areas respectively. You will get a Pivot Table with Names as Row Labels and Sum of CGPA. There will be a dropdown icon for Row Labels.

Selecting Fields for the Pivot Table

Click the image for a detailed view

Step 5: Click the Row Labels dropdown => hover the mouse pointer over the Value Filters option in the dropdown menu => you will find the Top 10 option.

Top 10 option under Value Filters option in Row Labels dropdown

Step 6: After selecting the Top 10 option, you will get the Top 10 Filter (Name) dialog box. To get the top 5, reduce the number from 10 to 5.

Changing the Value to 5 in Top 10 dialog box to get top 5 values and names in Excel Pivot Table

Step 7: After clicking the OK button you will get only the top 5 names and values in the Pivot Table. Select any cell in the Pivot Table and you will find the PivotTable Analyze tab. Go to the PivotTable Analyze tab and you will find the Insert Slicer command.

Inserting Slicer from PivotTable Analyze tab

Step 8: After, clicking the Insert Slicer command, you will get the following dialog box. Check the checkbox of the Department option.

Selecting Department option for Inserting Slicers

Step 9: After clicking the OK button, the Department Slicer will appear in your worksheet.

Slicers to apply criteria on top 5 values and names in Excel

Step 10: Select any department from the slicer. You will see the corresponding top 5 names and values in the Pivot Table.

Selecting a Slicer to view changes in top 5 values and names in Excel Pivot Table

Note: Although we will get the top 5 values and names, the list will not be sorted based on the values, but the alphabetical order of the names.

4. How to Highlight Top 5 Values and Names Using Conditional Formatting in Excel

If you want to highlight the top 5 values and names without extracting them, then you can use the Conditional Formatting feature. Apply the following steps to highlight rows with the top 5 values and names.

Step 1: Select the range B5:C23 => go to the Home tab => click the Conditional Formatting dropdown => you will find the New Rule command.

Applying a New Rule from Conditional Formatting dropdown

Step 2: After clicking the New Rule command, you will get the Edit Formatting Rule dialog box. Select the Use a formula to determine which cells to format option and set the formula to the following:

=$C5>=LARGE($C$2:$C$23,5)

Using a formula to determine cells with top 5 values and names

 

Step 3: Click the Format button and you will get the Format Cells dialog box. Go to the Fill tab and select the color you prefer.

Selecting Fill Color

Click the image to get a detailed view

 

Tip: You can similarly change the Font Color and Font Style from the Font tab.

Step 4: After clicking the OK button in the Format Cells dialog box, you will get a preview of the formatting of the formatted cells.

Applying the formatting rule to highlight top 5 values and names

 

Step 5: Click the OK button in the Edit Formatting Rule dialog box. This will highlight the rows with the top 5 values and names.

Top 5 values and names highlighted using Conditional Formatting in Excel

Read More: How to Check If a Value is in List in Excel


5. How to Find Top 5 Values and Names Using VBA

If you feel the array formulas are too complex, then you can apply a simple VBA code to extract the top 5 values and names from a dataset. Apply the following steps to find the top 5 values and names using VBA.

Step 1: Go to the Developer tab. You will see the Visual Basic option.

Visual Basic Option in Developer tab

Step 2: After clicking the Visual Basic option, the Visual Basic Editor window will open. If you click the Insert menu, you will find the Module option.

Inserting a Module

Note: If the Developer tab is not available in your Excel ribbon, then you can use the keyboard shortcut Alt + F11 to open the Visual Basic Editor window.

Step 3: After selecting the Module option, Module1 will appear. Insert the following code in the module => click the Save button => click the Run button.

Save and Run VBA to Find Top 5 Values and Names in Excel

Sub ExtractTop5ValuesAndNames()
    Dim ws As Worksheet
    Dim last_row As Integer
    
    Dim top5_names() As Variant
    Dim top5_values() As Variant
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    Dim temp_values As Double
    Dim temp_name As String
    
    Dim dataName_col As Integer
    Dim dataValues_col As Integer
    Dim dataStart_row As Integer
    
    Dim outStart_row As Integer
    Dim outName_col As Integer
    Dim outValues_col As Integer
    
    Set ws = ThisWorkbook.ActiveSheet
    
    dataName_col = 2
    dataValues_col = 3
    dataStart_row = 5
    
    outStart_row = 7
    outName_col = 6
    outValues_col = 7
    
    lastRow = ws.Cells(ws.Rows.Count, dataName_col).End(xlUp).Row
    
    ReDim top5_names(1 To 5)
    ReDim top5_values(1 To 5)
    
    For i = 1 To 5
        top5_values(i) = -1
    Next i
    
    For i = dataStart_row To lastRow
        temp_values = ws.Cells(i, dataValues_col).Value
        temp_name = ws.Cells(i, dataName_col).Value
        
        For j = 1 To 5
            If temp_values > top5_values(j) Then
                For k = 5 To j + 1 Step -1
                    top5_values(k) = top5_values(k - 1)
                    top5_names(k) = top5_names(k - 1)
                Next k
                
                top5_values(j) = temp_values
                top5_names(j) = temp_name
                Exit For
            End If
        Next j
    Next i
    
    For i = 1 To 5
        ws.Cells(i + outStart_row - 1, outName_col).Value = top5_names(i)
        ws.Cells(i + outStart_row - 1, outValues_col).Value = top5_values(i)
    Next i
End Sub

Step 4: After Running the VBA, return to your worksheet. You will find the top 5 values and names in the range F7:G11.

Top 5 values and names in Excel extracted using VBA


What Are the Things to Remember?

  • If you are using Excel 2019 or earlier versions, then press Ctrl + Shift + Enter keys instead of the Enter key while using the array formulas.
  • While imposing multiple criteria in the formula, use the Plus symbol (+) for OR logic, and the Asterisk symbol (*) for AND logic.
  • The list of top 5 values and names calculated using the Value Filters of Row Labels option in the Pivot Table is not sorted based on the values.

Download Practice Workbook


This concludes our tutorial on finding the top 5 values and names in Excel. We discussed various formulas for Microsoft 365 and Excel 2019 or earlier versions. Our formulas can handle duplicates or ties and single or multiple criteria while finding the top 5 values and names. This tutorial also presents how to highlight the top 5 values and names using Conditional Formatting and how to extract them using VBA.

We hope that these methods were helpful to you. Let us know your thoughts on the article in the comment section.


Related Articles


<< Go Back to Find Value in Range | Excel Range | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

7 Comments
  1. Fantastic! What I was looking for.

  2. 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!

  3. 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.

    • Reply Avatar photo
      Osman Goni Ridwan Aug 4, 2022 at 2:04 PM

      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!

  4. 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!

    • Reply Avatar photo
      Osman Goni Ridwan Sep 1, 2022 at 10:25 AM

      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!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo