
VLOOKUP Multiple Results: How to Return All Duplicate Values in Excel (Complete Guide for 365 and 2019)
When looking up data in Excel, you may often have data where the same key (such as a customer ID, product code, or employee ID) appears multiple times. However, VLOOKUP always returns only the first result, which can throw off your report. This article provides the most reliable formulas for solving the vlookup multiple results issue by version (365/2021 vs. 2019/2016), including copy-and-paste templates and practical safeguards.
Quick Fix — Solve VLOOKUP Multiple Results in 10 Minutes
- For Excel 365/2021 or later:
=FILTER(반환범위, 조건범위=조건값, "No match") - For Excel 2019/2016: Use
INDEX+SMALLand copy the nth-result formula down. - If you must keep VLOOKUP: Use a helper key and sequence number to look up the first, second, and third results with VLOOKUP.
For more advanced multiple-condition and multiple-result patterns, see these related articles: VLOOKUP Multiple Conditions and Multiple Results: 5 Practical Formulas, Mastering the INDEX MATCH Function, IF Function Multiple Conditions Guide, Free Excel Functions A-to-Z Guide, XLOOKUP vs. VLOOKUP Comparison.
Why VLOOKUP Is Limited for Multiple Results
VLOOKUP searches for one lookup_value in the first column of the table_array and returns one matching row. Therefore, when a key is duplicated, it returns only the first value it finds.
(Recommended) Excel 365/2021: Spill All Results with FILTER
Sample Data (Copy and Paste as Is)
| OrderID | Customer | Product | Amount |
|---|---|---|---|
| 1001 | C-01 | Hoodie | 59000 |
| 1002 | C-02 | Tee | 19000 |
| 1003 | C-01 | Pants | 49000 |
| 1004 | C-03 | Hoodie | 59000 |
| 1005 | C-01 | Cap | 15000 |
| 1006 | C-02 | Pants | 49000 |
| 1007 | C-01 | Tee | 19000 |
| 1008 | C-03 | Cap | 15000 |
| 1009 | C-02 | Hoodie | 59000 |
| 1010 | C-01 | Socks | 5000 |
Format as a table (recommended): Select A1:D11 → press Ctrl+T → select “My table has headers” → click OK.
Multiple Row Results (Amount List)
=FILTER(D2:D11, B2:B11=F2, "No match")
Multiple Column Results (Return the Entire Row)
=FILTER(A2:D11, B2:B11=F2, "No match")
Combine Multiple Results in One Cell (TEXTJOIN + FILTER)
=TEXTJOIN(", ", TRUE, FILTER(C2:C11, B2:B11=F2, ""))
Official documentation: VLOOKUP function, FILTER function, TEXTJOIN function.
Older Excel Versions 2016/2019: Return the nth Result with INDEX+SMALL
Enter this in H2 and copy it down:
=IFERROR(
INDEX($D$2:$D$11,
SMALL(
IF($B$2:$B$11=$F$2, ROW($D$2:$D$11)-ROW($D$2)+1),
ROWS($H$2:H2)
)
),
"")
When You Must Use VLOOKUP: Simulate Multiple Results with a Helper Column
Create a Helper Key (Duplicate Sequence Number)
=B2&"|"&COUNTIF($B$2:B2, B2)
Return the First, Second, and Third Results with VLOOKUP
=IFERROR(VLOOKUP($F$2&"|"&ROWS($G$2:G2), $E$2:$F$11, 2, FALSE), "")
Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| VLOOKUP returns only the first value | The function structure returns only one row | 365: FILTER; older versions: INDEX+SMALL |
| FILTER returns #SPILL! | Cells in the spill range contain values | Clear the cells below or to the right |
| There are results, but Excel returns No match | Spaces or formatting mismatch | Use TRIM/CLEAN and standardize with VALUE/TEXT |
| #VALUE! in older versions | Array formula entry issue | Check whether Ctrl+Shift+Enter is required |
Conclusion
vlookup multiple results is not about making VLOOKUP more complicated. It is about recognizing that the data has a one-to-many structure and changing the output format. When possible, return a list with FILTER in Excel 365; for older versions, use INDEX+SMALL to return each nth result. These are the safest approaches.
- VLOOKUP Multiple Conditions and Multiple Results: 5 Practical Formulas
- Mastering the INDEX MATCH Function
- XLOOKUP vs. VLOOKUP Comparison
Subscribe to receive continued updates on practical templates for lookup, data cleanup, and error prevention.