In Excel, to return a value based on the selection of the dropdown list, I will use an Excel table, data validation to create dynamic dropdown list and the INDEX function.
The following dataset contains eight products that were consumed in the first three months:
Now I want the program to instantly return the value in G7 when the user makes a selection from the Product and Month dropdowns in G5 and G6 respectively.
I convert the range in column A to an Excel table. I do so by selecting the range A5:A13 > Insert > Table > OK. By creating an Excel table, I can create the Product dynamic dropdown list.
I use data validation to create a dynamic dropdown list in G5, listing all the Product numbers in column A, by going to Data > Data Validation > Allow: List > Source: =INDIRECT("Table1")
To create a dynamic dropdown list for Month, I select the range B5:D5 and name it as month in the name box, like shown below:
I use data validation to create a dynamic dropdown list in G6, listing all the Month numbers in row 5, by going to Data > Data Validation > Allow: List > Source: =month
After all this, I construct my INDEX formula in G7, like shown below:
The @ operator disables the array behavior of the INDEX formula and to replace the VALUE! error with an empty string "", I nest my entire formula in the IFERROR function.
Your dropdown INDEX formula is ready! I hope this post helped you.
