If we need to compare two tables in **Excel**, we can use **the VLOOKUP function**. It’s an easy way to compare two tables and find missing values, common values or return a value from a third column. Are you having trouble comparing two tables using **the VLOOKUP function**? This article will help you to learn **how to compare two tables in excel using the VLOOKUP function**. Let’s get started!

**Table of Contents**hide

## Download Practice Workbook

You can download the** Excel workbook** from here.

## 6 Ideal Examples to Compare Two Tables in Excel Using VLOOKUP Function

In this article, we will show you **6** ideal examples to compare two tables in **Excel** using the **VLOOKUP** function. It will help you compare two dataset columns in different scenarios. We will learn how to compare two columns in the same worksheet and how to compare two columns from different worksheets also.

### 1. Compare Two Columns in Same Worksheet

Comparing two columns in the same worksheet is the most common case in our daily life. It can be done easily using the **VLOOKUP** function. In order to demonstrate this example, we have taken a dataset like the following figure where we have **4 Teams** from **Group A** and the **Qualified teams** from **Group A**. Now we will compare these two columns of tables in excel using the **VLOOKUP** function and display the common teams between the two columns in the column **Group A Selected Teams**.

In order to compare the two tables using the **VLOOKUP** function, follow the steps below.

**Steps:**

- First, select cell
**D5**and type the following formula:

`=IFNA(VLOOKUP(B5,$C$5:$C$6,1,FALSE),"")`

- Second, press
**Enter**and since**Netherlands**is a qualified team from**Group A**, you will see it in cell**D5**.

- Third, select the
**D5**cell and drag the**Fill Handle**to the entire column**Group A Selected Teams**. - As a result, you will see an output like the image below where the common teams between the two columns will be displayed in column
**Group A Selected Teams**.

**🔎**** How Does the Formula Work?**

**VLOOKUP(B5,$C$5:$C$6,1,FALSE):**Firstly, this part of the formula looks for**B5**in the range (**$C$5:$C$6**). Then, returns the value from column**B**. If the statement is**False**, it will display**#N/A**in the cell.**IFNA(VLOOKUP(B5,$C$5:$C$6,1,FALSE),””):**Finally,**the IFNA****function**finally removes the**#N/A**if the statement was**False**and gives the final output.

**Read More: How to Compare Two Pivot Tables in Excel (3 Suitable Examples)**

### 2. Compare Two Columns in Different Worksheets

In order to compare two columns of tables in different excel worksheets using the **VLOOKUP** function, we have taken a dataset like the following figure where we have **4 Teams** from **Group A** in **sheet 1**.

And we have the **Qualified teams** from **Group A** in **sheet 2**.

Now we will compare these two columns using the **VLOOKUP** function and display the common teams between the two columns in the column **Group A Selected Teams** in **sheet 1**. In order to compare two columns from different sheets using the **VLOOKUP** function, follow the steps below:

**Steps:**

- Firstly, select cell
**C5**and type the following formula:

`=IFNA(VLOOKUP(B5,Sheet2!$B$5:$B$8,1,FALSE),"")`

- Secondly, press
**Enter**and since**Netherlands**is a qualified team from**Group A**, you will see it in cell**C5**.

- Thirdly, select the
**C5**cell and drag the**Fill Handle**to the entire column**Group A Selected Teams**. - Finally, you will see an output like the image below where the common teams between the two columns will be displayed in column
**Group A Selected Teams**.

**🔎**** How Does the Formula Work?**

**VLOOKUP(B5,Sheet2!$B$5:$B$8,1,FALSE):**Firstly, this part of the formula looks for**B5**in the range (**Sheet2!$B$5:$B$8**). Then, returns the value from column**B**. If the statement is**False**, it will display**#N/A**in the cell.**IFNA(VLOOKUP(B5,Sheet2!$B$5:$B$8,1,FALSE),””):**Finally, the**IFNA**function finally removes the**#N/A**if the statement was**False**and gives the final output.

### 3. Finding Common Values Between Two Columns

If we want to compare two columns of tables in the same excel worksheet and find the common values between them, it can be done easily using the **VLOOKUP** function. In order to solve this example, we have taken a dataset like the following figure where we have **8 Teams** from **Group A** and the **8 Qualified teams** in total. Now we will compare these two columns using the **VLOOKUP** function and display the teams qualified from **Group A**.

Follow the steps below in order to compare two columns and find missing values using the **VLOOKUP** function.

**Steps:**

- First of all, select cell
**D5**and type the following formula:

`=FILTER(B5:B12, IFNA(VLOOKUP(B5:B12, C5:C12, 1, FALSE), "")<>"")`

- Second, press
**Enter**and since it is an array formula, you will see all the selected teams from**Group A**under the column**Group A Selected Teams**like the image below.

**🔎**** How Does the Formula Work?**

**VLOOKUP(B5:B12, C5:C12, 1, FALSE):**Firstly, this part of the formula looks for (**B5:B12**) in the range (**C5:C12**).**IFNA(VLOOKUP(B5:B12, C5:C12, 1, FALSE):**Secondly, the**IFNA**function finds if the statement is**True**.**FILTER(B5:B12, IFNA(VLOOKUP(B5:B12, C5:C12, 1, FALSE), “”)<>””):**Finally,**the FILTER function**sorts all the**TRUE**statements and give the final output.

### 4. Finding Missing Values Between Two Columns

If we want to compare two columns and find the difference between them, it can be done easily using the **VLOOKUP** function. In order to solve this example, we have taken the same dataset as the following figure where we have **8 Teams** from **Group A** and **4 Qualified teams** from **Group A**. Now we will compare these two columns of tables using the Excel **VLOOKUP** function and display the **Not Qualified teams** from **Group A**.

Follow the steps below in order to compare two columns and find missing values using the **VLOOKUP** function.

**Steps:**

- To begin with, select cell
**D5**and type the following formula:

`=FILTER(B5:B12,ISNA(VLOOKUP(B5:B12,C5:C8,1,FALSE)))`

- Lastly, press
**Enter**and since it is an array formula, you will see all the**Not Qualified teams**from**Group A**under the column**Not Qualified**like the image below.

**🔎**** How Does the Formula Work?**

**VLOOKUP(B5:B12,C5:C8,1,FALSE):**Firstly, this part of the formula looks for**B5:B12**in the range (**C5:C8**).**ISNA(VLOOKUP(B5:B12,C5:C8,1,FALSE)):**Secondly,**the ISNA function**finds all the statements which are**False**.**FILTER(B5:B12,ISNA(VLOOKUP(B5:B12,C5:C8,1,FALSE))):**Finally, the**FILTER**function function sorts all the**FALSE**statements and give the final output.

**Read More: Compare Two Tables and Highlight Differences in Excel (4 Methods)**

### 5. Identifying Matches and Differences Between Two Columns

Now to identify matches and differences between **2** columns using the **VLOOKUP** function, we have taken a dataset like the following figure where we have **8 Teams** from **Group A** and **4 Qualified teams** from **Group A** in another column. Now we will compare these two columns using the **VLOOKUP** function and find matches and differences. After finding the matches and differences, we will insert the status either **Qualified** or **Not Qualified** in the **Status** column.

In order to compare two columns using the **VLOOKUP** function, follow the steps below:

**Steps:**

- Firstly, select cell
**C5**and type the following formula:

`=IF(ISNA(VLOOKUP(B5,$E$5:$E$8,1,FALSE)),"Not Qualified","Qualified")`

- Secondly, press
**Enter**and since**Ecuador**is not a qualified team from**Group A**, you will see**Not Qualified**status for this team.

- Thirdly, select the
**C5**cell and drag the**Fill Handle**to the entire column**Status**. - Finally, you will see an output like the image below where the qualified teams will have the status
**Qualified**and the not qualified teams will have the status**Not Qualified**.

**🔎**** How Does the Formula Work?**

**VLOOKUP(B5,$E$5:$E$8,1,FALSE):**Firstly, this part of the formula looks for**B5**in the range (**$E$5:$E$8**).**ISNA(VLOOKUP(B5,$E$5:$E$8,1,FALSE)):**Secondly,**the ISNA function**finds all the statements which are**False**.**IF(ISNA(VLOOKUP(B5,$E$5:$E$8,1,FALSE)),”Not Qualified”,”Qualified”):**Finally,**the IF function**gives the output**Qualified**if it is a match and gives output**Not Qualified**if it does not match.

**Read More: How to Compare Two Tables for Differences with Excel VBA (3 Methods)**

### 6. Compare Two Columns and Return a Value from 3rd Column

Sometimes we have to return a value from the third column in **Excel**. In order to compare two columns and return a value from **3rd** column using the** VLOOKUP** function, we have taken a dataset like the following figure where we have **8 Teams** from **Group A** and **4 Qualified teams** from **Group A** and their **Next Game date** in columns **E** and **F**. Now we will compare these two columns using the **VLOOKUP** function and return the **Next Game date** for the qualified teams in column **C**.

To compare two columns using the **VLOOKUP** function and return a value from **3rd** column, follow the steps below:

**Steps:**

- First, select cell
**C5**and type the following formula:

`=IFNA(VLOOKUP(B5,$E$5:$F$8,2,FALSE),"")`

- Second, press
**Enter**and since**Ecuador**is not a qualified team from**Group A**, you will see a blank space for this team.

- Third, select the
**C5**cell and drag the**Fill Handle**to the entire column**Next Game**. - Finally, you will see an output like the image below where the qualified teams will have the
**Next Game**date and the not qualified teams will have**blank spaces**in the column**Next Game**.

**🔎**** How Does the Formula Work?**

**VLOOKUP(B5,$E$5:$F$8,2,FALSE):**Firstly, this part of the formula looks for**B5**in the range (**$E$5:$F$8**).**IFNA(VLOOKUP(B5,$E$5:$F$8,2,FALSE),””):**Finally, the**IFNA**function finds if the statement is**True**and gives the output when the statement is**True**.

## Things to Remember

**The FILTER function**is only available in**Microsoft Office 365**. So if you have an older version of**Microsoft Office**, you won’t be able to use this function.- You don’t need to use
**Fill Handle**for the**FILTER function**. Since it is an array formula, it will automatically sort all the values and give you the output.

## Conclusion

Hence, follow the above-described steps. Thus, you can easily learn **how to compare two tables in Excel VLOOKUP**. Hope this will be helpful. Follow the **ExcelDemy** website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.

Dear Asaduzzaman,

Your post has been for a great help for me. Much appreciated

Keep it up

Hello,

Andre!Thanks for your appreciation. To get more helpful posts stay in touch with ExcelDemy.

Regards

ExcelDemy