Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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 VLOOKUPHow to Merge Two Tables in Excel Using VLOOKUP

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


Download Excel Workbook


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]


2 Suitable Examples to Merge Two Tables in Excel Using VLOOKUP

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 with Common Column (5 Ways)


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)

VLOOKUP Formula

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 (3 Ways)


Conclusion

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.

Do check out our awesome website, Exceldemy, to find interesting articles on Excel.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo