How to Return Multiple Match Values in Excel (4 Ways)

Free Excel workbook

Get the Multiple Matches Workbook

Download the ready-to-use workbook and practice the exact formulas from the tutorial.

• Practice all 3 ways to return multiple match values in Excel
• See FILTER, TEXTJOIN, and INDEX + AGGREGATE examples already set up
• Follow the tutorial without building the lookup logic from scratch

Enter your email below for instant access.

Get the Practice Workbook

Free. Instant access.

How to Return Multiple Matches with INDEX and AGGREGATE (All Excel Versions)

Why Use INDEX and AGGREGATE for Multiple Matches?

These two functions work together to create a dynamic list of results:

✅ INDEX pulls data from a specified range.
✅ AGGREGATE helps locate the matching rows, even when there are multiple matches.
✅ Works without array formulas. No need to press Ctrl + Shift + Enter!

Step-by-Step: Build the INDEX + AGGREGATE Formula

This method will use the INDEX function with the AGGREGATE function return multiple match values in Excel. It will locate the associated Apps for the selected Division and compile the results into a new list.  We will also integrate an IF test to visually suppress any errors that may show up when the returned items do not fully populate the results list area.

To begin, select cell G4 and enter the Division “Game”.

NOTE:  We could provide the user with a drop-down list to ease the selection process for Division, but for simplicity, we will hardcode the Division name.  For a tutorial on creating unique dropdown lists from existing multi-valued lists, click the link below.

Excel: Extract unique items for dynamic data validation drop down list

The most common function people use when finding items in an Excel list is VLOOKUP.  If you require a refresher on the use of VLOOKUP, click the link below.

Excel VLOOKUP: Basics of VLOOKUP and HLOOKUP explained with examples

The problem with using VLOOKUP in this scenario is that VLOOKUP will always stop on the first encountered matching item in the search list.  If we are searching for “Game”, the VLOOKUP will always stop in cell A7.

We want to build a list where the 1^st occurrence of a “Game” App is placed in cell G5; the 2^nd occurrence is placed in cell G6; the 3^rd in cell G7; etc.

We will use the INDEX and AGGREGATE functions to create this list.  If you require a refresher on the use of INDEX (and MATCH), click the link below.

How to use Excel INDEX MATCH (the right way)

Select cell G5 and begin by creating an INDEX function.

=INDEX(array, row_num, [column_num])

The INDEX function has the following parameters:

• Array = the cells to have items extracted from and returned as answers.
• Row_num = the “up and down” position in the list to move to extract data.
• Column_num = the “left to right” position in the list to move to extract data.

We want to extract App names from cells B5:B14.

=INDEX($B$5:$B$14

We want to return the App named “Fightrr” from the 3^rd position in the App list, so as a test we will hard-code the number “3”.

=INDEX($B$5:$B$14, 3)

If we fill the formula down the cells in column “G”, the App named “Fightrr” appears repeatedly, a behavior like the earlier VLOOKUP results.  We need to find a way to have the row_num’s return value change from “3” to “4” to “5” to “7”.  We cannot simply increase the value of the row-num parameter by 1 every time we repeat the formula; the parameter needs to change based on the position of the associated Division in column “A”.

We will use the AGGREGATE function to generate a list of rows (i.e. positions) where the selected Division (“Game”) is discovered.  The advantage of using the AGGREGATE function is that it can accept multiple answers without the use of CTRL-Shift-Enter.

The AGGREGATE function has the following parameters:

• Function_num = a number corresponding to a function in the AGGREGATE list.

We will use the SMALL function (number 15).

• Options = a number corresponding to a behavior for handling errors, hidden data, and other AGGREGATE and SUBTOTAL functions when mixed with data.

We will use option #3 to ignore all other issues.

• Array = the values to be aggregated.
  We will select cells A5:A14.
• [k] = optional value when using selection functions, like SMALL or LARGE.
  We will save this parameter for later.

TIP: To focus on one problem at a time, we will build the AGGREGATE function off to the side in column “H”.  Once the AGGREGATE function is working to our satisfaction, we will fold the logic of AGGREGATE into the INDEX function.

Select cell H5 and enter the following formula:

=AGGREGATE(15, 3, $A$5:$A$14)

If we highlight the array parameter and press F9, we will see that the supplied array returns the words located in cells A5:A14.

Our objective is not to find the smallest word; we want to know which cells in the array match our selected Division.  To do this, we will test each cell in the array to see if it matches the selected Division.  Modify the array as follows.

=AGGREGATE(15, 3, ($A$5:$A$14=$G$4))

If we highlight the array parameter and press F9, we will see the following test results.

The problem here is that Excel interprets a FALSE response as 0 (zero) and a TRUE response as 1 (one).  If we use the SMALL function for discovery, the 0 will be selected first.  We want to convert the FALSE responses to errors and the TRUE responses to position numbers within the list (i.e. 3, 4, 5, 7).  To do this, we will divide the array by itself.

=AGGREGATE(15, 3, (($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)))

Because 0 ÷ 0 = ERROR and 1 ÷ 1 = 1, we get the following list of responses.

To change the 1’s to position numbers, we will multiply each 1 by its corresponding row number.

=AGGREGATE(15, 3, (($A$5:$A$14=$G$4)/ ($A$5:$A$14=$G$4) * ROW($A$5:$A$14)))

This will provide the following responses.

Because we didn’t start our list on row 1, our positions in the list are offset; in this case by 4 rows.  Since the header for this table is in row 4, we will subtract the header row’s position value from the previous list of answers.

=AGGREGATE(15, 3, (($A$5:$A$14=$G$4) / ($A$5:$A$14=$G$4) * ROW($A$5:$A$14)) -ROW($A$4))

This will provide the following responses.

It’s now time to loop back and utilize the [k] option (the 4^th parameter) of the AGGREGATE function.  We want the SMALL function to increment by one for each copy of the AGGREGATE function.  This will be accomplished by a cleaver trick of counting the cells in an ever-increasing range selection.

=AGGREGATE(15, 3, (($A$5:$A$14=$G$4) / ($A$5:$A$14=$G$4) * ROW($A$5:$A$14)) -ROW($A$4),N)

In this case, “N” will be the following function.

ROWS($F$5:F5)

When used on the first AGGREGATE function, the result will be 1.  By anchoring the beginning of the range to cell $F$5 as an absolute reference and leaving the ending of the range F5 as a relative reference, the range will change its size as the range ending moves further away from the range beginning.

The formula with the intelligent row counter should appear as follows.

=AGGREGATE(15, 3, (($A$5:$A$14=$G$4) / ($A$5:$A$14=$G$4) * ROW($A$5:$A$14)) -ROW($A$4), ROWS($F$5:F5))

Now copy the formula (without the equals sign) from cell H5 and paste it into the row_num parameter of the INDEX function in cell G5.  The updated formula should appear as follows.

Original formula

=INDEX($B$5:$B$14, 3)

Updated formula

=INDEX($B$5:$B$14, AGGREGATE(15, 3, (($A$5:$A$14=$G$4) /($A$5:$A$14=$G$4) *ROW($A$5:$A$14)) -ROW($A$4), ROWS($F$5:F5)))

Fill the formula down through cells G5:G14.  We now have the list of associated Apps to the selected Division as well as errors for when our return list is shorter than our expected maximum list length.

We want to hide the errors.

The simple (read “lazy”) way of hiding the errors is to nest the entire INDEX/AGGREGATE function in an IFERROR function like the following.

=IFERROR(INDEX($B$5:$B$14, AGGREGATE(15, 3, (($A$5:$A$14=$G$4) /($A$5:$A$14=$G$4) *ROW($A$5:$A$14)) -ROW($A$4), ROWS($F$5:F5))), ””)

The problem is that the IFERROR must fully process the INDEX/AGGREGATE function to determine if an error is generated.  This can lead to many wasted CPU cycles when generating long lists of associated Apps.

The better tactic is to use an IF statement to count the number of times the selected Division appears in the list of Divisions and then compare that against the current App list’s length.  If the App list length exceed the number of times the selected Division appears in the original Division list, the IF function will not execute the INDEX/AGGREGATE formula.

To give the IF function something to compare against, select cell F4 and enter the following “helper” formula.

=COUNTIF(A5:A14, G4)

The updated IF function will perform the following test.

=IF(ROWS($F$5:F5)<=$F$4, INDEX/AGGREGATE, "")

This formula will test the ever-expanding range that begins in cell F5 to determine if the range height exceeds the value supplied by the helper cell F4.  The updated formula will appear as follows.

=IF(ROWS($F$5:F5)<=$F$4, INDEX($B$5:$B$14, AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14)) -ROW($A$4), ROWS($F$5:F5))),"")

Because “Game” has 4 entries in column “A”, when we get to the 5^th iteration the ROWS function in the IF function will generate a 5.  The test becomes “5<=4” which generates a FALSE condition, thereby display the empty text supplied by the two double quotes.

How to Remove the Helper Cell from the Formula

If you don’t want to have the “helper” formula in cell F4, you can fold the COUNTIF logic into the IF function.  The updated formula would appear as follows.

=IF(ROWS($F$5:F5)<=COUNTIF($A$5:$A$15,$G$4), INDEX($B$5:$B$14, AGGREGATE(15,3,(($A$5:$A$14=$G$4)/ ($A$5:$A$14=$G$4)*ROW($A$5:$A$14)) -ROW($A$4), ROWS($F$5:F5))),"")

NOTE: When the COUNTIF was in a “helper” cell, we did not make any of the references absolute because we were not repeating the formula across any other cells.  If we fold the COUNTIF logic into the IF function, we need to make the references absolute due to the repeated nature of the formula in column “G”.

The only drawback to this consolidation is that the counting of the selected Division must be repeated for each IF function. In longer lists (i.e. hundreds of thousands of rows), this could negatively impact processing time. 

By performing the COUNTIF as a separate “helper” calculation, it must only be performed once.

Featured Course

Master Excel’s Essential Modern Functions

FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE. The Excel functions most professionals were never taught, and the ones that turn hours of work into minutes.

Learn More

How to Return All Matches in One Cell with TEXTJOIN

If you prefer to see all results in one cell, the TEXTJOIN function is your best bet.

✅ Cleaner, with results in a single cell.
✅ Simpler formula than Method 1.

💡 Note: The TEXTJOIN function is available in Excel 2016 or above and Office 365.

Step-by-Step: Build the TEXTJOIN + IF Formula

The TEXTJOIN function has the following parameters:

• Delimiter – the character that separates the returned values.
  We will create a comma-delimited list, so we will enter “,”.
• Ignore_empty – This determines whether to include any empty cells in the results list.
  We are unsure as to our needs, so we will use FALSE and change it later if needed.
• Text1 – This is the range of cells containing the source data.
  We will select A5:A14.

Begin by selecting cell G4 and replacing the Division “Game” with “Utility”.

Select cell H5 and entering in the following formula.

=TEXTJOIN(“,”,False, B5:B14)

This provides the following result.

Accord,Blend,Fightrr,Hackrr,Kryptis,Misty Wash,Perino,Sleops,Twenty20,WenCaL

We do not want the complete list of Apps, we only want Apps that are associated with the selected Division (cell G4).  To filter the list to only associated Apps, we will perform a logical test, using IF, to compare each of the Apps’ associated Division with the selected Division. Update the formula as follows.  

❗ Remember to finish the formula by pressing Ctrl+Shift+Enter.

=TEXTJOIN(",",FALSE, IF(A5:A14=G4, B5:B14, ""))

This provides the following result.

Accord,,,,,Misty Wash,,,Twenty20,

If we highlight the IF function in the formula and press F9 we will see the following responses.

To remove the empty cell notations, update the formula by changing the Ignore_empty parameter from FALSE to TRUE. Remember to finish the formula by pressing CTRL-Shift-Enter.

=TEXTJOIN(",",TRUE, IF(A5:A14=G4, B5:B14, ""))

This provides the following result.

Accord,Misty Wash,Twenty20

Alternative: Two-Step Solution

If complex formulas aren’t your thing, we’ve got a simpler two-step method to find multiple matches in your data. It breaks down the process into manageable parts. Curious? Click HERE to learn more.

How to Use the FILTER Function to Return Multiple Matches (Recommended)

If you have Microsoft 365 or Excel 2021+, the FILTER function is the simplest way to return all matching values. One formula, no helper columns, no Ctrl+Shift+Enter.

=FILTER(B5:B14, A5:A14=G4)

This returns every App name from column B where the Division in column A matches the value in G4. The results spill automatically into the cells below.

FILTER Function Syntax

=FILTER(array, include, [if_empty])

• array — the range of cells you want to return results from (e.g., B5:B14 for App names).
• include — a logical test that produces TRUE or FALSE for each row. Only TRUE rows are returned.
• if_empty — optional. What to display when no rows match. Without this argument, FILTER returns a #CALC! error when there are zero matches.

How to Handle Zero Matches

If the lookup value does not exist in your data, FILTER returns a #CALC! error by default. To prevent this, use the third argument:

=FILTER(B5:B14, A5:A14=G4, "No matches found")

Now the formula displays “No matches found” instead of an error. You can also use an empty string “” if you prefer a blank cell.

How to Filter with Multiple Criteria (AND Logic)

To match rows where multiple conditions are all true, multiply the conditions together. Each condition produces a TRUE/FALSE array, and multiplying them applies AND logic (a row must pass every test).

=FILTER(B2:B100, (A2:A100=E2)*(C2:C100="Active"))

This returns App names where the Division matches E2 and the Status column equals “Active”. Add as many conditions as needed by multiplying additional arrays.

For OR logic (rows matching any condition), add the arrays with + instead of *:

=FILTER(B2:B100, (A2:A100="Game")+(A2:A100="Utility"))

This returns Apps belonging to either the Game or Utility division.

For a full walkthrough of AND, OR, and combined criteria, see How to Use the FILTER Function with Multiple Criteria.

How to Sort the Results

FILTER returns matches in the order they appear in the source data. To sort the results alphabetically, wrap the formula in SORT:

=SORT(FILTER(B5:B14, A5:A14=G4))

This spills the matching App names in A-to-Z order. For Z-to-A, add -1 as the fourth argument:

=SORT(FILTER(B5:B14, A5:A14=G4), 1, -1)

How to Remove Duplicates from the Results

If your data contains repeated values and you only want unique matches, wrap FILTER in UNIQUE:

=UNIQUE(FILTER(B5:B14, A5:A14=G4))

You can combine both. To get unique matches sorted alphabetically:

=SORT(UNIQUE(FILTER(B5:B14, A5:A14=G4)))

How to Return All Matches in One Cell

If you want all results combined into a single cell instead of spilling into separate rows, wrap FILTER inside TEXTJOIN:

=TEXTJOIN(", ", TRUE, FILTER(B5:B14, A5:A14=G4))

This returns a comma-separated list like “Fightrr, Hackrr, Kryptis, Twenty20” in one cell. Change the delimiter to any character you prefer, such as ” | “ or CHAR(10) for line breaks within the cell.

Wildcard and Partial Text Matching

FILTER does not support wildcard characters like or ? directly in the include argument. If you enter =FILTER(B2:B100, A2:A100=”Game“), Excel looks for the literal text “Game*” and returns nothing.

To match partial text, combine FILTER with SEARCH:

=FILTER(B2:B100, ISNUMBER(SEARCH("game", A2:A100)))

This returns every row where column A contains “game” anywhere in the cell. For a full breakdown of begins-with, ends-with, and case-sensitive matching, see the partial text and wildcards section below.

Common FILTER Errors and Fixes

• #CALC! error — No rows matched your criteria. Add the if_empty argument: =FILTER(B5:B14, A5:A14=G4, “”)
• #SPILL! error — The cells where results need to spill are not empty. Clear the cells below and to the right of your formula.
• #VALUE! error — The include array is a different size than the array argument. Both ranges must have the same number of rows.
• Wrong results with numbers stored as text — If your lookup column mixes true numbers with text-formatted numbers, FILTER treats them as different types. Use Find and Replace or VALUE() to standardize the format first.

Version requirement: FILTER is available in Microsoft 365 and Excel 2021. It is not available in Excel 2019 or earlier. If you need a solution for older versions, use INDEX + AGGREGATE for separate-cell output or TEXTJOIN + IF for single-cell output.

How to Return Grouped Matches with GROUPBY and ARRAYTOTEXT

If you have Excel 365, the GROUPBY function paired with ARRAYTOTEXT can return all matching values grouped by category in a single formula. No TEXTJOIN, no IF, no helper columns.

Say your data has Division names in column A and App names in column B. Instead of writing a separate lookup for each Division, enter this one formula:

=GROUPBY(A2:A100, B2:B100, ARRAYTOTEXT, , 0)

This returns a two-column result: each unique Division in the first column, and all its matching App names concatenated in the second column. The output spills automatically.

How the formula works:

• A2:A100 is the grouping column (the field you want to group by, such as Division).
• B2:B100 is the values column (the field you want to collect, such as App names).
• ARRAYTOTEXT is the aggregation function. It converts each group’s array of values into a comma-separated text string.
• The fourth argument is left blank (default field headers behavior).
• 0 in the fifth argument means no totals row.

The result looks like this:

Game → Fightrr, Hackrr, Kryptis, Twenty20
Utility → Accord, Misty Wash, Twenty20

Custom delimiter: ARRAYTOTEXT always separates values with a comma and a space. If you need a different separator (such as a semicolon or line break), replace ARRAYTOTEXT with a LAMBDA that uses TEXTJOIN:

=GROUPBY(A2:A100, B2:B100, LAMBDA(x, TEXTJOIN("; ", TRUE, x)), , 0)

When to use GROUPBY instead of FILTER: GROUPBY is best when you want a summary of every category at once rather than filtering for one specific value. If you only need matches for one lookup value, FILTER is simpler. If you need a full grouped report, GROUPBY handles it in one formula.

Version requirement: GROUPBY is available only in Microsoft 365. It is not available in Excel 2021 or earlier.

Why Can’t VLOOKUP or XLOOKUP Return Multiple Values?

Both VLOOKUP and XLOOKUP are designed to return exactly one result. They scan a column from top to bottom and stop as soon as they hit the first match. Every subsequent match is ignored.

This is not a bug or a limitation you can work around with arguments. The match_mode parameter in XLOOKUP controls how the function matches (exact, wildcard, approximate) but not how many results it returns. It will always return one row.

Example: If “Game” appears in cells A7, A8, A9, and A11, both of these formulas return only the value from A7:

=VLOOKUP("Game", A2:B100, 2, FALSE)
=XLOOKUP("Game", A2:A100, B2:B100)

The values in A8, A9, and A11 are never reached.

What to use instead:

• For all matches in separate cells → use FILTER
• For all matches in one cell → use TEXTJOIN + IF or FILTER + TEXTJOIN
• For all matches in every Excel version → use INDEX + AGGREGATE

If you are already comfortable with VLOOKUP and want to understand the differences before switching, see our full comparison: XLOOKUP vs VLOOKUP: Which Should You Use?

How to Return Multiple Matches with Partial Text or Wildcards

The FILTER function does not support wildcard characters like or ? directly. If you enter =FILTER(B2:B100, A2:A100=”Game“), Excel will look for the literal text “Game*” and return nothing.

To match partial text, combine FILTER with the SEARCH function:

=FILTER(B2:B100, ISNUMBER(SEARCH("game", A2:A100)))

This returns every row where column A contains the word “game” anywhere in the cell, whether it appears at the beginning, middle, or end. SEARCH is case-insensitive, so “Game”, “GAME”, and “game” all match.

How it works:

• SEARCH(“game”, A2:A100) checks every cell in the range. If “game” is found, it returns the character position (a number). If not, it returns a #VALUE! error.
• ISNUMBER() converts those results into TRUE (found) or FALSE (not found).
• FILTER uses the TRUE/FALSE array to return only the matching rows.

Begins with: To match only values that start with specific text, use LEFT instead of SEARCH:

=FILTER(B2:B100, LEFT(A2:A100, LEN(E2))=E2)

Where E2 contains the text to match. LEN(E2) counts the characters so the formula adapts to any search term length.

Ends with: Use RIGHT for values that end with specific text:

=FILTER(B2:B100, RIGHT(A2:A100, LEN(E2))=E2)

Case-sensitive matching: Replace SEARCH with FIND. The FIND function works the same way but treats uppercase and lowercase letters as different characters:

=FILTER(B2:B100, ISNUMBER(FIND("Game", A2:A100)))

This would match “Game” but not “game” or “GAME”.

Combining partial match with multiple criteria: Multiply conditions together to apply AND logic. For example, to return Apps where the Division contains “game” AND the Status column equals “Active”:

=FILTER(B2:B100, ISNUMBER(SEARCH("game", A2:A100)) * (C2:C100="Active"))

For a full guide on building multi-criteria FILTER formulas, see How to Use the FILTER Function with Multiple Criteria.

Which Method Should You Use? (Comparison Table)

Download the Practice Workbook

Featured Bundle

Black Belt Excel Bundle

This Excel Black Belt Package includes EIGHT of our Popular Courses. You’ll learn high-value, in-depth Excel skills that solve real problems.

Learn More

LinkedIn Twitter YouTube

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.

More About Leila Join 500,000+ professionals in our courses