How to Get the Category Name with the Max Value

When working with Excel, especially when you automate a report, you may need to get the category name with the max value. For example, in the following table, what will be the age group that has the most count?

This seems very easy when there is only a simple table. However, it becomes very handy when working with a dataset that has many communities and each community needs a report.

To get the category name with the max value, please combine with the max function, the match function, and the index function.

=INDEX(A2:A6, MATCH(MAX(B2:B6),B2:B6,0))

Where MAX(B2:B6) is to find the maximum value in the data range B2:B6, and returns 76 in this example;

MATCH(MAX(B2:B6),B2:B6,0) is to find the row number that has the maximum value and returns 3;

INDEX(A2:A6, MATCH(MAX(B2:B6),B2:B6,0)) is to get the category name in row 3, which is "30-39 yrs".

Leave a Reply