How to Compare Multiple Columns Using VLOOKUP in Excel (5 Methods)

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.

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:

• 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:

• 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:

• 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:

• 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:

• 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.

Related Articles

• How to Match Multiple Columns in Excel (Easiest 5 ways)
• Compare 3 Columns for Matches in Excel (4 Methods)
• How to Compare 4 Columns in Excel VLOOKUP (Easiest 7 Ways)
• How to Compare Two Columns and Return Common Values in Excel
• Compare Two Columns in Excel and Highlight the Greater Value (4 Ways)