In Excel, to look up the value in any column of the source data and return the corresponding value from the left column, use the INDEX MATCH formula.
I have the following dataset. Now I want to find the ID of the student in G2 based on the student selection from the dropdown in G1.
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.
Use data validation to create Student dropdown list in G1, listing student names in column B. To do so, go to Data > Data Validation > Allow: List > Source: =INDIRECT("data[student]")
To find the ID of the student, construct the INDEX MATCH formula in G2, like shown below:
To replace the N/A error with an empty string "", nest the entire formula in the IFNA function.
Your dropdown INDEX MATCH formula is ready! I hope this post helped you.