VLOOKUP Multiple Results: How to Return All Duplicate Values in Excel

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

  1. For Excel 365/2021 or later: =FILTER(반환범위, 조건범위=조건값, "No match")
  2. For Excel 2019/2016: Use INDEX+SMALL and copy the nth-result formula down.
  3. 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)

OrderIDCustomerProductAmount
1001C-01Hoodie59000
1002C-02Tee19000
1003C-01Pants49000
1004C-03Hoodie59000
1005C-01Cap15000
1006C-02Pants49000
1007C-01Tee19000
1008C-03Cap15000
1009C-02Hoodie59000
1010C-01Socks5000

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

SymptomCauseSolution
VLOOKUP returns only the first valueThe function structure returns only one row365: FILTER; older versions: INDEX+SMALL
FILTER returns #SPILL!Cells in the spill range contain valuesClear the cells below or to the right
There are results, but Excel returns No matchSpaces or formatting mismatchUse TRIM/CLEAN and standardize with VALUE/TEXT
#VALUE! in older versionsArray formula entry issueCheck 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.

Subscribe to receive continued updates on practical templates for lookup, data cleanup, and error prevention.

Leave a Reply

Your email address will not be published. Required fields are marked *