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.
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 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_lookup – Approximate 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.
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.
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.
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
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.
<< Go Back to Merge Tables in Excel | Merge in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
