In Excel, to extract data from the database based on the selection of dropdown list, I will use the data validation to make dropdown list, the INDEX function that returns the value at the given location and the MATCH function that returns the numeric position of the item.
The following dataset contains weekly sales report:
Now I want the program to extract data of all the columns instantly when the user makes a selection of the emp name from the dropdown list, like shown below:
To accomplish the task, I will create dropdown list and use the INDEX and MATCH formula.
I use data validation using the shortcut [Alt A V Enter] in sequence to create a dropdown list in I5, listing all the emp names in column A, like so:
After this, I construct my INDEX and MATCH formula in I6 and drag the fill handle down to copy the formula to the rest of the cells like shown below:
Your dropdown INDEX and MATCH formula is ready! I hope this post helped you.
