When you use VLOOKUP on a column that contains duplicate lookup values, it returns the first match it finds, reading top to bottom, and ignores all later duplicates. This is by design, but it often surprises users who expect all matches or the last match. To handle duplicates properly, you need to change your formula, add helper columns, or use a different function like XLOOKUP, FILTER, or INDEX/MATCH.
How VLOOKUP Handles Duplicates by Default
VLOOKUP searches the first column of a table for a lookup value and returns a value from a column you specify in the same row. The function stops at the very first exact match (or approximate match if you use TRUE). It does not continue scanning for other occurrences. For example, if column A contains the names “Alice”, “Bob”, “Alice”, and you look up “Alice”, VLOOKUP will return the value from the row with the first “Alice” (row 2) and ignore the second one in row 3.
This behavior is consistent with the way VLOOKUP was designed: it assumes the lookup column contains unique identifiers, like product IDs or employee numbers. When that assumption is violated, the results can be misleading, especially if you need to see all matching records or the last occurrence.
Why You Might Need to Handle Duplicates
Duplicate lookup values are common in real-world data. For instance, a sales log may have multiple transactions for the same customer name, an inventory sheet may list the same item code in several rows, or a roster may have duplicate employee names. If you simply use VLOOKUP, you’ll only get the first transaction, the first inventory entry, or the first person with that name. That can be a serious problem when you need a complete list of all orders for a customer, a total quantity for an item, or a way to identify the most recent entry.
Depending on your goal, you may want to:
- Return all matching values (e.g., all sales amounts for “Alice”)
- Return the last matching value (e.g., the most recent price change)
- Sum or average all matching values (e.g., total sales per customer)
- Return a unique list of duplicates for further analysis
Each goal requires a different approach, and plain VLOOKUP only covers the first-match case.
Using Helper Columns to Force VLOOKUP to Work with Duplicates
One classic workaround is to add a helper column that makes each lookup value unique. You can do this by concatenating the lookup value with a running count of occurrences. Here’s how:
- Add a new column (say column C) next to your data.
- In C2, enter the formula
=A2&COUNTIF($A$2:A2,A2)and copy it down. This creates values like “Alice1”, “Alice2”, etc. - Now use VLOOKUP with the helper column. For the lookup value, you need to build the same concatenated key. For example, to get the second “Alice”, use
=VLOOKUP("Alice"&2, C2:D10, 2, FALSE).
This method works, but it requires you to know which occurrence number you want. If you want all matches, you’d have to repeat the formula for each occurrence. A more flexible approach is to use array formulas or newer functions.
Better Alternatives: XLOOKUP, FILTER, and INDEX/MATCH
If you have Excel 2021 or Microsoft 365, you have access to functions that handle duplicates far more elegantly.
XLOOKUP works like VLOOKUP but also has an optional if_not_found argument and a match_mode that can find the next larger or exact match. However, by default XLOOKUP also returns the first match. To get the last match, you can use the search_mode argument set to -1 (search from last to first). For example, with names in A2:A10 and values in B2:B10, to get the last value for “Alice”:
=XLOOKUP("Alice", A2:A10, B2:B10, "not found", 0, -1)
This returns the value from the last row that has “Alice”.
FILTER is the go-to for returning all matches. It spills all matching values into adjacent cells. For example, to list all sales amounts for “Alice”:
=FILTER(B2:B10, A2:A10="Alice")
This will spill down automatically. If you want the result as a single cell (like a comma-separated list), you can wrap it with TEXTJOIN: =TEXTJOIN(", ", TRUE, FILTER(B2:B10, A2:A10="Alice")).
INDEX/MATCH is an older but still powerful alternative. To get the last match, you can use an array formula like =INDEX(B2:B10, MATCH(2, 1/(A2:A10="Alice"))) and press Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365. This works by finding the position of the last “Alice” in the array.
Common Problems and Misconceptions
One common misconception is that VLOOKUP can be made to return all matches by simply dragging the formula down. That doesn’t work because VLOOKUP always returns the first match, so every dragged copy returns the same value. Another misconception is that sorting the data will change which duplicate is returned. Sorting does affect which row is considered “first” because VLOOKUP scans from the top, so if you sort descending, the “first” match might be the last one in the original order. That can be a quick hack, but it’s fragile and not recommended.
Another frequent problem is that VLOOKUP with duplicates may return incorrect results when using approximate match (TRUE). With duplicates, approximate match can return the wrong row if the lookup column isn’t sorted, and duplicates can cause unpredictable behavior. Always use FALSE for exact match unless you specifically need an approximate match and your data is sorted.
How to Choose the Right Method
Your choice depends on what you need:
- First match – Use VLOOKUP or XLOOKUP with default settings.
- Last match – Use XLOOKUP with search_mode -1, or an INDEX/MATCH array formula.
- All matches – Use FILTER (Excel 365/2021) or a pivot table, or a helper column with a running count and then use a series of VLOOKUPs.
- Sum or average of all matches – Use SUMIF, SUMIFS, AVERAGEIF, or AVERAGEIFS. For example,
=SUMIF(A2:A10, "Alice", B2:B10)adds all values for Alice. - Unique list of duplicates – Use UNIQUE function to list distinct lookup values, then use COUNTIF to see how many times each appears.
For most cases, FILTER is the cleanest solution for listing all matches, and SUMIFS is the best for aggregating.
Frequently Asked Questions
Why does VLOOKUP only return the first match?
VLOOKUP is designed to find a single value in a table. It stops scanning at the first exact match, because it assumes the lookup column contains unique keys. That’s why it’s important to use a unique identifier, or switch to a function like FILTER if you need all matches.
Can I make VLOOKUP return the last match instead?
Yes, but not with VLOOKUP alone. You can use XLOOKUP with the search_mode argument set to -1, as shown above. Alternatively, you can use an array formula with INDEX and MATCH that finds the last occurrence.
How do I get all duplicate matches in separate cells?
Use the FILTER function. For example, =FILTER(B2:B10, A2:A10="Alice") will spill all matching values down the column. This requires Excel 2021 or Microsoft 365. If you have an older version, you can use a helper column with a running count and then use INDEX/SMALL to extract each match.
What if I want to sum all values for a duplicate lookup value?
Use SUMIF or SUMIFS. For instance, =SUMIF(A2:A10, "Alice", B2:B10) returns the total of all B values where A equals “Alice”. This works in all versions of Excel.
Does sorting the data help VLOOKUP with duplicates?
Sorting changes which row is considered first, so it can make VLOOKUP return a different duplicate, but it doesn’t give you all matches. It’s a temporary fix that can break if the data changes. Better to use a function that explicitly handles duplicates.
Can I use VLOOKUP to return a value from a duplicate row that is not the first?
Yes, but you need to add a helper column that makes each row unique, as described earlier. For example, create a column that concatenates the lookup value with a count of occurrences, then use VLOOKUP on that helper column with the occurrence number appended to your lookup value.
Related
- Excel Strikethrough: How to Apply & Remove It
- Excel Utility for E-Invoice: What It Does
- Excel Undo Encrypt with Password? How to Remove It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort an Excel Column Alphabetically Without Mixing RowsTo sort a column alphabetically in Excel for Microsoft 365 on Windows, click a cell in the column…
- Lower Quartile Formula in Excel: Find Q1 CorrectlyIn Excel, calculate the lower quartile (Q1) with =QUARTILE.INC(A2:A9,1), replacing A2:A9 with your data range. This returns the…
- Excel Formula to Pick Randomly From a ListWith a contiguous list in A2:A10, use =INDEX(A2:A10,RANDBETWEEN(1,ROWS(A2:A10))) to return one randomly selected item. The result can change…
- When VLOOKUP Does Not Work: Common Causes and FixesVLOOKUP usually fails because its lookup value does not exactly match the first column of the selected range,…
- Excel Formula for Left of CharacterThe simplest Excel formula to extract text to the left of a specific character is =LEFT(A1, FIND("char", A1)…
- Excel Formula to Round to the Nearest Whole NumberTo round a number in Excel to the nearest whole number, use the ROUND function with zero decimal…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.