How to Calculate Row Number with the Second Largest Value

You can calculate the maximum value using the MAX function, as well as the Large function to calculate the second largest value.

However, to calculate the row number, you will need the MATCH function. If the row number for the second largest value, you will need to combine the two functions together.

To calculate the second largest value row number, please use the following formula.

=MATCH(LARGE(B2:B6,2),B2:B6,0)

Where LARGE(B2:B6,2) is to find the second largest value in the date range, and 0 is the match type meaning exact match.

To calculate the row number for the third largest value, please change 2 to 3 with the formula below.

=MATCH(LARGE(B2:B6,3),B2:B6,0)

Where 0 is the match type meaning exact match. You can change 2 or 3 to any other number to have the Nth largest number in the data range.

However, the above formula may not work properly in the situations that the dataset has duplicated values, please refer to the following formula:

=MATCH(LARGE(A2:A6, COUNTIF(A2:A6,MAX(A2:A6))+1), A2:A6,0)

=LARGE(A2:A6, COUNTIF(A2:A6,MAX(A2:A6))+1) is to find the second largest value in the dataset with duplicates.

Leave a Reply