You can calculate the maximum value using the MAX function, and the LARGE function to calculate the second largest value. However, to calculate the row number, you will need to combine it with the MATCH function. The row number is the relative position in the data range.
Example: You work with the survey data with the age group in column A and the number of responses in column B.
Formula 1: To count the row number for the second largest value, please use the following formula.
=MATCH(LARGE(B2:B6,2), B2:B6,0)
- LARGE(B2:B6,2) is to find the second largest value in the date range; and
- 0 is the match type which means the exact match.
To calculate the row number for the third largest value or any other value, please change 2 to 3 or the number you need with the formula below.
=MATCH(LARGE(B2:B6,3), B2:B6,0)
Formula 2: To count the second largest value row number with duplicates, please use the following formula.
However, the above formula will not work properly in situations where 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)
- COUNTIF(A2:A6, MAX(A2:A6)): To count the number of max values;
- LARGE(A2:A6, COUNTIF(A2:A6, MAX(A2:A6))+1): To find the second large value;
- MATCH(LARGE(A2:A6, COUNTIF(A2:A6, MAX(A2:A6))+1), A2:A6,0): To find the relative position of the second largest value.
