Note: The other languages of the website are Google-translated. Back to English

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.

merge tables with index and match 1

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).

merge tables with index and match 2

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.

merge tables with index and match 3

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

Excel INDEX function

The Excel INDEX function returns the displayed value based on a given position from a range or an array.

Excel MATCH function

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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL