Retrieving multiple matching records in Excel often frustrates analysts, as traditional lookup tools only return the first result. When tracking complex organizational assets, relying solely on standard funding sources like basic operational budgets can limit your data visibility.

Utilizing the modern FILTER function grants you the power to dynamically extract every matching entry instantly. Under the stipulation that this feature requires Excel 365 or 2021, this method entirely eliminates manual sorting. For example, isolating all active grants assigned to "Community Development" serves to prove its efficiency.

Below, we outline the exact formula syntax and step-by-step implementation guide.

Excel Formula to Lookup Value and Return Multiple Matches

If you have worked with Excel for any length of time, you are likely familiar with lookup functions like VLOOKUP, HLOOKUP, or the more modern XLOOKUP. While these functions are incredibly powerful for daily data tasks, they all share a common limitation: they only return the first matching value they encounter.

But what happens when you need to find a lookup value that appears multiple times in your dataset and return all matching records? For instance, listing all sales transactions for a specific representative, or pulling all projects assigned to a particular department.

In this comprehensive guide, we will explore the best formulas and techniques to look up a value and return multiple matches in Excel, catering to both modern Microsoft 365 users and legacy Excel users.

The Dataset Example

To keep our examples clear and consistent, we will use the following sample dataset containing regional sales data (range A1:C10):

Our goal is to look up "Alice" or the region "East" and extract all matching rows dynamically.

Method 1: The Modern & Easiest Way – The FILTER Function

If you are using Microsoft 365 or Excel 2021 and newer, you have access to Dynamic Arrays. The absolute best, fastest, and most elegant solution to return multiple matches is the FILTER function.

The Syntax

=FILTER(array, include, [if_empty])

• array: The range of cells you want to extract data from.
• include: The logical condition(s) to filter by.
• [if_empty]: (Optional) The value to return if no matches are found.

Step-by-Step Example

Suppose you want to extract all rows where the Sales Rep is "Alice". Write the following formula in an empty cell where you want your results to begin (e.g., cell E2):

=FILTER(A2:C10, A2:A10="Alice", "No Records Found")

How It Works

Because of Excel's Dynamic Array engine, you write this formula in a single cell, and it automatically "spills" the matching values down and across into neighboring cells. If "Alice" has three matching rows, Excel will automatically fill three rows of data.

Pro-Tip: If you only want to return the Sales Amount column instead of the entire table, narrow your array range like this:

=FILTER(C2:C10, A2:A10="Alice", "No Records Found")

Method 2: Returning Multiple Matches in a Single Cell (Using TEXTJOIN and FILTER)

Sometimes, you don't want your results to spill into separate rows. Instead, you might want to look up a value and return all matching results as a comma-separated list inside a single cell.

By nesting the FILTER function inside the TEXTJOIN function, we can easily achieve this in modern Excel versions.

The Formula

To list all Sales Reps working in the "East" region inside a single cell, use:

=TEXTJOIN(", ", TRUE, FILTER(A2:A10, B2:B10="East"))

Breaking Down the Formula

1. FILTER(A2:A10, B2:B10="East"): Finds all instances in column B containing "East" and returns the corresponding names in column A (Alice, Charlie, Alice, Charlie).
2. TEXTJOIN(", ", TRUE, ...): Takes those returned values, ignores any empty cells (due to the TRUE argument), and concatenates them into a single text string separated by a comma and a space.

Result: Alice, Charlie, Alice, Charlie

Method 3: The Classic Legacy Way (For Excel 2019, 2016, and Older)

If you are working on an older version of Excel that doesn't support Dynamic Arrays or the FILTER function, you have to use a classic, more complex array formula combining INDEX, SMALL, IF, and ROW.

Note: If you are using Excel 2019 or older, you must press Ctrl + Shift + Enter instead of just Enter when finishing this formula. This turns it into an array formula, wrapping it in curly braces { }.

The Formula

Assuming your lookup value ("East") is typed in cell E1, paste this formula in cell F2, press Ctrl + Shift + Enter, and drag it down to the rows below:

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

How This Formula Works Step-by-Step

This formula seems intimidating, but it can be understood when broken down from the inside out:

1. ROW($B$2:$B$10)-ROW($B$2)+1: This creates an array of relative row numbers within our data range, starting from 1 to 9: {1; 2; 3; 4; 5; 6; 7; 8; 9}.
2. IF($B$2:$B$10=$E$1, ...): This checks which cells in our region column match "East". If true, it returns the relative row number from Step 1. If false, it returns FALSE. The output looks like: {1; FALSE; 3; FALSE; 5; FALSE; 7; FALSE; FALSE}.
3. ROW(1:1): This acts as an incrementing counter. In the first row, ROW(1:1) evaluates to 1. When dragged down to the next row, it becomes ROW(2:2) which evaluates to 2, and so on.
4. SMALL(..., ROW(1:1)): The SMALL function returns the k-th smallest value from our array. In our first row, it returns the 1st smallest numeric value (which is 1). Dragged down, it returns the 2nd smallest (which is 3), then the 3rd smallest (5), and so on.
5. INDEX($A$2:$A$10, ...): The index function retrieves the value from the Sales Rep column (column A) at the row number calculated by the SMALL function. Row 1 yields "Alice", Row 3 yields "Charlie", and so forth.
6. IFERROR(..., ""): Once Excel runs out of matching values, the SMALL function will throw a #NUM! error. IFERROR catches this error and outputs a clean, blank cell.

Summary: Which Method Should You Use?

Depending on your version of Excel and your output layout requirements, use this quick reference table to choose the best method:

Conclusion

While Excel's traditional lookup functions limit you to a single matching value, you now have the tools to easily conquer multiple match scenarios. If you are fortunate enough to be using Microsoft 365, lean heavily on the FILTER and TEXTJOIN functions-they are faster to calculate, easier to write, and much simpler to maintain. If you are stuck in legacy versions, the INDEX/SMALL array formula is a reliable classic that will get the job done without requiring complex VBA code.