How to Use the FILTER Function for Complete and Partial Text Match in Excel

Jun 26, 2025

—

by

The FILTER function in Excel 365 is a powerful tool that allows you to extract rows from a dataset that meet specific criteria. It dynamically spills the matching results into adjacent cells — and is ideal for creating reports, dashboards, and searchable tables.

🧠 FILTER Function Syntax:

excelCopyEdit=FILTER(array, include, [if_empty])

array: The range you want to filter.
include: A logical expression that determines which rows to return.
if_empty: (Optional) Value to return if no match is found.

✅ 1. Complete Match of a Text String

📘 Scenario:

You have a list of employees and want to extract all rows where the department is exactly “HR”.

📊 Sample Data:

Name	Department
Rohan	HR
Meena	Sales
Aarti	HR
Nikhil	IT

📄 Formula:

excelCopyEdit=FILTER(A2:B5, B2:B5 = "HR", "No match found")

🟢 Output:

Name	Department
Rohan	HR
Aarti	HR

✅ Exact/complete matches only — cells must exactly equal "HR".

🔍 2. Partial Match of a Text String

📘 Scenario:

You want to filter all rows where the department contains the word “Sales”, including partial terms like “Sales – North”, “Sales Team”, etc.

📊 Sample Data:

Name	Department
Rohan	HR
Meena	Sales – East
Aarti	HR
Nikhil	Sales Team

📄 Formula:

excelCopyEdit=FILTER(A2:B5, ISNUMBER(SEARCH("Sales", B2:B5)), "No match")

🟢 Output:

Name	Department
Meena	Sales – East
Nikhil	Sales Team

✅ SEARCH enables case-insensitive partial matching, and ISNUMBER checks whether the text was found.

💡 Tip:

Use SEARCH("text", cell) for partial, case-insensitive match.
Use FIND("text", cell) for partial, case-sensitive match.

🎯 When to Use Complete vs Partial Match?

Use Case	Match Type	Formula Logic
Filter all “IT” records only	Complete	`B2:B10 = "IT"`
Filter names that contain “Raj”	Partial	`ISNUMBER(SEARCH("Raj", A2:A10))`

🚀 Take Filtering Further with Excel VBA

If you’re impressed by what Excel formulas can do, imagine being able to automate these filters, build custom forms, and generate filtered reports with one click.

With Excel VBA, you can do all this and more — turning hours of manual work into minutes.

🎓 Learn to Automate Excel with VBA – Without Prior Coding

📘 Mastering Excel Automation – Excel VBA Training Course

✅ What You’ll Learn:

Automate repetitive tasks
Build dynamic reports
Create custom filters, buttons, and user forms
Use loops, conditions, and functions to control your spreadsheets

🎥 42 structured videos
🕒 4 hours 8 minutes of hands-on content
💰 Limited-time price: ₹441 (was ₹1,299)
📚 Lifetime access – Learn at your own pace