Excel dropdown INDEX MATCH formula

You are currently viewing Excel dropdown INDEX MATCH formula

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.

Dataset

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]")

Using data validation to create dynamic dropdown list

To find the ID of the student, construct the INDEX MATCH formula in G2, like shown below:

Constructing the INDEX MATCH formula

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.

Download Practice Workbook

Kunal

Hey, I am Kunal Aggarwal, the founder and author of Kunal Excel Tip based in New Delhi, India. I have completed my postgraduation from NIIT Limited, New Delhi in Excel course in 2019. Since then, I have been using and learning Microsoft Excel and started writing posts on the spreadsheet software here since March 2024.

Leave a Reply