VLOOKUP Formula to Compare Two Sheets in Excel Easily

Formulas & functions · Updated November 5, 2026

To compare values on two Excel sheets by a shared ID, enter this formula on the first sheet, assuming IDs are in column A and values to compare are in column B on both sheets: =IFERROR(IF(VLOOKUP(A2,'Sheet 2'!$A$2:$B$100,2,FALSE)=B2,"Match","Different"),"Missing"). It looks up the ID from A2 on Sheet 2, compares the corresponding value with B2, and reports whether it matches, differs, or is missing.

What the comparison checks

A VLOOKUP comparison is useful when two sheets contain related records but you need to check whether a field agrees for each record. For example, one sheet might hold a current product list and another an updated list. A product ID can identify the same item on each sheet, while its price or description is the value you want to compare.

The ID is the lookup value: it tells Excel which row on the other sheet to examine. The value in the selected return column is then compared with the value in the current row. In the opening formula, the three possible results mean:

  • Match: Excel found the ID and the two values are equal.
  • Different: Excel found the ID, but the values are not equal.
  • Missing: VLOOKUP did not find that ID in the lookup range.

This is a row-by-row comparison, not a complete audit of every record in both directions. If the second sheet contains IDs that never appear on the first, you need to check that sheet separately as well.

Set up the sheets and lookup range

For the example, Sheet1 has IDs in A2:A100 and values in B2:B100. Sheet 2 has the corresponding IDs in A2:A100 and values in B2:B100. Put the comparison formula in C2 on Sheet1, then fill it down the rows you want to check. The sample range ends at row 100; adjust it to cover your actual data.

VLOOKUP searches only the leftmost column of its table range. That is why the lookup range begins with the IDs in column A and extends to the values in column B. The number 2 in the formula tells Excel to return the second column of that range. FALSE requests an exact match, which is generally the right choice for identifiers such as product codes, employee numbers, or invoice IDs.

Because the formula compares a value from the same row as the lookup ID, confirm that A2 and B2 on the first sheet belong together. The formula does not establish that the current sheet’s rows are correctly paired; it uses the ID to find the corresponding row on the other sheet.

How to enter and fill the formula

  1. Open the workbook and identify the key column and the field you need to compare on each sheet.
  2. On the sheet you want to use as the comparison report, select the first empty result cell, such as C2.
  3. Enter the formula, replacing the sheet name, columns, and row limits if your layout differs.
  4. Press Enter and check the result against a record you can verify manually.
  5. Copy the formula down. You can drag the fill handle at the lower-right corner of the cell, or double-click it when adjacent rows contain data.

The dollar signs in $A$2:$B$100 lock the lookup range so it stays fixed when the formula is filled down. A2 and B2 have no dollar signs, so those row references change to A3 and B3, then A4 and B4, and so on. If your list is longer than row 100, extend the range before filling the formula.

In Excel for Microsoft 365 on Windows, a sheet name with a space is surrounded by single quotation marks, as in 'Sheet 2'!$A$2:$B$100. A sheet name without spaces can also be quoted, though it does not require it. One way to avoid typing the reference is to start the formula, click the other sheet tab, and select the range; Excel inserts a reference to that sheet. On Mac, the formula works the same way, and you can select ranges across sheets in the same manner. Excel for the web also supports VLOOKUP and cross-sheet references.

Understand the formula parts

The comparison formula combines VLOOKUP with IF and IFERROR. VLOOKUP searches for the value in A2 in the first column of the specified range. With a match, it returns the value in the second column. IF then compares that returned value with B2 and chooses Match or Different. If VLOOKUP cannot find the ID, it returns an #N/A error; IFERROR changes that error into the more readable result Missing.

FALSE is important here. If you omit the final VLOOKUP argument or use TRUE, Excel can perform an approximate match, which is unsuitable for many ID comparisons and can return an unexpected record. Exact matching does not require the lookup IDs to be sorted. It does require the values used as keys to be comparable, including consistent data types and spelling.

For example, if A2 contains 1042 and B2 contains North, and Sheet 2 has 1042 in A12 and North in B12, the formula returns Match. If B12 contains South instead, it returns Different. If 1042 does not appear in Sheet 2 column A within the stated range, the result is Missing.

Check whether an ID exists without comparing a field

Sometimes the question is only whether each ID on the first sheet appears anywhere on the second. In that case, look up the ID in a one-column range. With IDs in A2:A100 on the current sheet and IDs in A2:A100 on Sheet 2, enter this in the result column on the current sheet:

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

For an ID that exists in the lookup range, this returns Found; an ID that is absent returns Missing. The range has one column, so the return-column number is 1. ISNA tests specifically for the not-found error, rather than hiding every possible error. That can make unexpected issues easier to notice than using a broad error handler.

This test checks only from the current sheet to Sheet 2. To identify IDs that are present only on Sheet 2, put a corresponding check on Sheet 2 using its ID as the lookup value and the first sheet’s ID range as the lookup range.

Handle duplicates and inconsistent data

VLOOKUP returns the first matching row it encounters in the lookup range. If Sheet 2 contains the same ID more than once with different values, the formula does not compare against every duplicate or warn you that the key is repeated. Check for unique IDs before relying on the result, or decide how duplicates should be resolved. A lookup based on an ID that is not unique may give a technically valid result that does not represent the intended record.

Watch for differences that look identical on screen but are not stored identically. One sheet may store an ID as a number and another as text; codes may also contain leading or trailing spaces, inconsistent punctuation, or different capitalization. Leading zeros matter for codes such as 00125: if one sheet stores the code as the number 125, the leading zeros are not part of that numeric value. Decide whether the field is a number or a text identifier and standardize both lists accordingly.

Text comparisons can also be affected by spaces and formatting choices. VLOOKUP does not automatically trim unwanted spaces or convert a text code into a number. Inspect the source values when records unexpectedly show Missing or Different. You may need to clean the source columns or create consistent helper columns before running the comparison. Avoid changing identifiers casually: a conversion that removes meaningful leading zeros can make a comparison less accurate.

Common errors and practical fixes

  • #N/A appears: The key may genuinely be absent, or the lookup range may not include it. Check the spelling, data type, spaces, and range boundaries. IFERROR in the main formula displays Missing instead, but it does not fix the underlying cause.
  • Every row says Missing: Verify the sheet name and range reference, and confirm that IDs are in the first column of that range. Check that the formula refers to the intended cell on the current sheet.
  • A result is wrong despite a matching ID: Look for duplicate IDs. VLOOKUP returns the first matching row, so later duplicates are not considered.
  • The formula displays as text: The destination cell may be formatted as Text, or the entry may begin with an apostrophe. Set the cell to General, then re-enter the formula.
  • Excel reports a formula problem: Confirm that quotation marks and parentheses are balanced and that the sheet name is correct. The examples use commas as argument separators; some regional Excel settings use semicolons instead.

Also check whether a comparison is case-sensitive before treating a Match as proof of identical text. Standard VLOOKUP text matching is not case-sensitive, so values such as North and NORTH are treated as equal for this purpose. If capitalization itself is meaningful to your check, use a case-sensitive method instead of relying on this formula.

VLOOKUP compared with newer lookup options

VLOOKUP is a practical choice when the key is in the leftmost column of a table and the return field is to its right. Its limitation is that the return-column number is counted from the beginning of the selected range. If someone inserts or rearranges columns, that number may no longer point to the intended field. VLOOKUP also cannot return a value from a column to the left of its lookup column.

For Microsoft 365, XLOOKUP can express the same comparison by specifying the lookup and return columns separately. With IDs in A2:A100 and values in B2:B100 on both sheets, and the current row’s ID and value in A2 and B2, this formula returns Match, Different, or Missing:

=IF(XLOOKUP(A2,'Sheet 2'!$A$2:$A$100,'Sheet 2'!$B$2:$B$100,"Missing",0)="Missing","Missing",IF(XLOOKUP(A2,'Sheet 2'!$A$2:$A$100,'Sheet 2'!$B$2:$B$100,"Missing",0)=B2,"Match","Different"))

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Its separate lookup and return ranges avoid VLOOKUP’s leftmost-column restriction, and its match-mode argument of 0 requests an exact match. However, the VLOOKUP formula may suit workbooks that must remain compatible with older Excel versions. Choose based on the Excel versions used by the people who need to open the workbook.

Frequently Asked Questions

Can VLOOKUP compare two sheets directly?
Yes. Use a value from one sheet as the lookup value, reference the other sheet’s lookup range, and compare the returned value with the value on the first sheet. Both sheets need a shared key for matching corresponding records.

Does VLOOKUP compare two entire sheets automatically?
No. It evaluates the lookup for the row containing the formula. Fill the formula down to check the rows in your list, and run a reverse check if you also need to find records that exist only on the other sheet.

Why use FALSE in a VLOOKUP comparison?
FALSE requests an exact match for the lookup key. This is usually appropriate for IDs and codes because an approximate match can return the wrong row, especially when the data is not arranged for approximate lookups.

Can VLOOKUP find values when the lookup column is not first?
No. VLOOKUP searches the first column in its table range. Reorder the range so the key is first, or use XLOOKUP in Microsoft 365 or Excel 2021 and later, where lookup and return ranges are specified separately.

How do I find IDs that appear on only one sheet?
Run an exact-match existence check from the first sheet to the second, then run the equivalent check in the opposite direction. A one-way check cannot reveal records that occur only in the sheet being searched.

Choose a comparison that fits the task

For a straightforward check of corresponding values, use the three-result formula and verify a few matches, differences, and missing IDs manually. For a presence-only check, use the simpler Found/Missing formula. In either case, make sure the key is stable and unique, the range covers the full list, and the formula is filled through every relevant row. Those checks make the output easier to trust and help distinguish real differences from data-quality problems.

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.