In Excel, to find the maximum, minimum and average value in the range and return the corresponding value from another range, use the INDEX MATCH formula with the AVERAGE, MAX and MIN functions.
The INDEX MATCH formula is used to look up the value in any column of the source data and return the corresponding value from the left column.
I have the following dataset. Now I want that when the user makes a selection from the dropdown (Area, Population) in B12, the program will calculate the maximum, minimum and average value in column B or C depending on the selection and return the corresponding value from column A, like shown below:
To accomplish the task, I perform the following steps:
Convert the source data into an Excel table and name it as data. To do so, select the source data range (including the headers), go to the Insert tab and click on the Table button.
Create named ranges separately for the Area range and Population range. Select the Area range and name it as area in the name box. Similarly, select the Population range and name it as population.
Use data validation to create category dropdown list in B12, listing area and population ranges. To do so, go to Data > Data Validation > Allow: List > Source: =$B$2:$C$2
To find the maximum value in column B or C depending on the selection from B12 and return the corresponding value from column A, construct the following formula in B13, like shown below:
To find the minimum value in column B or C depending on the selection from B12 and return the corresponding value from column A, construct the following formula in B14, like shown below:
To find the average value in column B or C depending on the selection from B12 and return the corresponding value from column A, construct the following formula in B15, like shown below:
To replace the REF! error with an empty string "", nest all the formulas in the IFERROR function.
Your dropdown INDEX MATCH with AVERAGE, MAX, MIN calculator is ready! I hope this post helped you.
