When working with large survey data, especially when you need to automate a report, you may need to get the category name with the max value or the second largest value.
Example: You are working with the following dataset with the age in column A and the number of responses in column B.
Formula: What is the age group for the second largest group?
To calculate the category name according to the second largest value, please combine the MATCH function and the INDEX function.
=INDEX(A2:A6, MATCH(LARGE(B2:B6,2), B2:B6,0))
- LARGE(B2:B6,2): to find the second largest value, which is 54;
- MATCH(LARGE(B2:B6,2), B2:B6,0): to find the position of the second largest value, which is 3;
- The INDEX function returns the value in Column A.
Dataset has duplicates?
When there are duplicates in the dataset, the above formula may not work properly, for example, in the following dataset, the formula will return to "30-39 yrs" instead of "18-29 yrs".
To get the absolute second-largest value in the dataset, please refer to how to get the second-largest value with duplicates.
To have the category name with the second largest value, please use the following formula:
=INDEX(A2:A6, MATCH(LARGE(B2:B6, COUNTIF(B2:B6,MAX(B2:B6))+1),B2:B6,0))
- COUNTIF(B2:B6, MAX(B2:B6)) is to count the number of maximum values;
- LARGE(B2:B6, COUNTIF(B2:B6,MAX(B2:B6))+1) is to get the second largest value;