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. **You can get this example workbook (for free) by clicking the button below**.

## Related Excel VLookup Tutorials

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

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to open.

## 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. **You can 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:

- 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.
- Do the following in the Home tab of the Ribbon.
- Expand the Insert dropdown.
- Select Insert Cells. Excel displays the Insert dialog box.

- Do the following in the Insert dialog box.
- Select the Shift cells right option button.
- Click the OK button.

### Step 1 Example

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

- Select cells B6 to B27.
- In the Excel Ribbon, go to:
- Home.
- Insert.
- Insert Cells.

- Click the OK button in the Insert dialog box.
- 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 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 formulas stored in the “Match Found in Column 1?” column.

- “Formula” to the right of the newly added “Match Found in Column 2?” 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:

- Searches for a value (stored in one of the 2 columns you compare) in the (other) column (you use for comparison purposes).
- 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 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 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.

- Lookup_value:
- 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.

- Lookup_value:

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

I:

- Copy the (partial) VLOOKUP compare 2 columns and find matches formulas in cells B8 and F8.
- 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:

- Tests whether a value is #N/A; and
- Returns TRUE or FALSE, as follows:
- TRUE: The value is #N/A.
- 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 B8
ISNA(VLOOKUP(A8,$E$8:$E$27,1,FALSE))
'Cell F8
ISNA(VLOOKUP(E8,$A$8:$A$27,1,FALSE))
```

I:

- Copy the (partial) VLOOKUP compare 2 columns and find matches formulas in cells B8 and F8.
- 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:

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

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

- Copy the VLOOKUP compare 2 columns and find matches formulas in cells B8 and F8.
- 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. **You can get this example workbook (for free) by clicking the button below**.

## Related Excel VLookup Tutorials

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

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to open.