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).
√ Note: Since we have filled in the information from table 1, now we only need to retrieve corresponding data from table 2.
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)
Cell G16 (Rank)
√ 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:
- 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.