What is the formula for comparing lists of data in Excel?

This formula in particular allows you to take a partial list with some blank data and compare it to a master list and retrieve the blank data.  For example, let's say we have a list of 10 student names that we need Student ID's for, and the only point of reference that we have is a master list of all 200 students and their respective ID's. The formula allows us to match the name from our list to the master list, and bring a copy of the student ID to our list. Attached is a spreadsheet outlining these details in a more visual method.  
Formula for data comparison in Excel

=INDEX($B$2:$B$6,MATCH($D2,$A$2:$A$6,0))

B - information trying to find (A student ID for example)

D2 - column to compare from (Our list)

A2 - Column to compare to (the master list)

As mentioned, your output will be the student ID's for all the students in our list.  
Index_Match_formula_Flowers.xlsx

Was this article helpful?


Thanks for your feedback! Did this solve your issue?

Comments (optional):


Thanks for your feedback!
We're glad it was helpful but sorry it didn’t solve your issue. If you need assistance, click Chat with Support below.
We’re sorry to hear that. Please tell us why.

 I don't like how this works.

 The answer is confusing.

 The answer didn't match what I was searching for.

Additional Comments (optional):


Thanks for your feedback! If you need assistance, click Chat with Support below.
Thanks for your feedback. Help us make our products even better by sharing details in our Idea Banks or our online Community.
Thanks for letting us know. We'll work on clarifying the information in the article. If you need assistance, click Chat with Support below.
Thanks for letting us know. We'll work on updating the search engine to return more relevant results.