The short answer: VLOOKUP returns the first matching value when duplicates exist in the lookup column, so it does not show all duplicates—it shows the first one it finds. To handle duplicates properly, you need to either identify them, sum or average them, or return all matches using a different function.
Why VLOOKUP Behaves This Way
VLOOKUP searches down the first column of a table until it finds a value that exactly matches (or approximately, if range_lookup is TRUE) the lookup value. When it finds a match, it immediately returns the corresponding value from the column you specify, then stops searching. This means if your lookup column has duplicate values, VLOOKUP will always return the first occurrence, ignoring any later duplicates. This is by design—VLOOKUP was built for one-to-one lookups, not one-to-many.
For example, if you have a product list where “Apple” appears in rows 2 and 7, and you use =VLOOKUP("Apple", A2:B10, 2, FALSE), it returns the value from row 2 only, never row 7.
How to Identify Duplicate Values First
Before deciding how to handle duplicates, you need to know if they exist. Excel has built-in tools for this:
- Conditional Formatting: Select the column, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This visually marks duplicates.
- COUNTIF formula: In a helper column, use
=COUNTIF(A:A, A2). If the result is greater than 1, the value in A2 appears more than once. - Remove Duplicates: If you want to clean the data, select the range, go to Data > Remove Duplicates. This permanently deletes duplicate rows, so save a backup first.
Once you know duplicates exist, you can choose the right approach.
Returning All Matches for Duplicates
If you need to list every matching row, VLOOKUP won’t work. Instead, use the FILTER function (available in Excel 2021 and Microsoft 365). For example, with lookup values in A2:A10 and results in B2:B10, and the target in D1, use:
=FILTER(B2:B10, A2:A10=D1)
This returns a spilled array of all values in B where the corresponding A equals D1. If no matches, it returns a #CALC! error; you can wrap it with IFERROR to show a message: =IFERROR(FILTER(B2:B10, A2:A10=D1), "No matches").
For older Excel versions without FILTER, you can use an array formula with INDEX and SMALL, but it’s complex. A simpler alternative is to use a pivot table to list all occurrences.
Summing or Averaging Duplicate Values
Often you don’t need each duplicate row—you need a total or average. Use SUMIF or AVERAGEIF instead of VLOOKUP. For example, with products in A2:A10 and sales in B2:B10, and the product name in D1:
=SUMIF(A2:A10, D1, B2:B10) returns total sales for that product across all duplicate rows.
=AVERAGEIF(A2:A10, D1, B2:B10) returns the average.
These functions naturally handle duplicates by including every matching row.
When to Use VLOOKUP vs. INDEX/MATCH vs. XLOOKUP
If you need only the first match, VLOOKUP works fine. But for more control, consider:
- INDEX/MATCH: Allows lookup columns to the left of the return column, and it’s slightly faster on large datasets. Formula:
=INDEX(B2:B10, MATCH(D1, A2:A10, 0))—still returns the first match. - XLOOKUP (Excel 2021/365): Simpler syntax and can return the first or last match.
=XLOOKUP(D1, A2:A10, B2:B10)returns the first; add the optional match_mode argument to change behavior. XLOOKUP also has a built-in error handler:=XLOOKUP(D1, A2:A10, B2:B10, "Not found").
None of these return all duplicates—only FILTER does that.
Common Problems and Misconceptions
A frequent misconception is that VLOOKUP with range_lookup TRUE (approximate match) will handle duplicates differently. It doesn’t—it still returns the first match. Another issue: if your lookup column has leading or trailing spaces, VLOOKUP may fail to match. Use TRIM to clean data: =VLOOKUP(TRIM(D1), A2:B10, 2, FALSE).
Another common problem is that VLOOKUP returns #N/A when the lookup value isn’t found. Wrap with IFERROR: =IFERROR(VLOOKUP(D1, A2:B10, 2, FALSE), "Not found").
Also, be aware that VLOOKUP with range_lookup TRUE requires the first column to be sorted ascending; otherwise you get incorrect results. For exact matches, always use FALSE.
Alternatives for Complex Duplicate Handling
If you need to return the last duplicate instead of the first, use LOOKUP with a clever trick: =LOOKUP(2, 1/(A2:A10=D1), B2:B10). This returns the last match. Or use XLOOKUP with match_mode set to -1 (exact match or next smaller) but that’s not the same.
For counting duplicates, use COUNTIF: =COUNTIF(A2:A10, D1) tells you how many times the value appears.
If you need to extract a unique list of lookup values, use UNIQUE: =UNIQUE(A2:A10). Then you can VLOOKUP each unique value to get its first occurrence.
Frequently Asked Questions
Does VLOOKUP return all duplicates or just the first? Just the first. VLOOKUP stops at the first exact match and returns that row’s value. To get all matches, use FILTER or a pivot table.
How do I sum all duplicate values in Excel? Use SUMIF. For example, =SUMIF(A2:A10, D1, B2:B10) sums all values in B where A equals the lookup value in D1.
Why is my VLOOKUP returning #N/A even though the value exists? Common causes: the lookup value isn’t exactly the same (extra spaces, different text case—though VLOOKUP is case-insensitive), the column index is wrong, or the range doesn’t include the lookup column. Use TRIM and check your range.
Can I use VLOOKUP to find the last duplicate? Not directly. Use the LOOKUP trick: =LOOKUP(2, 1/(A2:A10=D1), B2:B10) returns the last match. Or use XLOOKUP with the search_mode argument to search from last to first (search_mode -1).
What’s the difference between VLOOKUP and XLOOKUP for duplicates? XLOOKUP also returns the first match by default, but it has options to search from the end or to return all matches (with the FILTER function). It also handles errors more gracefully and doesn’t require the lookup column to be the first column.
Related
- Excel Budget Report Template: How to Build One
- Excel Budget Calendar Template: How to Build One
- Excel Sum Only Numbers Ignore Text in Cell?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SPLIT Function Usage in ExcelThe SPLIT function does not exist in Excel; the correct way to split text in Excel is to…
- Select Multiple Names in Excel Filter: A Practical GuideTo show rows for several names in an Excel filter, open the filter arrow in the names column,…
- Excel Month to Date Average FormulaThe simplest Excel formula for a month-to-date average is =AVERAGEIFS(values, dates, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), dates, "="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A2:A100, "="&DATE(YEAR(D1),MONTH(D1),1), A2:A100, "="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),…
- XLOOKUP Case Sensitivity: How Excel Matches Text ValuesNo. In Excel for Microsoft 365, XLOOKUP is not case-sensitive: it treats text such as “Maple,” “MAPLE,” and…
- Excel Running Balance Formula: Step-by-Step GuideThe simplest Excel formula for a running balance is =SUM($B$2:B2) for a cumulative total of deposits/withdrawals, or =C2+D2-E2…
- Excel Filter Not Showing All Data? Find the Cause FastExcel filters usually miss rows because the filter range stops before the full dataset, or because the values…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.