Often, we have to work with multiple columns in **Excel **to search for the common data or to find out the missing values. On the other hand, the **VLOOKUP** function in **Excel **helps us to look up a value in a range, table, etc. In this article, we will show you the effective methods to **Compare Multiple Columns **using **VLOOKUP** in **Excel**.

To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents the **Salesman** and **Product **of a company.

**Table of Contents**hide

## Download Practice Workbook

Download the following workbook to practice by yourself.

## Introduction to Excel VLOOKUP Function

**Syntax**

**VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])**

**Arguments**

**lookup_value: **The value to look for in the leftmost column of the given table.

**table_array:** The table in which it looks for the **lookup_value** in the leftmost column.

**col_index_num:** The number of the column in the table from which a value is to be returned.

**[range_lookup]: **Tells whether an exact or partial match of the **lookup_value** is required. **0** for an exact match, **1** for a partial match. Default is **1** (**partial match**). This is optional.

## 5 Methods to Compare Multiple Columns Using VLOOKUP in Excel

### 1. Compare Multiple Columns in Excel and Return Matching Data Using VLOOKUP

The **VLOOKUP** function can compare different columns in **Excel **and bring out the common data. In our first method, we’ll use this function to compare columns **B **and **E **and then fill up the missing products in column **F**. Therefore, follow the steps below to perform the task.

**STEPS:**

- First, select cell
**F5**. Here, type the formula:

`=VLOOKUP(E5,$B$5:$C$10,2,0)`

- Then, press
**Enter**.

- After that, use the
**AutoFill**tool to complete the series. - Finally, you’ll get the missing products in column
**F**.

Here, it looks for the **E5 **cell value in the range **B5:C10**. Then, it brings out the value present in the second column i.e. column **C**.

**Read More:** **How to Compare Three Columns in Excel Using VLOOKUP**

### 2. Excel VLOOKUP, IFNA & FILTER Functions to Compare Multiple Columns and Return Common Values

The **IFNA** function in **Excel **returns the outcome of an expression, and it returns the specified value we input if the expression results in **#N/A**. The **FILTER** function filters a certain range. In the following dataset, we’ll use the **VLOOKUP**, **IFNA** & **FILTER** functions to **Compare Multiple Columns **and to **Return Common Values**. So, learn the steps given below.

**STEPS:**

- Firstly, select cell
**F5**and type the formula:

`=FILTER(B5:B10, IFNA(VLOOKUP(B5:B10, D5:D10, 1, FALSE), "")<>"")`

- Then, press
**Enter**and it’ll return the common values present in columns**B**and**D**.

🔎 **How Does the Formula Work?**

**➤ VLOOKUP(B5:B10, D5:D10, 1, FALSE)**

Here, it looks for **B5:B10** cell values in the range** D5:D10**. The column number is **1 **as there is only a single column.

**➤ IFNA(VLOOKUP(B5:B10, D5:D10, 1, FALSE), “”)**

Then, this part returns the result of the expression **VLOOKUP(B5:B10, D5:D10, 1, FALSE)**. For** #N/A** results, it returns a blank cell.

**➤ FILTER(B5:B10, IFNA(VLOOKUP(B5:B10, D5:D10, 1, FALSE), “”)<>””)**

Finally, the **FILTER** function sifts out the blank cells.

**Read More:** **Compare Three Columns in Excel and Return a Value(4 Ways)**

### 3. Compare Multiple Columns with VLOOKUP & IFERROR Functions in Excel

We can also use the **VLOOKUP** function for comparing more than just two columns. In the below dataset, we’ll compare columns **B **and **D **first. Then we’ll compare the outputs with column **F**. Thus, we’ll get our desired outcome which is **Group A Qualified Salesman** with **Age **under **40**. Hence, follow the process given below to carry out the operation.

**STEPS:**

- Select cell
**H5**at first. - Then, type the formula:

`=IFERROR(VLOOKUP(IFERROR(VLOOKUP(B5:B10,D5:D10,1,FALSE),""),F5:F10,1,FALSE),"")`

- After that, press
**Enter**.

- At last, it’ll return the desired outcome.

🔎 **How Does the Formula Work?**

**➤ VLOOKUP(B5:B10,D5:D10,1,FALSE)**

Firstly, it looks for **B5:B10** cell values in the **D5:D10** range and returns the common values.

**➤ IFERROR(VLOOKUP(B5:B10,D5:D10,1,FALSE),””)**

Subsequently, this part returns the result of **VLOOKUP(B5:B10,D5:D10,1,FALSE)**, and blank cells if there is an error.

**➤ VLOOKUP(IFERROR(VLOOKUP(B5:B10,D5:D10,1,FALSE),””),F5:F10,1,FALSE)**

Again, it looks for the result of **IFERROR(VLOOKUP(B5:B10,D5:D10,1,FALSE),””) **in the **F5:F10 **range and returns the matching values.

**➤ IFERROR(VLOOKUP(IFERROR(VLOOKUP(B5:B10,D5:D10,1,FALSE),””),F5:F10,1,FALSE),””)**

This formula returns the final result. But for an error, it will return a blank cell.

**Related Content:** **VLOOKUP Formula to Compare Two Columns in Different Sheets!**

**Similar Readings:**

**Excel Compare Text in Two Columns (7 Fruitful Ways)****Excel Compare Two Lists and Return Differences (7 Ways)****Match Two Columns in Excel and Return a Third (3 Ways)****How to Compare Two Columns or Lists in Excel****Excel Compare Two Cells Text (9 Examples)**

### 4. Apply VLOOKUP, ISNA & IF Functions in Excel for Comparing Multiple Columns and Pull Missing Values

Additionally, we can pull out the missing values after comparing multiple columns. The **ISNA** function returns **TRUE **for **#N/A** and **FALSE **otherwise. The **IF** function tests a logical operation and returns a value if the condition satisfies. But it will return another value if the condition doesn’t satisfy. Here, in the below dataset, we’ll use the **VLOOKUP**, **ISNA** & **IF** functions for **Comparing Multiple Columns **and **Pulling **the **Missing Values** i.e. the values in column **B **which are absent in column **D**.

**STEPS:**

- In the beginning, select cell
**F5**and type the formula:

`=IF(ISNA(VLOOKUP(B5, $D$5:$D$10, 1, FALSE)), B5, "")`

- Then, press
**Enter**and use the**AutoFill**tool to fill the series. - In the end, it’ll return the desired output.

🔎 **How Does the Formula Work?**

**➤ VLOOKUP(B5, $D$5:$D$10, 1, FALSE)**

It looks for the **B5 **cell value in range** D5:D10** and returns the common data.

**➤ ISNA(VLOOKUP(B5, $D$5:$D$10, 1, FALSE))**

Afterward, this part returns **FALSE **for the results of **VLOOKUP(B5, $D$5:$D$10, 1, FALSE)** and **TRUE **for **#N/A**.

**➤ IF(ISNA(VLOOKUP(B5, $D$5:$D$10, 1, FALSE)), B5, “”)**

Lastly, it returns the cell values of column **B **that are not present in column** D**. Otherwise it returns a blank cell for the matched values.

**Read More:** **How to Compare Two Columns in Excel for Missing Values (4 ways)**

### 5. Use VLOOKUP to Compare Multiple Columns in Different Excel Sheets

So far we’ve discussed comparing the columns present in a single sheet. In the last method, we’ll show how to **Compare Multiple Columns **present in **Different Excel Sheets**. Therefore, learn the below process to perform the task.

**STEPS:**

- Firstly, select cell
**D5**of**Sheet1**. - Type the formula:

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

- Then, press
**Enter**.

- Subsequently, use the
**AutoFill**tool to complete the series. - As a result, you’ll get the outcome.

🔎 **How Does the Formula Work?**

**➤ VLOOKUP(B5, Sheet2!$B$5:$B$10, 1, FALSE)**

This part of the formula looks for the **B5 **cell value of **Sheet1 **in **B5:B10** of **Sheet2**. Then, it returns the matching data.

**➤ IFNA(VLOOKUP(B5, Sheet2!$B$5:$B$10, 1, FALSE), “”)**

At last, it returns the result of **VLOOKUP(B5, Sheet2!$B$5:$B$10, 1, FALSE) **and otherwise, a blank cell.

**Related Content:** **Macro to Compare Two Columns in Excel and Highlight Differences**

## Conclusion

Now you will be able to **Compare Multiple Columns **using **VLOOKUP **in **Excel** with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.