Skip to main content

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


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

merge tables with index and match 3

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.

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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations