Use VLOOKUP in Excel to Compare Two Sheets

Formulas & functions · Updated November 21, 2026

If Sheet1 has matching IDs in column A and values to compare in column B, and Sheet2 uses the same layout, enter this in Sheet1 cell C2 and fill down: =IFERROR(IF(B2=VLOOKUP(A2,Sheet2!$A$2:$B$100,2,FALSE),"Match","Different"),"Not found"). It looks for each ID from Sheet1 in Sheet2, compares the corresponding values, and reports whether the records match or the ID is missing.

What VLOOKUP compares between sheets

VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. To compare two worksheets, use a shared identifier—such as an employee number, product code, invoice ID, or email address—to find the corresponding record on the other sheet. Then compare the value you want to check.

In the opening example, A2 is the lookup key on Sheet1. VLOOKUP searches for that key in the first column of the range on Sheet2, then returns the value from the range’s second column. The outer IF checks whether that returned value equals B2. IFERROR changes a lookup error, such as an ID that is absent from Sheet2, into the readable result Not found.

This is a row-by-row comparison, not a comparison of every cell or the overall structure of two worksheets. It is most useful when both lists have a reliable shared key and you want to identify matching, changed, or missing records.

Prepare the sheets and choose a lookup key

Before writing a formula, make sure the sheets contain the same kind of identifier in a consistent format. A person’s name may not be unique, and small differences in spelling can prevent a match. A unique employee ID or account number is usually a better key. If a record has no single unique field, you may need to create a combined key or compare more than one field separately.

For the example, arrange the data like this:

  • Sheet1: identifiers in A2:A100, values to check in B2:B100, and comparison results in column C.
  • Sheet2: corresponding identifiers in A2:A100 and comparison values in B2:B100.

The lookup range must start with the column containing the identifiers. The rows in the two sheets do not need to be in the same order: VLOOKUP searches for the key and returns the value from its matching row. The dollar signs in the range reference lock its boundaries when you copy the formula down. Change the row limits to include your actual data, or use a larger range if appropriate.

Enter the comparison formula step by step

  1. Open the workbook and confirm the worksheet names. The example uses Sheet1 and Sheet2; if a worksheet name contains spaces, enclose it in single quotation marks in the reference, as in ‘January Data’!$A$2:$B$100.
  2. On Sheet1, select C2, the first row where you want a result.
  3. Enter the opening formula. It uses FALSE as the fourth VLOOKUP argument to request an exact match rather than an approximate match.
  4. Press Enter and check the result against a record you can verify manually.
  5. Copy the formula down column C for the remaining records. You can drag the fill handle or select the target cells and use Home > Fill > Down in Excel for Microsoft 365 on Windows.
  6. Review the results, especially Not found and Different. Those labels identify records that need investigation; they do not explain why a discrepancy exists.

In this formula, 2 means return the second column of the selected range, which is column B because the range begins in column A. If you change the layout, update the range and column number together. The formula does not search the entire worksheet; it searches only the first column of the range you specify.

Read a concrete example

Suppose Sheet1 lists item IDs and current prices: A2 contains P104 and B2 contains 12.50. On Sheet2, P104 appears in A18 and its listed price in B18 is 12.50. The formula returns Match even though the records occupy different rows. If B18 is 13.00, the result is Different. If P104 does not appear anywhere in Sheet2’s lookup range, the result is Not found.

That distinction matters when reviewing a reconciliation. Different means the key was found but the compared values do not equal one another. Not found means the formula could not find the key in the specified range. A missing result might indicate a genuinely absent record, an incomplete range, or inconsistent key formatting, so check the source data before treating it as a confirmed omission.

For numeric values such as prices or quantities, an exact equality check is appropriate when the values should agree precisely. If the values contain calculated decimals and tiny rounding differences are expected, decide on an acceptable tolerance instead of assuming that every unequal value is meaningful. A text comparison can also be affected by differences such as extra spaces or inconsistent punctuation.

Find records that are missing from the other sheet

Sometimes you only need to know whether each ID from Sheet1 exists on Sheet2, not compare a second field. With IDs in Sheet1 A2:A100 and IDs in Sheet2 A2:A100, enter this in Sheet1 B2 and fill down:

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

VLOOKUP searches Sheet2’s ID column and returns the matching ID when it finds one. ISNA detects the #N/A result used when no match exists, and IF displays a clearer label. This check only goes from Sheet1 to Sheet2. To find IDs that exist on Sheet2 but not Sheet1, run the equivalent check in the other direction, swapping the sheets and the relevant ID cells.

A comparison is only as complete as its lookup range. If new rows were added below row 100, they will not be included in this example. Expand the range to cover all relevant records before interpreting Missing as a true discrepancy.

Understand duplicates and exact-match behavior

VLOOKUP returns the first matching row it encounters in the lookup range. If Sheet2 contains the same ID more than once with different values, the opening formula compares Sheet1’s value only with the first occurrence. It will not warn you that the key is duplicated or compare against every duplicate. Check that the identifier is unique when that is a requirement, or resolve duplicates before relying on the comparison.

Using FALSE for exact matching is important for IDs and codes. With an approximate-match setting, results can be incorrect or misleading when the lookup data is not arranged as required for that behavior. For a sheet-to-sheet reconciliation, the intended question is usually whether that exact key exists, so FALSE is the appropriate choice.

Exact matching does not make unlike values equivalent. For example, the number 123 and text stored as 123 may not behave as the same key in every comparison context. Leading zeros can also be significant: a code stored as text with value 00123 is not necessarily interchangeable with the number 123. Keep identifiers consistently typed and formatted across both sheets.

Troubleshoot common VLOOKUP problems

  • Unexpected Not found results: Check spelling, leading or trailing spaces, and whether the lookup range includes the full list. A text-formatted ID and a numeric ID can look alike on screen but be stored differently.
  • Wrong value returned: Verify that the ID column is the first column in the selected range and that the column index points to the field you intend to compare. In the example, 2 returns the range’s second column.
  • Results change when copied: Keep the lookup range fixed with dollar signs, as in $A$2:$B$100. The reference to A2 should remain relative so it changes to A3, A4, and so on as the formula fills down.
  • Some records are missed: Confirm that the range includes the last used row and that the data is on the sheet named in the formula. A misspelled worksheet name normally produces a reference error rather than a valid comparison.
  • Values look identical but show Different: Check whether one value is stored as text and the other as a number, whether there are extra spaces, and whether decimal precision or punctuation differs. Formatting alone may make distinct underlying values appear alike.

For text fields where spaces are the likely problem, it may be useful to clean the source values in helper columns before comparing. Do not remove characters blindly from identifiers: spaces or punctuation may be meaningful in a particular code system. Keep an untouched copy of source data so that cleanup decisions can be reviewed.

Make the results easier to review

Once the formulas are filled down, filter the result column to show only Different or Not found. This narrows a long reconciliation to records that need attention. In Excel for Microsoft 365 on Windows, you can also highlight labels using Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, then specify the text to highlight. Choose separate formatting for Different and Not found if you want to distinguish changed values from missing keys visually.

Keep the original data intact and put comparison results in a separate column or worksheet. That makes it easier to audit what was compared and reduces the risk of overwriting a source value. If the source sheets are refreshed regularly, document the key columns, comparison field, and row coverage so the same checks can be repeated consistently.

VLOOKUP limitations and alternatives

VLOOKUP is a practical choice when the lookup key is in the leftmost column of the lookup range and the return field is to its right. It uses a numbered column argument, so inserting or rearranging columns can make that number point to a different field. It also returns only one matching row, which is a limitation when identifiers are duplicated.

In Microsoft 365 and Excel 2021, XLOOKUP can search a lookup column and return from a separate result column, including a column to its left. It also has an option for text to display when no match is found. For a straightforward comparison, VLOOKUP remains useful because it is widely recognized and its exact-match workflow is simple. If the workbook must open in an older Excel version, check function compatibility before replacing it with a newer function.

For repeated comparisons involving large or regularly refreshed datasets, Power Query may be a better fit than filling formulas down manually. It can merge tables using selected columns and load the result back into Excel. That approach takes time to learn, but it can make a recurring reconciliation easier to refresh and document. For a one-time check between two modest lists, a VLOOKUP result column is often more direct.

Frequently Asked Questions

Can VLOOKUP compare two sheets in different workbooks?
Yes. The lookup range can refer to another open workbook, and Excel may include that workbook’s name in the reference. Keep both workbooks accessible while building or checking the formula, and confirm that the external reference still points to the correct file if it is moved or renamed.

Does VLOOKUP require the rows to be in the same order?
No. With FALSE for exact matching, VLOOKUP searches the first column of its range for the key and returns a value from the matching row. The records can be in different orders on the two sheets.

Why does VLOOKUP return #N/A?
It usually means the exact lookup value was not found in the first column of the specified range. Check the range boundaries, the key’s data type, and differences such as spaces or leading zeros. IFERROR can display a friendlier message, but it does not fix the underlying mismatch.

Can VLOOKUP compare two whole worksheets at once?
Not as a single formula that automatically checks every cell and every row. Typically, you choose a key and a field to compare, enter a formula for the first record, and fill it down. A separate formula or a data-comparison tool may be needed for additional fields or structural differences.

What if I want to compare more than one column?
Create a comparison result for each field you need to verify, using the same key lookup and the appropriate return column. Keep the results clearly labeled. If you need to reconcile many fields repeatedly, consider a structured Power Query merge or another process designed for multi-column comparisons.

Related

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.