Excel VLOOKUP Compare 2 Columns and Find Matches in 4 Easy Steps (2024)

In this Excel VLOOKUP to Compare 2 Columns and Find Matches Tutorial, you learn how to:

• Use the VLOOKUP function;
• To:
  • Compare 2 columns; and
  • Find matches.

This Excel VLOOKUP to Compare 2 Columns and Find Matches Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.

This Excel VLOOKUP to Compare 2 Columns and Find Matches Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

• Excel VLOOKUP Tutorial (under development): Click here to open.
• Excel VLOOKUP from Another Sheet in 4 Easy Steps: Click here to open.
• Excel VLookup Sum Multiple Row Values (in Same Column) in 4 Easy Steps: Click here to open.
• Excel VLOOKUP Multiple Columns in 5 Easy Steps: Click here to open.
• Excel VLOOKUP Sum Multiple Columns (Values) in 6 Easy Steps: Click here to open.
• Excel VLookup Sum Multiple Column Values (with XLOOKUP) in 4 Easy Steps: Click here to open.
• Excel VLookup Sum Multiple Rows and Columns in 3 Easy Steps: Click here to open.
• Excel VLookup Multiple Criteria with INDEX MATCH in 4 Easy Steps: Click here to open.
• Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps: Click here to open.
• Excel VLookup Multiple Criteria with the FILTER Function in 2 Easy Steps: Click here to open.
• Excel VLOOKUP Return Multiple Values with Helper Column in 4 Easy Steps: Click here to open.
• Excel VLookup Return Multiple Values with the INDEX Function in 7 Easy Steps: Click here to open.
• Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps: Click here to open.
• Excel VLookup Return Multiple Values in One Cell Separated by a Comma in 4 Easy Steps: Click here to open.
• Excel VLOOKUP Multiple Sheets in 10 Easy Steps: Click here to open.
• Excel VLOOKUP Multiple Sheets in Different Workbook in 10 Easy Steps: Click here to open.
• Excel VLOOKUP Sheet in Multiple Different Workbooks in 10 Easy Steps: Click here to open.

My Excel XLOOKUP Tutorial (click here to open) may help you:

• Better understand and implement the contents below.
• Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

• Excel Macro Tutorial for Beginners: Click here to open.
• Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
• 350 + Excel Keyboard Shortcuts And Hotkeys: Click here to open.

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link). The following are some of the topics covered in these Excel Courses:

• Excel essentials and must-know skills.
• Advanced Excel Formulas.
• Excel Tables.
• Pivot Tables.
• Dashboards.
• Power Pivot.
• Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

• Premium Courses at the Power Spreadsheets Academy: Click here to open.
• Books at the Power Spreadsheets Library: Click here to open.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

The VLOOKUP Compare 2 Columns and Find Matches Formula Template/Structure

The following is the VLOOKUP compare 2 columns and find matches formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/'More information: https://powerspreadsheets.com/vlookup-compare-two-columns/=IF(ISNA(VLOOKUP(ValueInColumn1,Column2,1,FALSE)),ValueIfMatchNotFound,ValueIfMatchFound)

The Example Before VLOOKUP Compare 2 Columns and Find Matches

This Excel VLOOKUP to Compare 2 Columns and Find Matches Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.

The example worksheet has 2 separate columns (A and C) with the following characteristics:

• 1 header (row 7).
• 20 entries (rows 8 to 27).

Step 1: (If Necessary) Add Columns for Match (or No Match) Output

If necessary (depending on your worksheet layout), add 1 or 2 columns to hold the VLOOKUP compare 2 columns and find matches formulas you create. As a general rule:

• Add (only) 1 column if you want to (only) display whether the values in 1 column are found in the other column, but not vice-versa. For example:
  • You want to display whether the values in Column1 are found in Column2;
  • You don't want to display whether the values in Column2 are found in Column1.
• Add 2 columns if you want to display whether the values in each column are found in the other column. In other words, you want to display whether:
  • The values in Column1 are found in Column2; and
  • The values in Column2 are found in Column1.

If necessary, you can insert columns (for example) as follows:

1. Select the cells in the column before which you want to insert columns. Inserted columns are inserted before (to the left) of the selected cells.
2. Do the following in the Home tab of the Ribbon.
   1. Expand the Insert dropdown.
   2. Select Insert Cells. Excel displays the Insert dialog box.
3. Do the following in the Insert dialog box.
   1. Select the Shift cells right option button.
   2. Click the OK button.

Step 1 Example

I add a column to the right of column A as follows:

1. Select cells B6 to B27.
2. In the Excel Ribbon, go to:
   1. Home.
   2. Insert.
   3. Insert Cells.
3. Click the OK button in the Insert dialog box.
4. Enter a header (Match Found in Column 2?) in the header row of the newly added column (cell B7).

I'll display whether the values in each column are found in the other column. In other words, I'll display whether:

• The values in Column1 are found in Column2; and
• The values in Column2 are found in Column1.

For purposes of this Tutorial, I (also):

• Add the following columns:
  • “Formula” to the right of the newly added “Match Found in Column 2?” column.
    • This column will display the VLOOKUP compare 2 columns and find matches example formulas stored in the “Match Found in Column 2?” column.
  • “Match Found in Column 1?” to the right of the second (Column 2) “Salesperson” column.
  • “Formula” to the right of the newly added “Match Found in Column 1?” column.
    • This column will display the VLOOKUP compare 2 columns and find matches example formulas stored in the “Match Found in Column 1?” column.
• Do some additional formatting and clean-up.

Step 2: Use the VLOOKUP Function to Compare the 2 Columns and Find Matches

In the VLOOKUP compare 2 columns and find matches formula, the VLOOKUP function does the following:

1. Searches for a value (stored in one of the 2 columns you compare) in the (other) column (you use for comparison purposes).
2. Returns one of the following:
   • If the searched value is found in the column (you use for comparison purposes): The searched value.
   • If the searched value isn't found in the column (you use for comparison purposes): The #N/A error.

Specify VLOOKUP's arguments as follows to compare the 2 columns and find matches:

• Lookup_value: The value (stored in one of the 2 columns you compare) you search for in the (other) column (you use for comparison purposes).
• Table_array: The column (a cell range with 1 column and several rows) you use for comparison purposes.
• Col_index_num: 1.
• Range_lookup: FALSE.

'Source: https://powerspreadsheets.com/'More information: https://powerspreadsheets.com/vlookup-compare-two-columns/VLOOKUP(ValueInColumn1,Column2,1,FALSE)

Step 2 Example

I enter the VLOOKUP compare 2 columns and find matches example formulas in 2 columns:

• Column B (cells B8 to B27); and
• Column F (cells F8 to F27).

The first cells I enter the VLOOKUP compare 2 columns and find matches example formulas are cells:

• B8; and
• F8.

I specify the arguments of the VLOOKUP function (for these cells) as follows:

• Cell B8:
  • Lookup_value:
    • A8.
    • This is the value stored in Column 1 I search for in Column 2.
  • Table_array (with absolute references):
    • $E$8:$E$27.
    • This is Column 2.
  • Col_index_num: 1.
  • Range_lookup: FALSE.
• Cell F8:
  • Lookup_value:
    • E8.
    • This is the value stored in Column 2 I search for in Column 1.
  • Table_array (with absolute references):
    • $A$8:$A$27.
    • This is Column 1.
  • Col_index_num: 1.
  • Range_lookup: FALSE.

'Source: https://powerspreadsheets.com/'More information: https://powerspreadsheets.com/vlookup-compare-two-columns/'Cell B8VLOOKUP(A8,$E$8:$E$27,1,FALSE)'Cell F8VLOOKUP(E8,$A$8:$A$27,1,FALSE)

I:

1. Copy the (partial) VLOOKUP compare 2 columns and find matches example formulas in cells B8 and F8.
2. Paste formulas in all (other) cells in the applicable columns (cells B9 to B27, and F9 to F27).

Step 3: Check Whether the VLOOKUP Function Returns the #N/A Error

The VLOOKUP function returns the #N/A error if no match (for the value you search for) is found (in the column you use for comparison purposes).

Use the ISNA function to identify the entries where this (VLOOKUP finds no match) occurs. The ISNA function:

1. Tests whether a value is #N/A; and
2. Returns TRUE or FALSE, as follows:
   1. TRUE: The value is #N/A.
   2. FALSE: The value isn't #N/A.

Set the Value argument of the ISNA function (in the VLOOKUP compare 2 columns and find matches formula) to the VLOOKUP function you created in step #2.

'Source: https://powerspreadsheets.com/'More information: https://powerspreadsheets.com/vlookup-compare-two-columns/ISNA(VLOOKUP(ValueInColumn1,Column2,1,FALSE))

Step 3 Example

Considering the VLOOKUP function I created in step #2:

'Source: https://powerspreadsheets.com/'More information: https://powerspreadsheets.com/vlookup-compare-two-columns/'Cell B8ISNA(VLOOKUP(A8,$E$8:$E$27,1,FALSE))'Cell F8ISNA(VLOOKUP(E8,$A$8:$A$27,1,FALSE))

I:

1. Copy the (partial) VLOOKUP compare 2 columns and find matches example formulas in cells B8 and F8.
2. Paste formulas in all (other) cells in the applicable columns (cells B9 to B27, and F9 to F27).

Step 4: Specify the Value to Return Depending on Whether a Match is Found (or Not) in the Applicable Column

Use the IF function to do the following:

1. Test whether the ISNA function (you created in step #3) returns:
   • TRUE: The VLOOKUP function didn't find a match (in the column you use for comparison purposes) for the value you search; or
   • FALSE: The VLOOKUP function found a match (in the column you use for comparison purposes) for the value you search.
2. Return one value (or another) depending on the Boolean value (TRUE or FALSE) returned by the ISNA function (you created in step #3).

Specify the arguments of the IF function as follows when creating a VLOOKUP compare 2 columns and find matches formula:

• Logical_test: The ISNA function you created in step#3.
• Value_if_true: The value to return if:
  • The VLOOKUP function (you created in step #2) doesn't find the value (stored in one of the 2 columns you compare) in the (other) column (you use for comparison purposes); and
  • The ISNA function (you created in step #3) returns TRUE.
• Value_if_false: The value to return if:
  • The VLOOKUP function (you created in step #2) finds the value (stored in one of the 2 columns you compare) in the (other) column (you use for comparison purposes); and
  • The ISNA function (you created in step #3) returns FALSE.

'Source: https://powerspreadsheets.com/'More information: https://powerspreadsheets.com/vlookup-compare-two-columns/=IF(ISNA(VLOOKUP(ValueInColumn1,Column2,1,FALSE)),ValueIfMatchNotFound,ValueIfMatchFound)

Step 4 Example

I specify the arguments of the IF function as follows:

• Logical_test: The ISNA function I created in step #3.
• Value_if_true: The string “Match not found” (“Match not found”).
• Value_if_false: The string “Match found” (“Match found”).

'Source: https://powerspreadsheets.com/'More information: https://powerspreadsheets.com/vlookup-compare-two-columns/'Cell B8=IF(ISNA(VLOOKUP(A8,$E$8:$E$27,1,FALSE)),"Match not found","Match found")'Cell F8=IF(ISNA(VLOOKUP(E8,$A$8:$A$27,1,FALSE)),"Match not found","Match found")

I:

1. Copy the VLOOKUP compare 2 columns and find matches example formulas in cells B8 and F8.
2. Paste formulas in all (other) cells in the applicable columns (cells B9 to B27, and F9 to F27).

Download the VLOOKUP Compare 2 Columns and Find Matches Example Workbook

This Excel VLOOKUP to Compare 2 Columns and Find Matches Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.

This Excel VLOOKUP to Compare 2 Columns and Find Matches Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

• Excel VLOOKUP Tutorial (under development): Click here to open.
• Excel VLOOKUP from Another Sheet in 4 Easy Steps: Click here to open.
• Excel VLookup Sum Multiple Row Values (in Same Column) in 4 Easy Steps: Click here to open.
• Excel VLOOKUP Multiple Columns in 5 Easy Steps: Click here to open.
• Excel VLOOKUP Sum Multiple Columns (Values) in 6 Easy Steps: Click here to open.
• Excel VLookup Sum Multiple Column Values (with XLOOKUP) in 4 Easy Steps: Click here to open.
• Excel VLookup Sum Multiple Rows and Columns in 3 Easy Steps: Click here to open.
• Excel VLookup Multiple Criteria with INDEX MATCH in 4 Easy Steps: Click here to open.
• Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps: Click here to open.
• Excel VLookup Multiple Criteria with the FILTER Function in 2 Easy Steps: Click here to open.
• Excel VLOOKUP Return Multiple Values with Helper Column in 4 Easy Steps: Click here to open.
• Excel VLookup Return Multiple Values with the INDEX Function in 7 Easy Steps: Click here to open.
• Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps: Click here to open.
• Excel VLookup Return Multiple Values in One Cell Separated by a Comma in 4 Easy Steps: Click here to open.
• Excel VLOOKUP Multiple Sheets in 10 Easy Steps: Click here to open.
• Excel VLOOKUP Multiple Sheets in Different Workbook in 10 Easy Steps: Click here to open.
• Excel VLOOKUP Sheet in Multiple Different Workbooks in 10 Easy Steps: Click here to open.

My Excel XLOOKUP Tutorial (click here to open) may help you:

• Better understand and implement the contents above.
• Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

• Excel Macro Tutorial for Beginners: Click here to open.
• Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
• 350 + Excel Keyboard Shortcuts And Hotkeys: Click here to open.

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link). The following are some of the topics covered in these Excel Courses:

• Excel essentials and must-know skills.
• Advanced Excel Formulas.
• Excel Tables.
• Pivot Tables.
• Dashboards.
• Power Pivot.
• Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

• Premium Courses at the Power Spreadsheets Academy: Click here to open.
• Books at the Power Spreadsheets Library: Click here to open.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).