VLOOKUP to Compare Two Columns on Different Sheets

Yes. In Excel for Microsoft 365, use an exact-match VLOOKUP to check whether each value in one sheet’s column appears in a column on another sheet. If the values to check are in Sheet1!A2:A100 and the list to search is Sheet2!A2:A100, enter this in Sheet1!B2 and fill down: =IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$100,1,FALSE)),"Only on Sheet1","Found on Sheet2").

What comparing columns with VLOOKUP means

VLOOKUP searches for a value in the leftmost column of a selected range and can return information from another column in that range. When the selected range is just the comparison column and the return-column index is 1, the result tells you whether the searched value exists there. The formula above turns that result into a readable status: “Found on Sheet2” or “Only on Sheet1.”

This is a membership check, not a comparison of values in the same row number. For example, if Sheet1 contains product codes in a different order from Sheet2, the formula still finds a code wherever it appears in the lookup list. That distinction matters: matching Sheet1!A2 to Sheet2!A2 would compare two particular cells, while VLOOKUP checks A2 against the entire specified range.

A one-way check answers, “Which values in the first list are absent from the second?” It does not by itself identify values that appear only in the second list. To compare both lists completely, run the check in both directions.

Set up the sheets and ranges

Use a consistent identifier in each column, such as an employee ID, invoice number, product code, or email address. The two lists can be on separate sheets in the same workbook, and their rows do not need to line up. In this example, Sheet1 holds the list being checked in column A; Sheet2 holds the reference list in column A.

Open the workbook and confirm which columns contain the values to compare.
On Sheet1, choose an empty column next to the first value, such as B2.
Enter the formula with the lookup range on Sheet2 and use absolute references for that range.
Fill the formula down alongside all the entries on Sheet1.
Filter the status column for “Only on Sheet1” to review unmatched values.

The dollar signs in $A$2:$A$100 keep the lookup range fixed as the formula is copied down. The relative reference A2 changes to A3, A4, and so on. Adjust row 100 to cover the actual list; a range that stops too soon can incorrectly mark later values as missing.

Why the formula uses FALSE

The final VLOOKUP argument, FALSE, requests an exact match. This is important for codes and names: an approximate match is not a safe way to test whether a specific value exists. VLOOKUP’s syntax in this example is VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). Here, A2 is the value being searched, Sheet2’s A2:A100 is the search range, 1 says to return the first column of that range, and FALSE requires an exact match.

When a match exists, VLOOKUP returns the matched value. When it does not, VLOOKUP produces an #N/A error. The outer ISNA function detects that missing-value error, and IF displays the appropriate status. This is why the formula reports existence rather than returning a useful additional field.

The search range must have the searched values in its leftmost column. If the keys are in Sheet2 column C, for example, include column C as the first column of the table array. VLOOKUP cannot search a column to the right of the first column in its range and then return a value to its left.

Compare the lists in both directions

To find values that appear only on Sheet2, add a status formula beside that sheet’s list and search Sheet1 instead. With the values in Sheet2!A2:A100 and Sheet1!A2:A100, enter the following in Sheet2!B2 and fill down:

=IF(ISNA(VLOOKUP(A2,Sheet1!$A$2:$A$100,1,FALSE)),"Only on Sheet2","Found on Sheet1")

Now each sheet has its own result column. Filter Sheet1’s status for “Only on Sheet1” and Sheet2’s status for “Only on Sheet2” to see the differences on both sides. If every value is found in both directions, the lists contain the same set of values, though their order and number of duplicate entries might still differ.

For a reliable comparison, make sure both lookup ranges include all populated rows. If the columns have headers, starting the ranges at row 2 avoids treating the header as a value. You can expand the row limits when more records are added, or use Excel Tables so ranges can adjust as their data grows.

Return related information instead of a status

Sometimes the goal is not simply to mark a match, but to bring a related value across sheets. For example, Sheet1 may contain product codes in A2:A100, while Sheet2 contains product codes in A2:A100 and descriptions in B2:B100. With the code to look up in Sheet1!A2, enter this in Sheet1!B2:

=IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$100,2,FALSE),"Not found")

This searches the first column of Sheet2’s A:B range and returns the second column, which is the description. IFERROR displays “Not found” if VLOOKUP returns an error. Use a different return-column number when the desired field is farther across the selected range; the number is counted from the range’s first column, not from the worksheet’s column A.

For a simple existence check, the first formula is clearer because it explicitly labels matches and misses. For a lookup that returns a description, amount, or other attribute, choose a range that includes both the key and the field to return. Keep the exact-match setting in place for identifiers.

Common reasons a value looks missing

A formula can report “Only on Sheet1” even when the two cells look alike. Often the underlying values differ. Check these common causes before concluding that the lists are genuinely different:

Extra spaces: A trailing space in one entry makes it different from the same text without that space. Spaces copied from websites or other systems can be hard to see.
Different data types: A number stored as text may not match the same digits stored as a number. A cell’s appearance alone may not reveal the distinction.
Different identifiers: Leading zeros, punctuation, hyphens, or inconsistent capitalization may matter. Decide whether those differences should count as distinct before cleaning the data.
Incomplete range: Confirm that the last row in the lookup range includes all reference values. Also check that the formula was filled down through the full first list.
Wrong search column: The lookup values must be in the first column of VLOOKUP’s selected range.

For text data, inspect suspicious entries directly and compare their lengths or cell formats. Cleaning a source list before lookup is often safer than changing the comparison formula, because a cleanup step makes the transformation visible and repeatable. Do not remove punctuation or leading zeros automatically if those characters are meaningful parts of an ID.

Duplicates and what the result does not prove

VLOOKUP returns the first matching entry it encounters. For an existence check, that is enough: one matching value means the searched value exists at least once. It does not tell you how many times the value occurs, whether both columns have the same duplicate counts, or which duplicate row is the intended one.

For example, Sheet1 could contain the code “P104” once and Sheet2 could contain it three times. The status formula reports a match in either case. If duplicate counts matter, treat that as a separate comparison and count occurrences for each key rather than relying on a yes-or-no lookup. Also decide whether repeated entries are valid records or data errors before interpreting a difference report.

Likewise, “Found on Sheet2” confirms presence, not that other details on the two sheets agree. If you need to compare a price, name, or date as well as an ID, retrieve or compare that field separately. Matching keys and matching records are related but different checks.

VLOOKUP alternatives in Microsoft 365

VLOOKUP is useful when the lookup key is at the left of the selected range and a broadly familiar formula is preferred. In Microsoft 365, XLOOKUP can express an existence check more directly and can search a lookup range without requiring a return-column index. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual Excel versions such as Excel 2019.

For a basic yes-or-no presence test, COUNTIF is another option: it counts occurrences in the other sheet’s column, so a result above zero means a match exists. VLOOKUP remains a good choice when the same process will also need to return a field from the matching row, or when users need a familiar lookup pattern. Choose based on the task and the Excel versions used by everyone who will open the workbook.

When the comparison needs a reusable report rather than formulas beside every row, Excel’s Power Query can also merge two tables using a key and show matching or unmatched records. That approach involves loading and refreshing queries, so it is more setup than a quick formula but can suit recurring data-cleaning work. For a one-time list check, the VLOOKUP status column is usually straightforward and easy to audit.

Frequently Asked Questions

Can VLOOKUP compare columns on different sheets?
Yes. Use a cell from the first sheet as the lookup value and specify the other sheet’s column as the lookup range. In the same workbook, a reference such as Sheet2!$A$2:$A$100 points to that sheet’s cells.

How do I find values in one sheet that are missing from another?
Run an exact-match lookup for each value in the first list against the second list, then label the #N/A results as missing. To find values missing in the opposite direction too, repeat the check with the sheets reversed.

Do the columns have to be in the same order?
No. VLOOKUP searches the entire specified lookup column, so the values can be in different row orders. The formula checks whether a value appears in the range, not whether the two cells in corresponding row numbers are equal.

Why does VLOOKUP return #N/A for a value I can see?
The entries may contain hidden spaces, differ in number-versus-text format, or include different characters such as a leading zero. Also verify that the range includes the visible entry and that the correct column is the first column in the VLOOKUP range.

Can VLOOKUP compare two columns in the same sheet?
Yes. The method is the same; replace the other-sheet reference with the range in the current sheet. VLOOKUP can compare lists anywhere in the workbook as long as the lookup range starts with the column containing the values to search.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.