How to Return All Values That Match Criteria in Excel?

Sharing is caring!

Microsoft Excel is widely used for organizing, analyzing, and filtering data. A common task users encounter is extracting all values that match specific criteria. This task can’t always be completed with basic formulas like VLOOKUP or IF, especially when you want multiple results instead of just one.

In this guide, you’ll learn how to return all matching values using different Excel methods like FILTER, INDEX, SMALL, IF, and array formulas. These techniques work across various use cases, from data analysis to report generation.

Why You Might Need to Return All Matching Values

There are many practical reasons why you might need to return multiple results:

• Listing all orders for a specific customer
• Getting all employees from a certain department
• Extracting products sold in a specific region
• Pulling all dates matching a condition

Traditional formulas like VLOOKUP or INDEX/MATCH return only the first match. To return all values, we need to use dynamic array formulas, helper columns, or Power Query.

Method 1: Use FILTER Function (Excel 365 & Excel 2021)

If you’re using Excel 365 or Excel 2021, the FILTER function is the easiest and most efficient method.

Syntax of FILTER

=FILTER(array, include, [if_empty])

• array: The range to return results from
• include: The condition to match
• if_empty: Optional value to show if no results found

Example: Return All Sales by a Specific Salesperson

Formula:

=FILTER(A2:C5, A2:A5="John")

Result:

This returns all rows where the Salesperson is John.

Method 2: Use INDEX and SMALL with Array Formula (For Older Excel Versions)

For Excel versions prior to 365/2021, you can use INDEX, SMALL, IF, and ROW inside an array formula.

Suppose your data is in A2:A10 (Names) and B2:B10 (Scores). You want to get all scores where Name = “Alice”.

Step 1: Create a Helper Column (optional but useful)

In C2, enter:

=IF(A2="Alice",ROW(A2)-ROW(A$2)+1,"")

Drag it down. This gives you a relative row number where the match is found.

Step 2: Use INDEX and SMALL to Return Matching Values

In E2 (output column), enter:

=IFERROR(INDEX(B$2:B$10,SMALL(IF(A$2:A$10="Alice",ROW(A$2:A$10)-ROW(A$2)+1),ROW(1:1))),"")

Press Ctrl + Shift + Enter (for array entry) if you’re not using Excel 365.

Drag the formula down to get multiple results.

Method 3: Use Advanced Filter (Manual Way)

If you prefer a manual, UI-based method without formulas, Advanced Filter is a built-in feature in Excel.

1. Select your data range (e.g., A1:C10)
2. Go to Data > Advanced
3. In the dialog box:
   • Choose “Copy to another location”
   • Set criteria range (e.g., E1:E2 where E1 is “Region” and E2 is “East”)
   • Set “Copy to” (e.g., G1)
4. Click OK

It will return only matching records.

Method 4: Use Power Query to Extract Matching Values

Power Query is powerful for transforming and filtering data.

How to Use It:

1. Select your data and go to Data > Get & Transform > From Table/Range
2. In Power Query Editor:
   • Use the filter dropdown to choose your matching criteria
   • Click Close & Load to send results back to Excel

You can easily refresh this when your data changes.

Method 5: Use TEXTJOIN to Concatenate Matching Results

Sometimes, instead of listing values in rows, you might want to combine them into a single cell. The TEXTJOIN function works well for this.

Example: Combine All Products in Category “A”

Formula:

=TEXTJOIN(", ", TRUE, IF(B2:B5="A", A2:A5, ""))

Press Ctrl + Shift + Enter if not in Excel 365.

Result: Pen, Pencil

This is useful when you want summary-style results in one cell.

Comparison of Methods to Return All Values That Match Criteria

Tips for Working with Matching Criteria

• Use named ranges to make formulas easier to read.
• Always sort and clean your data before applying formulas.
• Try to avoid hardcoding values (e.g., “John”); use a cell reference instead for flexibility.
• For case-sensitive matches, use functions like EXACT inside your formula.

Example: Return All Orders by Customer Name

To get all orders by Alice, use:

=FILTER(A2:C5, B2:B5="Alice")

Or with a cell reference for flexibility:

=FILTER(A2:C5, B2:B5=E1)

Where E1 contains “Alice”.

Use Named Ranges for Cleaner Formulas

Instead of using A2:A100, create a named range like CustomerList and use it:

=FILTER(SalesData, CustomerList=G1)

This improves readability and reduces errors.

Handling Multiple Criteria

If you want to return rows that match more than one condition, you can use the * (AND) or + (OR) logic inside FILTER.

Example: Return All Sales by John in East Region

=FILTER(A2:C10, (A2:A10="John")*(B2:B10="East"))

This acts as an AND condition.

Handling Errors When No Match is Found

By default, if no results match in FILTER, Excel will return #CALC!. You can customize this using the third argument.

=FILTER(A2:C10, A2:A10="Derek", "No matches found")

This makes your workbook cleaner and more user-friendly.

Summary

Returning all values that match criteria in Excel is a vital skill for data analysis, reporting, and automation. Depending on your Excel version, you can choose between:

• FILTER for dynamic and simple solutions
• Array formulas using INDEX, SMALL, and IF for older versions
• Power Query for structured, refreshable queries
• Advanced Filter for quick manual results
• TEXTJOIN for joining matching results into one cell

Each method has its strengths, so choose based on your needs and the Excel version you’re using. Mastering these will make you more efficient at handling complex data tasks in Excel.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.