How to Merge Two Tables in Excel Using VLOOKUP

In day-to-day usage, users encounter different Excel Tables containing inter-reliant data. As a result, they need to merge two tables in Excel using VLOOKUP. The Excel VLOOKUP function is very common among users, and it’s quite popular to fetch lookup values.

Let’s say we have two separate Excel tables holding different parts of the data. Therefore, we want to merge these two tables into one.

How to Merge Two Tables in Excel Using VLOOKUP How to Merge Two Tables in Excel Using VLOOKUP

This article discusses the VLOOKUP function and its usage to merge two tables in Excel.

VLOOKUP Function and Its Arguments

The VLOOKUP function looks for values within an assigned range or table, organized vertically. It also offers Approximate and Exact Matching for assigned values. And keep in mind that the lookup values must reside in the first column within the assigned ranges or tables. The syntax of the VLOOKUP function is

=VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])

VLOOKUP Function

VLOOKUP Arguments

  • lookup_value – the value you look for in the first column of an assigned range or table.
  • table_array – the range or table from which you fetch the lookup_value.
  • column_index_num – the column number within the range or table from which the value gets retrieved.
  • range_lookupApproximate Match = TRUE (Default), Exact Match = FALSE [Optional]

How to Merge Two Tables in Excel Using VLOOKUP: 2 Suitable Examples

There are two instances of table merging: Equivalent Tables and Non-equivalent Tables. Equivalent Tables are those that hold a minimum of one column of identical entries with no repetitions. And if the identical column entries are repetitive, then it’s referred to as Non-equivalent Tables. Here, we demonstrate how to merge two tables using VLOOKUP in both instances.

Follow the latter section to learn the details.

Example 1: Fetching Data to Merge Two Equivalent Tables Using VLOOKUP in Excel

As we mentioned earlier, the Equivalent Tables have identical data, but both tables contain different parts. In those cases, the VLOOKUP function fetches the missing data, thus merging those two tables.

Equivalent Table

Step 1: Type the following formula in any adjacent cells of the much larger Table.

=VLOOKUP([@[Employee ID]],Salary7[#All],COLUMN()-3,FALSE)

Step 2: Hit ENTER to display the outcomes. As the data is preformatted as a table, it incorporates all the data using an added column to the table.

🔺 Furnish the outcomes, and the table may look like the picture below.

Merge Equivalent Table Using VLOOKUP

Read More: How to Merge Two Tables in Excel

Example 2: Merging Two Non-Equivalent Tables Using VLOOKUP in Excel

Now, if we consider Non-equivalent Tables, the VLOOKUP function works just fine. For this case, we use different data to showcase a Non-equivalent Table example.

Merge Two Tables in Excel Using VLOOKUP

Step 1: Similar to Example 1, paste the following formula into any adjacent cells.

=VLOOKUP([@[Order ID]],SalesData9[#All],COLUMN()-3,FALSE)


Step 2: Use the ENTER key to merge the table instantly.

🔺 Modify the table according to your taste.

VLOOKUP Formula Explanation

In this article, we use two identical VLOOKUP formulas. Let’s break down one of them to understand how Excel delivers the results.

=VLOOKUP([@[Employee ID]],Salary7[#All],COLUMN()-3,FALSE)

  • [@[Employee ID]] = lookup_value.
  • Salary7[#All] = table_array.
  • COLUMN()-3 = column_index_num. The COLUMN function returns the column number of the cell where the formula is entered (i.e., Column E = 5). Then 3 is deducted to fetch the other table’s (5-3) or 2 column data.
  • FALSE = [range_lookup].

Read More: How to Merge Two Tables Based on One Column in Excel

Download Excel Workbook


This article demonstrates two instances of merging two tables in Excel using VLOOKUP. No matter what kind of data users use in their tables, VLOOKUP just merges them fine. We hope these examples provide substantial ground to comprehend the overall VLOOKUP usage. Comment if you have further inquiries or have anything to add.

<< Go Back to Merge Tables in Excel | Merge in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF