To find values shared by two worksheets, enter a VLOOKUP formula beside the values on the first sheet and use an exact match. If the IDs are in Sheet1!A2:A100 and Sheet2!A2:A100, put this in Sheet1!B2 and fill it down: =IF(A2="","",IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$100,1,FALSE)),"Not found","Duplicate")). It labels a value “Duplicate” when it appears in the second sheet and leaves blank rows blank.
What “duplicate” means in this comparison
Here, a duplicate means a value that appears in both worksheets. It does not necessarily mean that a row is repeated inside either worksheet. For example, if Sheet1 lists order IDs 1041, 1042, and 1043, while Sheet2 lists 1042, 1043, and 1044, then 1042 and 1043 are shared values. The other IDs are present in only one list.
VLOOKUP checks each value in one list against a lookup range in the other. It does not compare whole rows automatically. If an order ID matches but the customer name or amount differs, the ID still counts as a match; you would need to compare those other fields separately if that is what you mean by a duplicate.
It also matters which field you choose as the key. A unique customer number or invoice ID is usually safer than a name, because names can be spelled differently, shared by different people, or entered with inconsistent spaces. Decide what counts as “the same record” before interpreting the results.
How the VLOOKUP formula works
The formula uses VLOOKUP to search for the value in A2 within the first column of the range on Sheet2. The number 1 tells VLOOKUP to return the first column of that range, and FALSE requests an exact match rather than an approximate one. Since the lookup range is a single column, the returned value is either the matching key or an error if no match is found.
ISNA checks whether VLOOKUP returned the #N/A error that means it could not find the value. IF then displays “Not found” or “Duplicate” rather than leaving an error code in the results. The outer IF checks whether A2 is blank, which avoids labeling empty rows as matches or non-matches.
The dollar signs in $A$2:$A$100 lock the lookup range when you copy the formula down. The reference to A2 is relative, so it changes to A3, A4, and so on. If your list extends past row 100, expand the lookup range to cover all the relevant rows; otherwise, values below the range will never be checked.
Set up the worksheets before comparing
Keep the values you want to compare in one column on each worksheet, with a header in row 1 and records beginning in row 2. In this example, both columns contain order IDs. The sheets must be named Sheet1 and Sheet2 for the formula shown in the opening paragraph to work unchanged. If your tabs have different names, replace Sheet2 with the actual name.
- Use the same kind of identifier in both columns, such as invoice ID against invoice ID, rather than comparing unrelated fields.
- Check that numbers stored as text are not mixed with numeric values. A value that looks like 1042 can be text in one sheet and a number in the other, which may prevent an expected match.
- Remove or account for leading and trailing spaces if they are accidental. Excel treats text with extra spaces as different text.
- Keep the lookup range limited to the data area where practical. A bounded range is easier to inspect and can calculate more efficiently than a whole-column lookup in a large workbook.
If a worksheet name contains spaces, Excel requires single quotation marks around it in a sheet reference. For example, a range on a tab named March Data is referenced as 'March Data'!$A$2:$A$100. The apostrophes are part of Excel’s worksheet-reference syntax; do not use double quotation marks around the sheet name.
Enter the formula and read the results
- Open the worksheet containing the first list. Add a heading such as Match status in the column beside the IDs.
- Select the first result cell, such as B2, and enter the formula from the opening paragraph. Confirm that its lookup range points to the other worksheet and the correct key column.
- Press Enter. If A2’s value is present in the lookup range, the result should say Duplicate; otherwise, it should say Not found.
- Copy the formula down alongside every record. You can drag the fill handle at the lower-right corner of the cell, or double-click it when the adjacent data column is continuous.
- Filter the status column to show Duplicate if you want to review only shared IDs. Keep the original lists intact until you have checked the results.
For a concrete check, suppose Sheet1 has 1041, 1042, 1043, and 1044 in A2:A5, while Sheet2 has 1042, 1044, and 1045 in A2:A4. Filling the formula down on Sheet1 returns Not found for 1041, Duplicate for 1042, Not found for 1043, and Duplicate for 1044. The 1045 entry is not reported because the formula is checking the first list against the second, not the other way around.
Check whether duplicates exist in either direction
One pass answers the question “Which values in Sheet1 also appear in Sheet2?” It does not identify values that exist only in Sheet2. To find every unmatched value in both lists, run a second check beside the Sheet2 values and point that check back to Sheet1. In other words, reverse the source and lookup ranges while keeping the same exact-match logic.
This two-way check separates three useful outcomes: values shared by both sheets, values found only in the first sheet, and values found only in the second. If the lists have different lengths, that distinction is especially important. A one-way comparison can look complete while silently overlooking extra records in the lookup sheet.
Repeated occurrences are a separate issue. If an ID appears four times on Sheet2, VLOOKUP still reports that it exists; it does not tell you that there are four occurrences. If you need to detect repeated entries within a single worksheet, use a count-based check or sort and inspect that sheet as a separate task.
Common problems and how to diagnose them
- Every result says Not found. Confirm the sheet name, range, and key column. VLOOKUP only searches the leftmost column of its table range. If the IDs are in column C, the lookup range must begin in column C.
- Some apparent matches are missed. Compare the underlying values, not just how they look on screen. Check for spaces, different punctuation, leading zeros, or numbers stored as text. For instance, an ID displayed as 00123 may not be equivalent to numeric 123 if the leading zeros are meaningful.
- The formula returns #REF! or another error. Check that the column index is within the lookup range and that the referenced sheet and cells still exist. In the formula shown, the index is 1 because the lookup range contains one column.
- A blank row is labeled unexpectedly. The opening formula deliberately returns a blank when A2 is empty. If you remove that outer blank check, VLOOKUP may search for a blank value in the other range.
- Codes include characters such as an asterisk. VLOOKUP with exact-match mode can interpret * and ? as wildcard characters in text lookups. If those symbols are part of literal IDs, use a method that handles them as literal characters or clean the key format before comparing.
When differences are hard to spot, temporarily place a copy of the key beside the result and inspect a few examples from each outcome. Avoid assuming that formatting differences alone prove the values are different: cell formatting can make two underlying values appear similar, or make equivalent numbers look different.
COUNTIF and other ways to compare worksheets
For a simple yes-or-no existence check, COUNTIF is a shorter alternative. With IDs in Sheet1!A2:A100 and Sheet2!A2:A100, enter this in Sheet1!B2 and fill it down:
=IF(A2="","",IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"Duplicate","Not found"))
It produces the same labels for ordinary IDs and is easy to read as “count this value in the other range; if the count is greater than zero, it exists.” Like VLOOKUP, COUNTIF does not count how many copies are in the first list, and its criteria handling has special behavior for wildcard characters such as * and ?. Choose based on what you need: VLOOKUP is useful when you may later want to return a related field, while COUNTIF is concise for existence alone.
In Microsoft 365, XLOOKUP is another option and can return a custom not-found message, but it is not necessary for this task. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not support it. For a quick visual scan, Excel’s Duplicate Values conditional-formatting rule can highlight duplicates within a selected range, but it does not directly compare a range on one worksheet with a separate worksheet. For recurring comparisons or larger data sets, Power Query can merge tables on a key and show matching or unmatched records, though it takes more setup than a worksheet formula.
Frequently Asked Questions
Can VLOOKUP find duplicates between two different worksheets?
Yes. Put the values from one worksheet into VLOOKUP’s lookup value and use the other worksheet’s key column as the lookup range. Set the final argument to FALSE for an exact match, then use IF and ISNA to show a readable status.
Does VLOOKUP find duplicates in both worksheets at once?
No. One formula checks values from its source list against the other list. Repeat the comparison in the opposite direction to identify values that appear only on the second worksheet as well as those that appear only on the first.
Can VLOOKUP count how many times an ID appears?
Not with the existence-check formula above. It reports whether a match exists, and VLOOKUP returns the first match it encounters. Use COUNTIF when you need an occurrence count, or another counting approach designed for repeated values.
Why does VLOOKUP miss IDs that look identical?
Common causes include extra spaces, text-versus-number differences, leading zeros, or inconsistent punctuation. Check the stored values and standardize the key columns before relying on the comparison.
Will this formula work in Excel for Mac or Excel for the web?
VLOOKUP, IF, and ISNA are available in those versions too, and the formula logic is the same. Ribbon layouts and some fill interactions can differ, but you can enter the formula in a cell and copy it down in either version.
Related
- Supplier Contact List Template in Excel: Fields and Setup
- Supply List Excel Template: How to Build and Use One
- Excel Superscript Shortcut: Apply Superscript to Text
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- BMI Calculator Excel Formula: Steps, Examples, and TipsTo calculate BMI in Excel, divide weight in kilograms by height in meters squared. If weight is in…
- Excel MATCH Function: Arguments and Match TypesThe Excel MATCH function takes a value to find, a one-dimensional range to search, and an optional match…
- How to Count Individual Names in Excel with a FormulaTo count how often each individual name appears, list the names in one column, create a unique-name list,…
- If Excel Contains Specific Text: Formulas That WorkTo check whether a cell contains specific text in Excel, use IF with SEARCH and ISNUMBER. With the…
- String Functions in SQL: Common Uses, Syntax, and ExamplesSQL string functions work with text: they can join values, change letter case, remove unwanted spaces, extract part…
- Calculate CAGR Growth in Excel: Formula and ExamplesTo calculate compound annual growth rate (CAGR) in Excel, divide the ending value by the beginning value, raise…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.