Get the Multiple Match Results Workbook
Every solution from the tutorial in one file: the MATCH and OFFSET helper column, two modified versions, the AGGREGATE and TEXTJOIN formulas, and FILTER.
This method comes from our long-time friend Bob Umlas. He sent it to me around the time FILTER was announced, and I love how simple it is.
Not everyone has Microsoft 365 or Excel 2021, so FILTER isn’t always an option. This method uses two short formulas and works in every version.
The Example: List Every App for One Division
We have a dataset where we are reporting on the Revenue of selected Apps. The Apps are associated with a Division, and each Division hosts multiple Apps.

Our objective is to enter a Division in cell G4 and be presented with a list of Apps and their related Revenues.

The Two-Step Solution at a Glance
The Short Formula: IFERROR and INDEX
The result column uses a short IFERROR and INDEX formula, starting in cell G5:
=IFERROR(INDEX(B:B, F5), "")
It reads the row number from cell F5.
The Helper Column: MATCH and OFFSET
Column F is a helper column. Cell F5 holds this formula:
=MATCH($G$4, OFFSET($A$1, F4, 0, 1000, 1), 0) + F4
It’s a bit longer, but it’s not too difficult. We will break it down into its constituent pieces, so we understand exactly what it’s doing for us.
Which Version to Use
The downloadable workbook also has two modified versions of Bob’s method, so you can pick the one that fits your data.
One solution, named “Bob_Mod1” begins the Division search at the top of the table (row 4) using traditional cell references.
Another solution, named “Bob_Mod2” begins the Division search at the top of the table (row 4) using Structured References to cells. This is because the dataset has been formatted as a proper Excel Table.
The concept is the same for all solutions; the naming is just a bit different given your data and the range you wish to search.
How the Helper Column Finds Each Row
The solution we will examine begins the search for the selected Division at the top of the column (row 1).
The key is the MATCH and OFFSET formula in the helper column, starting in cell F5:
=MATCH($G$4, OFFSET($A$1, F4, 0, 1000, 1), 0) + F4The formula returns the discovered position (row number) for each App in the selected Division.

This formula performs all the heavy lifting. By discovering the row numbers of the related Apps for the selected Division, we will be able to easily return the App name and Revenue from the same discovered row number.

Step 1: Find the First Match with MATCH
To find the position of the Division in a list, use the MATCH function.
Match has the following structure and arguments:
MATCH(lookup_value, lookup_array, [match_type])- lookup_value contains what we want to find (cell G4)
- lookup_array is the range of cells we are searching (column A)
- [match_type] defines approximate or exact match strategy (0 for exact match)
Beginning in cell F5, enter the following formula:
=MATCH($G$4, A:A, 0)MATCH returns 7, because the first Game sits in row 7.

Problem 1: Every Row Returns the Same Result
The problem is that when we fill the formula down to adjacent rows, we are repeatedly returned the same result.

This is because our search area (column A) is not changing.
=MATCH($G$4, A:A, 0)Suppose we limit the search range from the entire column to just the cells we expect to see data, such as cell A1 through A20?
=MATCH($G$4, A1:A20, 0)
Now the results count down: 7, 6, 5, 4.

Problem 2: Relative Ranges Count From the Wrong Row
Although we have limited the range, the range is made of relative references which “move” when the formula is repeated; A1:A20 -> A2:A21 -> A3:A22, etc.
This means that the start of the search range is getting closer and closer to the first occurrence of the selected Division; 7th cell, then 6th cell, then 5th cell, etc.
We can’t make the range absolute ($A$1:$A$20) either, because then every row returns 7 again.
And every search still stops at the first Game it finds.
Step 2: Make the Search Range Move with OFFSET
The way to solve all the above issues is to use the OFFSET function.
OFFSET moves a range or changes its size, based on the numbers you give it. For more ways to use it, see the OFFSET function for dynamic calculations.
OFFSET has the following structure and arguments:
OFFSET(reference, rows, cols, [height], [width])- reference: the starting cell, here
$A$1. - rows: how many rows to move down. 0 for now.
- cols: how many columns to move across. 0, or leave it empty.
- [height]: how many rows the range should have. Use a number big enough for new data, like 1000.
- [width]: how many columns. 1, or leave it out. Left out, height and width match the size of the reference.
You can leave out the optional arguments, or leave cols empty:
=MATCH($G$4, OFFSET($A$1, 0, 0, 1000, 1), 0)OFFSET is volatile, so Excel recalculates it every time anything in the workbook changes. That’s fine for a report like this, but you’ll notice it in very large files.
You could also write the formula another way, omitting the defaults.
=MATCH($G$4, OFFSET($A$1, , , 1000), 0)Why the Result Still Repeats
If we fill the formula down the column, we are presented with the same result.

This is because we keep stopping at the first instance of the selected Division.
Start Each Search Below the Last Match
We need to make the range begin AFTER the previously discovered Division. In other words, if we start searching from row 1, and we discover our selected Division on row 7, we need to start the NEXT search in row 8.
The way we will make this dynamic is to use the previously discovered row number result as the rows argument.
This means that the 2nd time we use OFFSET, instead of starting the search on row 1, it will move down 7 rows to start its search on row 8.
Step 3: Use the Cell Above as the Starting Point
There’s one small issue: if every OFFSET looks at the previous OFFSET’s result for the row argument, where does the first OFFSET look for its row argument? Great question.
Since we have an empty cell above our first OFFSET function, we can point to it for the row argument. An empty cell is treated as a 0 (zero), thus, the first OFFSET will not move any rows when plotting its start point for the range.
Let’s select cell F5 and update the formula as follows.
=MATCH($G$4, OFFSET($A$1, F4, 0, 1000, 1), 0)When we fill the updated formula down, we don’t exactly get the results we were hoping for.

In the 2nd instance of OFFSET, we moved the start of the range to cell A8 (down 7 rows from $A$1) and discovered the selected Division on the 1st row (row 8) of the updated range starting on row 8.
In the 3rd instance of OFFSET, we moved the start of the range to cell A2 (down 1 row from $A$1) and discovered the selected Division on the 6th row (row 7) of the updated range starting on row 2.
Step 4: Add the Previous Row Number Back
The fix: add the previous row number to the MATCH result. That turns the position inside the moved range back into the real row number:
=MATCH($G$4, OFFSET($A$1, F4, 0, 1000, 1), 0) + F4Fill it down and you get 7, 8, 9 and 11, the rows of the four Game apps.

Fill the Helper Column Down
Fill the formula down far enough for the division with the most matches. Rows past the last match return #N/A.

We’ll hide those in Step 7.
Step 5: Return the App Name with INDEX
The hardest part is behind us.
Now that you know the row of each match, INDEX can return the App from that row.
Starting in cell G5, we will use the INDEX function to return the App located on each discovered row.
The helper column counts from row 1, so INDEX has to start at row 1 too. Use B1:B1000 or the whole column B:B.
=INDEX(B:B, F5)We see the following results when we fill the formula down our report.

Step 6: Return the Revenue
Returning each related revenue is performed the same way as the returned App name. The only difference is we search column C for the item to return.
=INDEX(C:C, F5)The results are as follows.

Step 7: Hide the #N/A Errors
To make the report more presentable, Bob uses Conditional Formatting to hide the errors in the “helper column” and the IFERROR function to suppress the errors in columns G and H.
Hide Helper Column Errors with Conditional Formatting
To hide all the errors in the “helper column”, we use Conditional Formatting. This is done by creating a condition based on a formula.
The formula will use the ISNA() function to test if a cell contains a #N/A error message.
- Select the “helper column” results (F5:F14)
- Select Home (tab) -> Styles (group) -> Conditional Formatting -> New Rule -> “Use a formula to determine which cells to format”
- Enter the rule =ISNA(F5). Keep F5 without dollar signs so each row checks its own cell.

- Click Format
- In the Format Cells dialog box, set the font color to white.

- Click OK twice

Hide the Other Errors with IFERROR
To hide the remaining #N/A errors in columns G and H:
- Update the formula in cell G5 to include the IFERROR function in a way that replaces the #N/A with empty text (two double quotes).
=IFERROR(INDEX(B:B, F5), "")- Perform the same modifications to the formula in cell H5.
=IFERROR(INDEX(C:C, F5), "")- Fill the formulas in cells G5 and H5 down to replace the original INDEX formulas with IFERROR/INDEX

Both formulas are short, so this method is easier to remember, build and fix than the one-formula AGGREGATE version from how to return multiple match values in Excel.
Modified Versions: COUNTIF and Excel Tables
Version 1: Cell References Starting at the Table
If you examine the formulas on the “Bob_Mod1” sheet, we see the following alternate way of producing the report.
- We don’t use Conditional Formatting to hide unneeded rows in the report. Instead, we create a COUNTIF function in cell F3 to determine the number of matching items (Division) that exist in the dataset.
=COUNTIF(A5:A1000, G4)- The next formula, starting in cell F5, is used to build the list of discovered locations for matching Divisions.
=IF(ROWS($E$5:E5) <= $F$3, MATCH($G$4, OFFSET($A$4, F4, 0, 1000, 1), 0) + F4, "")The first part of the IF function tests to see if we have yet to surpass the number of possible results.
ROWS($E$5:E5) <= $F$3If we haven’t exceeded that value, we use the same MATCH/OFFSET logic to discover the matching Divisions.
MATCH($G$4, OFFSET($A$4, F4, 0, 1000, 1), 0) + F4If we have surpassed the largest number of discovered results, we render the cell empty with empty text (two double quotes).
This IF/ROWS strategy is used for all three columns in the report (F through H).
One small difference in the column G and H formulas; we are using the range $B$4:$B$1000 instead of B:B.
=IF(ROWS($E$5:E5) <= $F$3, INDEX($B$4:$B$1000, F5), "")This allows us to have unrelated information above our table without interfering with the searches.
Version 2: Excel Table with Structured References
If you examine the sheet named “Bob_Mod2”, you can see we are working with a proper Excel Table.

Because Excel Tables use Structured References instead of traditional cell references, the formulas point to the column names instead of the specific cells (this is a bit of a generalization).
- The formula to determine the number of matching items (cell F3)
=COUNTIFS(TableApp[Division], G4)- The “helper column” formula to determine matching Division locations (starting in cell F5)
=IF(ROWS($E$5:E5) <= $F$3, MATCH($G$4, OFFSET(TableApp[[#Headers], [Division]], F4, 0, ROWS(TableApp[Division]), 1), 0) + F4, "")NOTE: The formula looks much larger, but the logic is the same. Because Structured References take up more space than traditional cell references, the formula increases in character count but not strategy.
- The formula to determine matching related App name locations (starting in cell G5)
=IF(ROWS($E$5:E5) <= $F$3, INDEX(TableApp[[#All], [Apps]], F5), "")- The formula to determine matching related Revenue locations (starting in cell H5)
=IF(ROWS($E$5:E5) <= $F$3, INDEX(TableApp[[#All], [Revenue]], F5), "")One-Formula Options: AGGREGATE and TEXTJOIN
The workbook also keeps the original one-formula solutions on the sheet named Solution. AGGREGATE works in Excel 2010 and later and needs no Ctrl+Shift+Enter. It returns each matching app:
=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))), "")TEXTJOIN lists every matching app in one cell, separated by commas. TEXTJOIN works in Excel 2019 and later. In Excel 2019, confirm the formula with Ctrl+Shift+Enter. In Microsoft 365, Enter is enough.
=TEXTJOIN(", ", TRUE, IF(A5:A14=G4, B5:B14, ""))Structured Reference Version
If you’re using proper Excel Tables, the formulas appear as follows.
For related Apps
=IF(ROWS($F$5:F5) <= $F$4, INDEX(TableDiv[Apps], AGGREGATE(15, 3, (TableDiv[Division] = $G$4) / (TableDiv[Division] = $G$4) * (ROW(TableDiv[Division]) - ROW(TableDiv[[#Headers], [Division]])), ROWS($F$5:F5))), "")For related Revenue
=TEXTJOIN(", ", TRUE, IF(TableDiv[Division]=$G$4, TableDiv[Revenue], ""))The Easiest Option in Microsoft 365: FILTER
If you have Microsoft 365, Excel 2021 or later, or Excel for the web, FILTER is the easiest option. One formula returns both columns, with no helper column. The data table here is named TableDA. For more examples, see the FILTER function with multiple criteria.
In cell G5:
=FILTER(TableDA[[Apps]:[Revenue]], TableDA[Division] = G4, "")Download the Multiple Match Results Workbook
Get the Free Workbook
Five ways to return every match. Pick the one that fits your Excel version.
Frequently Asked Questions
How do I return multiple matches in Excel without FILTER?
Use a helper column with =MATCH($G$4, OFFSET($A$1, F4, 0, 1000, 1), 0) + F4 to find the row of each match. Then return the result from that row with =IFERROR(INDEX(B:B, F5), ""). Copy both formulas down.
Why does VLOOKUP only return the first match?
VLOOKUP and XLOOKUP stop at the first value that matches. To get every match, you need a method that keeps searching below the last match, like the MATCH and OFFSET helper column, or the FILTER function.
Which Excel versions does this method work in?
It works in every Excel version since Excel 2007, because it only uses MATCH, OFFSET, INDEX and IFERROR. You do not need Ctrl+Shift+Enter.
Why does the helper column formula add the cell above?
OFFSET moves the search range down by the previous row number, so MATCH returns a position inside the moved range. Adding the cell above turns that position back into the real row number.
How do I hide the #N/A errors below the last match?
Wrap the INDEX formulas in IFERROR, and hide the helper column errors with a conditional formatting rule =ISNA(F5) and a white font. Or count the matches with COUNTIF and stop the formulas after the last one.
What is the easiest way to get multiple matches in Microsoft 365?
Use FILTER: =FILTER(TableDA[[Apps]:[Revenue]], TableDA[Division]=G4, "") returns every matching app and its revenue in one formula. FILTER works in Microsoft 365, Excel 2021 and later, and Excel for the web.
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.







