When working with Excel, especially when you automate a report, you may need to get the group name for the maximum value.
Example: You are working with the survey data with many different communities, and in each community's data, column A is the participants' age groups, and column B is the number of responses in the age group.
Formula: To get the age group that has the most responses
To get the group name for the maximum value, please will need the MAX function, the MATCH function, and the INDEX function.
=INDEX(A2:A6, MATCH(MAX(B2:B6),B2:B6,0))
- MAX(B2:B6) is to find the maximum value in the data range B2:B6, which returns 76;
- MATCH(MAX(B2:B6), B2:B6,0) is to find the row number that has the maximum value and it returns 4;
- INDEX(A2:A6, MATCH(MAX(B2:B6), B2:B6,0)) is to get the group name from column A, which is "40 – 49 Years Old".
