Extracting Multiple Matching Values in Excel – 3 Smart Methods You Should Know!

𝗜𝗻𝘁𝗿𝗼𝗱𝘂𝗰𝘁𝗶𝗼𝗻:

Imagine you’re handling a 𝗹𝗮𝗿𝗴𝗲 𝗱𝗮𝘁𝗮𝘀𝗲𝘁 𝗶𝗻 𝗘𝘅𝗰𝗲𝗹, 𝗮𝗻𝗱 𝘆𝗼𝘂 𝗻𝗲𝗲𝗱 𝘁𝗼 𝗲𝘅𝘁𝗿𝗮𝗰𝘁 𝗮𝗹𝗹 𝘃𝗮𝗹𝘂𝗲𝘀 𝘁𝗵𝗮𝘁 𝗺𝗮𝘁𝗰𝗵 𝗮 𝘀𝗽𝗲𝗰𝗶𝗳𝗶𝗰 𝗰𝗿𝗶𝘁𝗲𝗿𝗶𝗼𝗻. Manually filtering them out is tedious and time-consuming. Fortunately, Excel offers powerful formulas to automate this task. Whether you’re using the latest Excel 365 or an older version, here are three effective methods to extract multiple matching values easily.

1. 𝗧𝗵𝗲 𝗙𝗜𝗟𝗧𝗘𝗥 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻 – 𝗧𝗵𝗲 𝗘𝗮𝘀𝗶𝗲𝘀𝘁 𝗪𝗮𝘆 (𝗘𝘅𝗰𝗲𝗹 𝟯𝟲𝟱/𝟮𝟬𝟭𝟵)

If you’re using Excel 365 or Excel 2019, the FILTER function is your best friend. It allows you to dynamically pull all matching values from a dataset.

Use Cases:

🎄Extracting all sales figures for a specific product.

🎄Filtering customer complaints related to a particular issue.

🎄Listing all invoices issued to a specific client.

Formula:

=𝗙𝗜𝗟𝗧𝗘𝗥(𝗕𝟰:𝗕𝟳, 𝗔𝟰:𝗔𝟳=𝗘𝟭, "𝗡𝗼 𝗠𝗮𝘁𝗰𝗵")

Explanation:

1. A4:A7 contains the criteria range (e.g., product names).

2. B4:B7 is the column from which values will be extracted.

3. E1 holds the lookup value (e.g., "Apple").

4. "No Match" is returned if no matches are found.

𝗣𝗿𝗼𝘀 & 𝗖𝗼𝗻𝘀:

✅ Dynamic and updates automatically.

✅ Easy to use and implement.

❌ Only available in Excel 365/2019.

2. 𝗧𝗵𝗲 𝗜𝗡𝗗𝗘𝗫-𝗦𝗠𝗔𝗟𝗟 𝗙𝗼𝗿𝗺𝘂𝗹𝗮 – 𝗕𝗲𝘀𝘁 𝗳𝗼𝗿 𝗢𝗹𝗱𝗲𝗿 𝗩𝗲𝗿𝘀𝗶𝗼𝗻𝘀

If you’re using Excel 2016, 2013, or earlier, the FILTER function isn’t available. Instead, you can use an INDEX-SMALL combination to extract multiple values.

𝗨𝘀𝗲 𝗖𝗮𝘀𝗲𝘀:

🎄Extracting employee names who belong to a specific department.

🎄Listing all transactions for a given vendor.

🎄Finding all product SKUs under a specific category.

𝗙𝗼𝗿𝗺𝘂𝗹𝗮 (𝗔𝗿𝗿𝗮𝘆 𝗙𝗼𝗿𝗺𝘂𝗹𝗮 - 𝗣𝗿𝗲𝘀𝘀 𝗖𝘁𝗿𝗹 + 𝗦𝗵𝗶𝗳𝘁 + 𝗘𝗻𝘁𝗲𝗿):

=IFERROR(INDEX($B$4:$B$7, SMALL(IF($A$4:$A$7=$E$1, ROW($A$4:$A$7)-ROW($A$2)+1), ROWS($F$2:F2))),"")

Explanation:

1. IF($A$2:$A$100=$E$1, ROW($A$2:$A$100)-ROW($A$2)+1): Finds matching row numbers.

2. SMALL(..., ROWS($F$2:F2)): Extracts the smallest row number, incrementing as the formula is dragged down.

3. INDEX($B$2:$B$100, ...): Retrieves values from column B based on found row numbers.

4. IFERROR(...,""): Returns an empty string if no match is found.

𝗣𝗿𝗼𝘀 & 𝗖𝗼𝗻𝘀:

✅ Works in older Excel versions.

✅ Can extract multiple values dynamically.

❌ More complex than FILTER.

❌ Requires Ctrl + Shift + Enter to function correctly.

3. 𝗧𝗘𝗫𝗧𝗝𝗢𝗜𝗡 – 𝗔𝗹𝗹 𝗠𝗮𝘁𝗰𝗵𝗲𝘀 𝗶𝗻 𝗮 𝗦𝗶𝗻𝗴𝗹𝗲 𝗖𝗲𝗹𝗹 (𝗘𝘅𝗰𝗲𝗹 𝟮𝟬𝟭𝟵/𝟯𝟲𝟱)

If you want all matching values in a single cell instead of multiple rows, TEXTJOIN is a great option.

Use Cases:

🎄Listing all project names handled by a specific manager in one cell.

🎄Combining all products purchased by a customer into one field.

🎄Merging all email addresses of employees in a department into a single string.

𝗙𝗼𝗿𝗺𝘂𝗹𝗮:

=TEXTJOIN(", ", TRUE, FILTER(B2:B100, A2:A100=E1, "No Match"))

𝗘𝘅𝗽𝗹𝗮𝗻𝗮𝘁𝗶𝗼𝗻:

FILTER(B2:B100, A2:A100=E1, "No Match"): Extracts all matching values.

TEXTJOIN(", ", TRUE, ...): Joins extracted values with a comma and space separator.

TRUE: Ignores empty cells.

Pros & Cons:

✅ Ideal for creating summaries.

✅ Eliminates the need for multiple rows.

❌ Limited to Excel 2019/365.

❌ Can become difficult to read if the dataset is too large.

Final Thoughts

No matter which version of Excel you’re using, there’s a method to extract multiple matching values efficiently:

𝗨𝘀𝗲 𝗙𝗜𝗟𝗧𝗘𝗥 𝗶𝗳 𝘆𝗼𝘂 𝗵𝗮𝘃𝗲 𝗘𝘅𝗰𝗲𝗹 𝟯𝟲𝟱/𝟮𝟬𝟭𝟵.

𝗨𝘀𝗲 𝗜𝗡𝗗𝗘𝗫-𝗦𝗠𝗔𝗟𝗟 𝗶𝗳 𝘆𝗼𝘂’𝗿𝗲 𝗼𝗻 𝗮𝗻 𝗼𝗹𝗱𝗲𝗿 𝗘𝘅𝗰𝗲𝗹 𝘃𝗲𝗿𝘀𝗶𝗼𝗻.

𝗨𝘀𝗲 𝗧𝗘𝗫𝗧𝗝𝗢𝗜𝗡 𝘄𝗵𝗲𝗻 𝘆𝗼𝘂 𝗻𝗲𝗲𝗱 𝗮𝗹𝗹 𝘃𝗮𝗹𝘂𝗲𝘀 𝗶𝗻 𝗼𝗻𝗲 𝗰𝗲𝗹𝗹.

These techniques can 𝘀𝗮𝘃𝗲 𝘆𝗼𝘂 𝗵𝗼𝘂𝗿𝘀 𝗼𝗳 𝗺𝗮𝗻𝘂𝗮𝗹 𝘄𝗼𝗿𝗸 𝗮𝗻𝗱 𝗺𝗮𝗸𝗲 𝘆𝗼𝘂𝗿 𝗱𝗮𝘁𝗮 𝗮𝗻𝗮𝗹𝘆𝘀𝗶𝘀 𝗺𝗼𝗿𝗲 𝗲𝗳𝗳𝗶𝗰𝗶𝗲𝗻𝘁. 𝗧𝗿𝘆 𝘁𝗵𝗲𝗺 𝗼𝘂𝘁 𝗮𝗻𝗱 𝗹𝗲𝘃𝗲𝗹 𝘂𝗽 𝘆𝗼𝘂𝗿 𝗘𝘅𝗰𝗲𝗹 𝘀𝗸𝗶𝗹𝗹𝘀! 🚀

👉 Which method do you use the most? Let me know in the comments!