Merge tables with INDEX and MATCH
Let’s say you have two or more tables with a common column, however, the data in the common columns are not listed in the same order. In this case, to merge, combine or join the tables while matching the data, you can use the help of the INDEX and MATCH functions.
How to merge tables with INDEX and MATCH?
To join table 1 and table 2 to combine all the information in the new table as shown in the screenshot above, you should first copy the data from table 1 or table 2 into the new table (here I copied the data from table 1, see screenshot below). Take the first Student ID 23201 in the new table as an example, the INDEX and MATCH functions will help you to retrieve its score and rank information in this way: MATCH returns the row position of the student ID that matches 23201 in table 2. The row information will be fed to INDEX to retrieve the value at the intersection of that row and the specified column (score or rank column).
Generic syntax
=INDEX(return_table,MATCH(lookup_value,lookup_array,0),col_num)
√ Note: Since we have filled in the information from table 1, now we only need to retrieve corresponding data from table 2.
- return_table: The table from which to return students’ scores. Here refers to table 2.
- lookup_value: The value you used to match the information in the return_table. Here refers to the student ID value in the new table.
- lookup_array: The range of cells with the values to compare with the lookup_value. Here refers to the student ID column in the return_table.
- col_num: The column number that indicates from which column of the return_table to return the corresponding information.
- 0: The match_type 0 forces MATCH to perform an exact match.
To retrieve corresponding data from table 2 to combine all the information in the new table, please copy or enter the formulas below in the cells F16 and G16, and press Enter to get the results:
Cell F16 (Score)
=INDEX($F$5:$H$11,MATCH(C16,$F$5:$F$11,0),2)
Cell G16 (Rank)
=INDEX($F$5:$H$11,MATCH(C16,$F$5:$F$11,0),3)
√ Note: The dollar signs ($) above indicate absolute references, which means the return_table and lookup_array in the formula won't change when you move or copy the formulas to other cells. However, there are no dollar signs added to the lookup_value since you want it to be dynamic. After entering the formula, drag the fill handle down to apply the formula to the below cells.
Explanation of the formula
Here we use the formula below as an example:
=INDEX($F$5:$H$11,MATCH(C16,$F$5:$F$11,0),2)
- MATCH(C16,$F$5:$F$11,0): The match_type 0 forces the MATCH function to perform an exact match. The function then returns the position of the match value of 23201 (the value in C16) in the lookup array $F$5:$F$11. So, the function will return 3 since its match value is at the 3rd position in the range.
- INDEX($F$5:$H$11,MATCH(C16,$F$5:$F$11,0),2) = INDEX($F$5:$H$11,3,2): The INDEX function returns the value at the intersection of the 3rd row and 2nd column of the return table $F$5:$H$11, which is 91.
Related functions
The Excel INDEX function returns the displayed value based on a given position from a range or an array.
The Excel MATCH function searches for a specific value in a range of cells, and returns the relative position of the value.
Related Formulas
Exact match with INDEX and MATCH
If you need to find out the information listed in Excel about a specific product, movie or a person, etc., you should make a good use of the combination of INDEX and MATCH functions.
Approximate match with INDEX and MATCH
There are times when we need to find approximate matches in Excel to evaluate employees' performance, grade students’ scores, calculate postage based on weight, etc. In this tutorial, we will talk about how to use the INDEX and MATCH functions to retrieve the results we need.
Lookup closest match value with multiple criteria
In some cases, you may need to lookup the closest or approximate match value based on more than one criteria. With the combination of INDEX, MATCH and IF functions, you can quickly get it done in Excel.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
