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:
![Dataset Dataset](https://kunalexceltip.com/wp-content/uploads/2024/03/Dataset-11.png)
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")
![Using data validation to create a dynamic dropdown list Using data validation to create a dynamic dropdown list](https://kunalexceltip.com/wp-content/uploads/2024/03/Using-data-validation-to-create-a-dynamic-dropdown-list.png)
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:
![Naming the range in the name box Naming the range in the name box](https://kunalexceltip.com/wp-content/uploads/2024/03/Naming-the-range-in-the-name-box.png)
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
![Using data validation to create a dynamic dropdown list Using data validation to create a dynamic dropdown list](https://kunalexceltip.com/wp-content/uploads/2024/03/Using-data-validation-to-create-a-dynamic-dropdown-list-1.png)
After all this, I construct my INDEX formula in G7, like shown below:
![Constructing dropdown INDEX formula Constructing dropdown INDEX formula](https://kunalexceltip.com/wp-content/uploads/2024/03/Constructing-dropdown-INDEX-formula.gif)
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.