VLOOKUP Formula in Excel to Find Duplicates

Formulas & functions · Updated November 20, 2026

To check whether a value in one Excel list also appears in another, use an exact-match VLOOKUP wrapped in ISNA: =IF(ISNA(VLOOKUP(D2,$A$2:$A$100,1,FALSE)),"Not found","Duplicate"). With the comparison list in A2:A100 and the value to check in D2, the formula returns “Duplicate” if it finds a match and “Not found” if it does not.

What “Find Duplicates” Means

There are two common tasks people mean by finding duplicates. One is checking whether entries in one list also appear in a second list—for example, whether this month’s customer names are already in a master list. The VLOOKUP formula above is designed for that kind of comparison. The other task is finding repeated entries within a single list. VLOOKUP can locate a value, but COUNTIF is usually a simpler and clearer tool for identifying repeats in one column.

Decide which task you have before choosing a formula. A match between two lists does not necessarily mean the value is repeated within either list: it may simply be a legitimate overlap. Conversely, the same name can appear twice in one list even if it is absent from another. In this article, “duplicate” in the VLOOKUP example means a value is present in both lists.

Set Up the VLOOKUP Comparison

For the example, suppose the existing or master list is in A2:A100. Put the values you want to check in D2:D50, and enter the formula in E2. VLOOKUP searches the first column of its table range, so the lookup list must be in column A at the left edge of the range. Here, the formula returns the matching value from that same column; the IF function turns the result into a useful label.

With the master list in A2:A100, a candidate value in D2, and the formula entered in E2, use:

=IF(ISNA(VLOOKUP(D2,$A$2:$A$100,1,FALSE),"Not found","Duplicate"))

That version has the closing parenthesis in the wrong place, so use this correctly balanced formula instead:

=IF(ISNA(VLOOKUP(D2,$A$2:$A$100,1,FALSE)),"Not found","Duplicate")

If D2 contains “Nora” and Nora appears anywhere in A2:A100, E2 displays “Duplicate.” If the list does not contain Nora, E2 displays “Not found.” Copy the formula down column E to check the other candidate values. The dollar signs keep the master-list range fixed as the formula is copied; D2 changes to D3, D4, and so on.

How the Formula Works

VLOOKUP looks for the value in D2 in the leftmost column of the selected table range. Its four arguments are the lookup value, the table range, the column number to return, and whether to use an approximate or exact match. The column number is 1 because the match is in the first—and only—column of A2:A100. FALSE requests an exact match, which is the appropriate choice when checking whether a list entry exists.

When a match is found, VLOOKUP returns the matching value. When there is no match, it returns the #N/A error. ISNA tests specifically for that error, and IF displays “Not found” when ISNA is TRUE or “Duplicate” otherwise. The labels are customizable; for example, replace “Duplicate” with “Already listed” if that better describes the comparison.

The range uses absolute references, written with dollar signs, so it stays A2:A100 when copied. The lookup reference D2 is relative, so it adjusts for each row. If your master list grows beyond row 100, expand the range or use an Excel table so new entries are included reliably.

Find Repeats Within One List

If your goal is to flag the second and later occurrence of a value in a single column, COUNTIF is a better fit. With the list in A2:A100 and the formula in B2, enter:

=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence"))

For the row being checked, COUNTIF counts how many times its value appears from the top of the list through that row. The first occurrence has a count of 1; a later occurrence has a count greater than 1 and is labeled “Duplicate.” The formula leaves a blank result when A2 is blank, avoiding a column of misleading duplicate labels for empty cells. Copy it down alongside the list.

This method deliberately marks only later occurrences, not the first one. If you want to label every value that appears more than once, including its first occurrence, use a count over the full list. With values in A2:A100 and the result in B2, enter:

=IF(A2="","",IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique"))

For instance, if “Nora” appears in A2 and A9, both rows will be labeled “Duplicate.” COUNTIF is often easier to maintain for this purpose because it directly counts occurrences rather than looking up and returning a value.

Use VLOOKUP to Return More Than a Label

You may want to retrieve a related detail from the matching row instead of displaying a duplicate label. For example, suppose names are in A2:A100 and account IDs are in B2:B100. With a candidate name in D2 and a formula in E2, use:

=IFNA(VLOOKUP(D2,$A$2:$B$100,2,FALSE),"Not found")

This searches the first column of A2:B100 and returns the value in the second column of the matching row. If Nora is found, E2 shows Nora’s account ID; otherwise it shows “Not found.” IFNA handles a missing-match #N/A result. VLOOKUP returns the first matching row, so if the master list contains the same name several times, this formula does not report every matching record. Use a unique identifier, such as an account ID, when names alone are ambiguous.

VLOOKUP’s Limits and Other Options

VLOOKUP is useful for a straightforward comparison, but it has specific behavior worth knowing. It searches only the leftmost column of its table range and returns a result from a column to the right. It also returns the first match it encounters. If the lookup column is not on the left, rearrange the data or use another lookup method rather than assuming VLOOKUP can search in either direction.

In Microsoft 365, XLOOKUP can perform an exact-match check and is not limited to returning values from columns to the right. With the master list in A2:A100, the candidate in D2, and the formula in E2, this version returns a label:

=IF(ISNA(XLOOKUP(D2,$A$2:$A$100,$A$2:$A$100)),"Not found","Duplicate")

XLOOKUP is available in Microsoft 365 and Excel 2021, but not in some older Excel releases. If a workbook must work in older versions, VLOOKUP with FALSE is a more broadly compatible option. For a plain yes-or-no membership check, COUNTIF is also a straightforward alternative: with the same lists and the formula in E2, use =IF(COUNTIF($A$2:$A$100,D2)>0,"Duplicate","Not found"). It counts matching cells and does not require a return-column number.

Common Problems and How to Fix Them

If a value that looks identical is reported as not found, check the underlying cell contents. One entry may contain a leading or trailing space, or a number may be stored as text in one list and as a number in the other. Clean or standardize the source data, then run the comparison again. A formula cannot reliably treat differently stored values as identical in every situation.

  • Wrong lookup range: Make sure the value to search is in the first column of the VLOOKUP range. If the master list is in column C, use a range that begins with C.
  • Approximate matching: Use FALSE as the fourth VLOOKUP argument. Leaving it out can invoke approximate-match behavior, which is not the right test for exact duplicates.
  • Range does not include new rows: Update the fixed range when the list grows, or convert the data to an Excel table and reference the table column.
  • Repeated entries in the master list: VLOOKUP returns the first matching entry. It confirms a match but does not count how many times the value appears there.
  • Different capitalization: Standard VLOOKUP matching is not case-sensitive. “Nora” and “NORA” are treated as a match. If capitalization is meaningful, use a case-sensitive method rather than relying on VLOOKUP alone.

ISNA is intentionally specific: it converts the expected “no match” #N/A error into a label while allowing other errors to remain visible for troubleshooting. IFERROR can hide any error, not just a missing match, so it may conceal problems such as an invalid column number in a more complex VLOOKUP.

Highlight or Filter the Results

A formula is helpful when you want a result column that can be sorted, filtered, or used in later calculations. If you only need to see duplicates within a selected range, Excel can also highlight them without adding a formula. Select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel opens a dialog where you can choose a formatting style.

That built-in rule is intended to identify repeated values in the selected range. It is not the same as comparing one list against another, so use the VLOOKUP or COUNTIF comparison for two separate lists. To work with formula results, select the result column and use Data > Filter; you can then filter for “Duplicate” or “Not found.” This makes it easier to review only the rows that need attention while keeping the original data intact.

Frequently Asked Questions

Can VLOOKUP find duplicates in the same column?
It can find a value in that column, but COUNTIF is usually simpler for identifying repeated occurrences. Use a growing range such as $A$2:A2 to label later repeats as you copy the formula down.

Why does my VLOOKUP formula return #N/A?
It means VLOOKUP did not find an exact match in the first column of its range. Check that the range starts in the right column, includes the value, and that spaces or text-versus-number differences are not making the entries unequal.

Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact match when checking for duplicates. TRUE requests approximate matching and is not appropriate for a simple exact-membership check.

Does VLOOKUP find every duplicate match?
No. VLOOKUP returns the first matching row. It can tell you that a value exists, but it does not list every occurrence or count them. Use COUNTIF to count matches, or another approach designed to return multiple results.

Is VLOOKUP case-sensitive?
No. Standard VLOOKUP matching does not distinguish uppercase and lowercase letters. If two entries differ only by capitalization and that difference matters, choose a case-sensitive comparison method.

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.