How to Find Second Largest Value with Criteria In Excel

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to show you 4 quick tricks to find the second largest value with criteria in excel. You can use these methods even in large datasets to find out the second largest value by setting criteria. Throughout this tutorial, you will also learn some important excel tools and techniques which will be very useful in any excel related task.


Download Practice Workbook

You can download the practice workbook from here.


4 Quick Tricks to Find Second Largest Value with Criteria in Excel

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 3 columns. Initially, we are keeping all the cells in Currency format. For all the datasets, we have 2 unique columns which are Club Name, Home Kit and Away Kit. Although we may vary the number of columns later on if that is needed.

excel find second largest value with criteria


1. Using LARGE Function

The LARGE function in excel can return a number from a list of numbers after we sorted it in descending order. Let us see how to apply this function to find the second largest value with criteria.

Steps:

  • First, go to cell F7 and insert the following formula:
=LARGE(IF(C4:D4=F5,C5:D10),2)

large function to find the second largest value with criteria in excel

  • Now, press Enter and this will calculate the second largest Home Kit price inside F7.

🔎 How Does the Formula Work?

  • IF(C4:D4=F5,C5:D10): This portion returns an array of the cell values and FALSE cell values.
  • =LARGE(IF(C4:D4=F5,C5:D10),2): This part of the formula returns the final value of 119.

Read More: How to Use VBA Large Function in Excel (4 Suitable Examples)


2. Applying AGGREGATE Function

The AGGREGATE function in excel gives us the ability to perform aggregate calculations like COUNT, AVERAGE, MAX, etc. This function also ignores any hidden rows or errors. We will use this function to find the second-largest value with specific criteria. Follow the steps below to do this.

Steps:

  • To begin with, double-click on cell F7 and enter the below formula:
=AGGREGATE(14,6,(C5:D10)/(C4:D4=F5),2)

using aggregate function to find the second largest value with criteria in excel

  • Next, press the Enter key and you should get the second-largest away kit price.

Read More: How to Use Excel Large Function with Criteria( 4 Suitable Ways)


3. Utilizing SUMPRODUCT Function

The SUMPRODUCT function in excel first multiplies the range of values and then gives the sum of those multiplications. We can use this function along with the LARGE function to find the second largest value with criteria.

Steps:

  • To begin this method, double-click on cell F7 and insert the formula below:
=SUMPRODUCT(LARGE((B5:B10=F5)*(C5:D10),2))

using sumproduct function to find the second largest value with criteria in excel

  • Next, press the Enter key and consequently, this will find the second largest price value for the Man City kit inside cell C10.

🔎 How Does the Formula Work?

  • (B5:B10=F5)*(C5:D10): This portion of the formula returns an array of values that are the highest in the list and other values as 0.
  • LARGE((B5:B10=F5)*(C5:D10),2): This portion gives the value 80 as the second largest value.
  • =SUMPRODUCT(LARGE((B5:B10=F5)*(C5:D10),2)): This part gives back the final value which is 80 in this case. 

Read More: How to Find Largest Lookup Value in Excel (3 Easy Ways)


4. Using VBA Code

If you are familiar with VBA in excel, you can find the second-largest value with criteria with just a few clicks. Let us see how to do this.

Steps:

  • For this method, go to the Developer tab and select Visual Basic.

using vba code to find the second largest value with criteria in excel

  • Now, select Insert in the VBA window and click on Module.

  • Next, type in the formula below in the new window:
Sub SecondLargest()
Range("F7").FormulaArray = "=LARGE(IF(C4:D4=F5,C5:D12),2)"
End Sub

  • Then, open the macro from the Developer tab by clicking on Macros.

  • Now, in the Macro window, select the SecondLargest macro and click Run.

  • As a result, the VBA code will calculate the second-highest value from all the away kits inside cell F7.

Read More: How to Lookup Next Largest Value in Excel (4 Easy Ways)


How to Find Top 5 Values and Names with Criteria in Excel

In this method, we will see the detailed steps to find the top 5 values and names with criteria in excel.

Steps:

  • To begin with, double-click on cell E5 and enter the below formula:
=LARGE($C$5:$C$10,ROWS($E$5:$E5))

  • Then, press the Enter key and copy this formula to the cells using Fill Handle.
  • Consequently, this will find the top 5 values for the home kits.

🔎 How Does the Formula Work?

  • ROWS($E$5:$E5): This portion gives the value of 1.
  • =LARGE($C$5:$C$10,ROWS($E$5:$E5)): This portion returns the final value that is the top 5 home kit prices.

Read More: [Fixed!] Sort Largest to Smallest Not Working in Excel


Things to Remember

  • You can use the ALT+F11 shortcut to open the VBA window and ALT+F8 to open the Macros window.
  • Note that the LARGE function ignores cells that are empty or contain TRUE or FALSE values in them.
  • If there is no numeric value, this function might return the #NUM! Error as a result.

Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to find the second-largest value with criteria in excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Article

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo