To find values in one Excel list that also appear in another, use VLOOKUP with an exact match: with values in A2:A100 and the comparison list in D2:D100, enter =IF(A2="","",IF(ISNA(VLOOKUP(A2,$D$2:$D$100,1,FALSE)),"Not in comparison list","Match")) in B2 and fill down. “Match” means the value in column A appears in column D; it does not mean the value is repeated within column A. For duplicates in a single column, use COUNTIF instead, because VLOOKUP finds the current cell’s own value.
What VLOOKUP Can—and Cannot—Tell You
VLOOKUP searches for a value in the first column of a lookup range and returns a value from a specified column in that range. It is useful for comparing two lists: for example, checking whether this month’s customer IDs also appeared in last month’s list, or finding which product codes in an order sheet exist in a reference table.
A common mistake is to search a column for its own values and label every successful VLOOKUP result a duplicate. If A2 contains a value and the lookup range includes A2, VLOOKUP finds that value in its own row—even when it appears nowhere else. The formula therefore confirms that the value exists in the range, not that it occurs more than once. This distinction matters when deciding whether you are comparing two lists or checking one list for repeated entries.
VLOOKUP is available in Microsoft Excel for Microsoft 365 on Windows, as well as many earlier Excel versions. The examples below use the standard function and work in Excel for the web too. The FALSE argument requests an exact match, which is usually the right choice for IDs, names, and codes.
Compare Two Lists with VLOOKUP
Suppose column A contains the current set of product codes and column D contains codes from a reference list. Put a result beside the first value in A, then copy it down. With current codes in A2:A100 and reference codes in D2:D100, use the formula in B2:
=IF(A2="","",IF(ISNA(VLOOKUP(A2,$D$2:$D$100,1,FALSE)),"Not in comparison list","Match"))
The first IF leaves the result blank when A2 is blank. VLOOKUP then searches for A2 in the first (and only) column of D2:D100. The 1 tells Excel to return the first column of that range, and FALSE means the match must be exact. If VLOOKUP cannot find the value, it returns #N/A; ISNA detects that error and displays “Not in comparison list.”
For example, if A2 is P-104 and that code appears in D15, B2 shows “Match.” If it is absent from D2:D100, B2 shows “Not in comparison list.” A code can appear several times in column D and still produce the same “Match” result. VLOOKUP answers whether at least one match exists; it does not count how many matches there are.
To show a value from another column of the reference table, make sure the lookup column is the first column in the selected range. With codes in D2:D100 and descriptions in E2:E100, this formula in B2 returns the description for each code in A2:A100, or a message when no code is found:
=IF(A2="","",IFERROR(VLOOKUP(A2,$D$2:$E$100,2,FALSE),"Not found"))
Here the 2 means “return the second column of D:E,” which is column E. This can help verify a match by showing its associated label, but it still does not count repeated codes in either list.
Find Repeated Values in One Column
If the question is whether a value appears more than once in the same list, COUNTIF is more direct. With entries in A2:A100, enter this in B2 and fill it down:
=IF(A2="","",IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique"))
COUNTIF counts how many cells in A2:A100 equal the value in A2. A count greater than 1 means the value appears at least twice, so every occurrence—including the first—is marked “Duplicate.” For example, if the list contains P-104 in A2 and A8, both rows are marked as duplicates. A code that appears only in A3 is marked “Unique.”
If you want to mark only the second and later occurrences, rather than every member of a duplicate group, use a running range. With the same list starting in A2, enter this in B2 and copy down:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Repeat after first","First occurrence"))
For P-104 in A2 and A8, the first row is labeled “First occurrence” and A8 is labeled “Repeat after first.” The range expands as the formula is filled downward, so each row counts the matching values from the top of the list through its own row. This is often useful when you want to keep the first record and review later repeats.
Enter and Fill the Formula
- Identify whether you are comparing two different lists or checking one list for repeats. Choose the VLOOKUP or COUNTIF method accordingly.
- Click the result cell beside the first data row, such as B2, and enter the appropriate formula.
- Press Enter to calculate it. Select the result cell again and drag its fill handle down the column, or double-click the fill handle to fill alongside adjacent data.
- Check a known match and a known non-match before relying on the results. Confirm that the ranges include all intended rows and exclude headings where appropriate.
- Filter the result column to “Match” or “Duplicate” to review the relevant records. In Excel, use Data > Filter, or use the filter arrow if the range is already filtered.
The dollar signs in references such as $D$2:$D$100 keep the lookup range fixed as you fill the formula down. The A2 reference is relative, so it changes to A3, A4, and so on. If you add rows beyond row 100, expand the range; otherwise, new entries will not be included. A formatted Excel Table can make expanding data easier, because its ranges can grow when rows are added.
Highlight Duplicates Instead of Adding a Result Column
For a visual check within one column, conditional formatting can color repeated values without adding a helper column. Select A2:A100, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a formatting style and confirm. This rule highlights values that occur more than once in the selected range.
You can also use a formula-based conditional formatting rule if you need to control the range or customize the rule. With A2:A100 selected and A2 as the active cell, choose Home > Conditional Formatting > New Rule, select “Use a formula to determine which cells to format,” and enter:
=COUNTIF($A$2:$A$100,A2)>1
Choose a format and select OK. The formula uses a fixed count range and a relative row reference, so Excel evaluates each selected cell against the full list. Conditional formatting marks duplicates visually; it does not remove them or change the underlying values. The menu names are the same in Excel for Microsoft 365 on Windows and Mac, though ribbon layout can vary slightly.
Common Matching Problems
- Extra spaces: “P-104” and “P-104 ” may not compare as expected. Clean imported text before matching. With a value in A2,
=TRIM(A2)removes ordinary leading, trailing, and repeated spaces; check the cleaned results before replacing source data. - Numbers stored as text: The number 104 and text “104” may behave differently in some comparisons. Check cell formatting and how the data was imported, then make both lists use a consistent data type.
- Case differences: VLOOKUP’s exact match is not case-sensitive, so ABC and abc are treated as a match. If letter case is meaningful, VLOOKUP alone is not enough to distinguish them.
- Wrong lookup range: VLOOKUP searches only the first column of its table array. If the values to search are in column E, a range beginning in D will search D instead. Reorder the range or use an alternative lookup method.
- Blank cells: A blank lookup value can match a blank in the comparison range. The opening formula avoids displaying a result for blank cells in column A, but a blank in the reference list is still worth checking if your data is untidy.
- Partial matches: FALSE requests an exact match; do not omit it when looking up IDs or codes. Wildcard matching is possible in some VLOOKUP cases, but it can create misleading results when the goal is to verify complete values.
When a result seems wrong, test a specific value by checking both its displayed contents and its underlying value. A cell may look like a clean code while containing spaces, a different data type, or other imported characters. Standardizing the lists before comparing them can prevent false “not found” results.
VLOOKUP Compared with Other Options
For a two-list existence check, VLOOKUP is familiar and works in older Excel versions. COUNTIF is often simpler when the only question is how many times a value occurs in one column, and it can also compare lists. With values in A2:A100 and the comparison list in D2:D100, this formula in B2 returns “Match” when A2 occurs at least once in column D:
=IF(A2="","",IF(COUNTIF($D$2:$D$100,A2)>0,"Match","Not found"))
XLOOKUP is another option in Microsoft 365 and Excel 2021 or later, but it is not necessary for the basic VLOOKUP method. For a simple yes-or-no comparison, choose the function that is easiest to maintain in the workbook and supported by the people who need to use it. For duplicates inside one list, COUNTIF or conditional formatting is generally clearer than trying to make VLOOKUP search its own source.
Frequently Asked Questions
Can VLOOKUP find duplicates in the same column?
Not reliably by searching the full column for each value: each value can match itself, even if it is unique. Use COUNTIF to test whether a value occurs more than once in that column.
Why does my VLOOKUP say every value is a match?
If the lookup range includes the value being checked, VLOOKUP can find that same value. This confirms existence in the range, not a second occurrence. Use a separate comparison list or COUNTIF for same-column duplicates.
How do I mark only the second occurrence?
Use the running COUNTIF formula above. It compares each row with the values from the first data row through the current row, so the first copy is labeled separately from subsequent copies.
Does VLOOKUP count how many times a value appears?
No. VLOOKUP returns a matching result from the first match it finds; it does not report the number of matches. Use COUNTIF when you need a count.
Can I delete duplicates after finding them?
Yes, but review the records first and keep a backup. Excel’s Data > Remove Duplicates command deletes repeated records from the selected range; it is separate from VLOOKUP and does not merely highlight them.
Related
- Excel SUM with Multiple Criteria: How to Do It
- Excel: Subtract the Same Value From Multiple Cells
- Excel Spreadsheet Keyboard Shortcuts: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel VLOOKUP Examples With a Drop-Down ListTo make a drop-down selection drive a lookup in Excel for Microsoft 365 on Windows, create a list…
- Calculate Profit Margin % in Excel: Formula and StepsTo calculate profit margin in Excel, divide profit by revenue. With revenue in B2 and total costs in…
- Excel NPV Formula for Monthly Cash Flows, ExplainedFor equally spaced monthly cash flows in Excel, use the NPV function with a monthly discount rate, then…
- How to Count Hours in Excel With a FormulaTo calculate hours between a start time and an end time, put the start in A2 and end…
- Excel MID Formula: Extract Text by PositionUse Excel’s MID function to extract a specified number of characters from a text value, starting at a…
- Excel Goal Seek Formula Alternatives That WorkExcel does not have a worksheet function called GOALSEEK; Goal Seek is a What-If Analysis command. The formula…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.