Using VLOOKUP to Compare Two Sheets in Excel Step by Step

To compare records on two Excel sheets, use VLOOKUP with an exact match and test whether it finds each key. If Sheet1 has IDs in A2:A100 and the other sheet, named Sheet 2, has IDs in A2:A100, enter this in Sheet1 B2 and fill it down: =IF(ISNA(VLOOKUP(A2,'Sheet 2'!$A$2:$A$100,1,FALSE)),"Missing from Sheet 2","Found"). It returns “Found” when the ID in A2 appears on Sheet 2, and “Missing from Sheet 2” when it does not.

VLOOKUP compares one lookup value at a time, so it is useful for checking lists of customer IDs, invoice numbers, product codes, or other shared keys. To compare a field such as an amount as well, look up the corresponding value and test whether it matches the value on the first sheet.

What comparing two sheets means

A spreadsheet comparison usually asks one of two questions: does every record in one list also exist in the other, or do the records have the same values in both places? Those are related but distinct checks. A presence check can identify IDs that are missing from a second sheet, but it cannot tell you whether the matching records have different prices, dates, or names.

VLOOKUP works by searching for a value in the leftmost column of a selected table range. When it finds a match, it can return a value from a column to the right. In a comparison, the shared identifier—such as an account number—serves as the lookup value. The returned field can then be compared with the corresponding field on the first sheet.

This method is a row-by-row comparison, not a complete audit of every cell in two workbooks. It assumes the identifier has the same meaning in both lists. If one sheet uses customer numbers and the other uses order numbers, for example, matching text alone does not establish that the records represent the same thing.

Prepare the sheets and choose a key

Before writing a formula, identify the column that uniquely identifies a record. A customer ID is usually more reliable than a customer name, because two people can share a name or one person’s name can be entered differently. Invoice numbers, SKU codes, and employee IDs are other common keys. If no single column is unique, you may need a combined key or a different comparison method.

Arrange the lookup sheet so the key is the first column in the selected range. VLOOKUP cannot search a column to the right of the return column and then return a value to its left. For example, if IDs are in column A and amounts are in column B on Sheet 2, the lookup range should begin at A and include B.

Give each sheet a clear name, and check that the key columns contain the same kind of data.
Remove unintended blank rows from the lookup range, or set a range wide enough to include all expected records.
Check whether IDs are unique. If a key appears more than once, VLOOKUP returns the first matching row, which may not be the record you intended.
Keep a copy of the source data before changing or cleaning values, especially when the sheets are business records.

For a sheet name containing spaces, put single quotation marks around the name in the formula, as in ‘Sheet 2’!$A$2:$A$100. Excel can also add the reference for you: type the formula up to the sheet reference, click the other sheet, select the range, and finish the formula.

Check whether each ID exists

The opening formula checks for an exact ID match in Sheet 2. Its arguments have specific jobs: A2 is the value to find; the range on Sheet 2 contains the search column; 1 tells VLOOKUP to return the first column of that range; and FALSE requests an exact match. ISNA detects the #N/A error that VLOOKUP produces when it cannot find the ID.

For example, assume Sheet1 A2 contains ID 104 and Sheet 2 includes 104 in its ID list. The formula returns “Found.” If the list does not contain 104, VLOOKUP returns #N/A internally and the formula displays “Missing from Sheet 2.” The formula does not change either source list; it creates a result in the cell where you enter it.

In Microsoft Excel for Microsoft 365 on Windows, enter the formula in the first result row, then use the fill handle—the small square at the bottom-right corner of the selected cell—to copy it down. You can also select the formula cell and the cells below it, then press Ctrl+D. On Mac, the fill handle works as well; the keyboard shortcut for Fill Down is Command+D. In Excel for the web, use the fill handle or copy and paste the formula down.

When you copy the formula, A2 should change to A3, A4, and so on. The dollar signs in $A$2:$A$100 keep the lookup range fixed. This combination of a changing lookup reference and a fixed table range is important: without the dollar signs, the search range can shift as the formula is filled down.

Compare a value as well as the ID

To check a field such as an amount, make the lookup range include both the ID and the value. Suppose Sheet1 has IDs in A2:A100 and amounts in B2:B100. Sheet 2 has IDs in A2:A100 and the corresponding amounts in B2:B100. Put this formula in Sheet1 C2, then fill it down:

=IFNA(IF(B2=VLOOKUP(A2,'Sheet 2'!$A$2:$B$100,2,FALSE),"Match","Different"),"ID not found")

Here, VLOOKUP searches the first column of the range on Sheet 2 and returns the second column, which is the amount. If ID 104 is in A2, B2 is 125, and the matching row on Sheet 2 has 125 in column B, the result is “Match.” If that row has 130, the result is “Different.” If ID 104 is absent, IFNA displays “ID not found.” The formula uses the exact underlying values, not just the rounded values shown on screen.

This check compares one field. To compare several fields, create a separate formula for each field, or build a consolidated status formula that tests each returned value. Separate checks are often easier to review because they show whether the discrepancy is in the amount, date, status, or another column.

Review results in both directions

Checking every ID on Sheet1 against Sheet 2 answers only whether Sheet1’s records can be found in Sheet 2. It does not reveal records that exist only on Sheet 2. To find those, repeat the presence check from Sheet 2, using each Sheet 2 ID as the lookup value and the Sheet1 ID range as the lookup range. Comparing both directions gives a fuller picture of additions and omissions.

After filling down, filter the result column to show only “Missing from Sheet 2” or “Different.” In Excel, select a cell in the data and choose Data > Filter, then use the filter arrow on the result column. You can also sort the status column so exceptions are grouped together. Keep the original rows and identifiers visible while reviewing results; a status without its key is hard to investigate.

For a useful reconciliation, record what each status means and the date or version of each source list. If one sheet is a current extract and another is an older export, a missing record might reflect timing rather than an error. The formula reports what the sheets contain; it cannot determine why the lists differ.

Common mismatches and how to fix them

A formula can report “Missing” even when two IDs look identical. Often the cause is a difference in the underlying data rather than a VLOOKUP problem. One ID may be stored as a number and the other as text, or a text value may contain leading or trailing spaces. A value such as 00124 can also lose its leading zeros if it is stored as a number, making it different from the text ID “00124.”

Check the cell contents: select each ID and inspect the formula bar. Confirm that the values have the same characters and that leading zeros have not been lost.
Check for spaces: imported text sometimes contains extra spaces. TRIM can remove ordinary leading and trailing spaces, but it does not remove every kind of nonprinting character.
Check the lookup range: confirm that the key is in its first column and that the range reaches the last populated row.
Check duplicates: VLOOKUP returns the first match. If duplicate IDs have different amounts, the returned value may not be the one you expected.
Check the match setting: use FALSE for an exact match. Approximate matching can return an unintended result when the lookup list is not arranged appropriately.

Do not “fix” IDs by converting them without checking what they represent. For product codes or account numbers, leading zeros may be meaningful even though they look numeric. Standardize both key columns using a consistent method that preserves the intended identifier, then rerun the comparison.

Understand VLOOKUP’s limits

VLOOKUP is straightforward for a simple key-and-return-column comparison, but it has constraints. It searches only the first column of its table range, returns a value from a column to the right, and uses a column number rather than a field name. If you insert or rearrange columns inside the range, the column number may point to a different field than before. Check the formula after structural changes.

Exact-match VLOOKUP does not require the lookup list to be sorted. The FALSE argument is what makes the search exact. This is different from approximate-match use, where sorting and match behavior matter. For comparisons, omitting the final argument is risky because VLOOKUP’s default is approximate matching.

VLOOKUP also does not automatically identify every difference between two complete tables. It checks the key and returns the requested column for that key. A full comparison may require multiple field checks, checks in both directions, or separate handling for duplicate and blank keys. Treat the result as a way to locate exceptions, not as proof that every aspect of two workbooks is identical.

Alternatives for larger or changing lists

If the sheets contain many columns or the lookup field is not the leftmost column, XLOOKUP can be more flexible. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It lets you specify a lookup array and a return array separately, and it can supply a value for the not-found case. Excel 2019 and earlier do not include XLOOKUP, so VLOOKUP or another compatible approach may be needed in those versions.

For repeated comparisons of regularly updated files, Power Query can import tables, merge them using a key, and show records that match or appear in only one source. It can be more suitable than copying formulas through a large workbook, but it requires setting up a query and refreshing it when the source data changes. For a one-time check on a modest list, VLOOKUP is often quicker to understand and maintain.

Choose the method based on the task: use VLOOKUP for a familiar, row-by-row lookup; use XLOOKUP when its flexible return range helps; and consider Power Query when you need a repeatable data-comparison process. Whichever method you choose, verify the key, account for duplicates, and review the exceptions against the source records.

Frequently Asked Questions

Can VLOOKUP compare two sheets in different workbooks?
Yes. Select the lookup range in the other workbook while building the formula, or use an external workbook reference. The source workbook may need to be available for the reference to update reliably, and Excel can include the workbook name and file path in the reference.

Do the two lists need to be sorted?
No, not when the formula’s final argument is FALSE for an exact match. Sorting can make lists easier for a person to inspect, but it is not required for exact-match VLOOKUP.

Why does VLOOKUP return #N/A when the ID is present?
Check for number-versus-text differences, extra spaces, missing leading zeros, an incorrect range, or a key that is not in the range’s first column. Also verify that the compared IDs truly contain the same characters.

Can VLOOKUP find records that are only on the second sheet?
Not from a formula filled down only on the first sheet. Run a second check in the other direction, using the second sheet’s IDs as lookup values and the first sheet’s ID column as the lookup range.

What happens if an ID appears more than once?
VLOOKUP returns the first matching row in the lookup range. If duplicate IDs are possible, investigate them separately or use a key that uniquely identifies each record before treating the returned value as definitive.

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.