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;

Any way to get this to work when there are duplicate values? If I have two 5s as the largest and second highest it only take one of them

Thank you for the question, we added a second portion to deal with the situation when there are duplicates for the maximum value.