Find multiple match results in Excel tutorial thumbnail

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.

Excel dataset with Division, Apps and Revenue columns

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

Report listing the Game apps and revenue for the division in G4

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), "")
IFERROR and INDEX formula in G5 returning the app from the row in 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
Helper column formula with MATCH and OFFSET in cell F5

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) + F4

The formula returns the discovered position (row number) for each App in the selected Division.

Helper column showing row numbers 7, 8, 9 and 11 for Game

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.

Apps for the Game division with their row numbers and revenue

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.

MATCH returns 7, the row of the first Game

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.

MATCH on the full column returns 7 in every row

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)
MATCH with the relative range A1:A20

Now the results count down: 7, 6, 5, 4.

Relative range results counting 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.

MATCH with OFFSET from A1 still returns 7 in every row

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.

OFFSET using the cell above as the rows argument

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) + F4

Fill it down and you get 7, 8, 9 and 11, the rows of the four Game apps.

Adding F4 to the MATCH result returns the real row numbers

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.

#N/A errors below the last match in the helper column

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.

INDEX on column B returns the app for each row number

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.

INDEX on column C returns the revenue for each match

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.

  1. Select the “helper column” results (F5:F14)
  2. Select Home (tab) -> Styles (group) -> Conditional Formatting -> New Rule -> “Use a formula to determine which cells to format”
  3. Enter the rule =ISNA(F5). Keep F5 without dollar signs so each row checks its own cell.
New Formatting Rule with the formula =ISNA(F5)
  1. Click Format
  2. In the Format Cells dialog box, set the font color to white.
Format Cells dialog setting the font color to white
  1. Click OK twice
Helper column errors hidden with conditional formatting

Hide the Other Errors with IFERROR

To hide the remaining #N/A errors in columns G and H:

  1. 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), "")
  1. Perform the same modifications to the formula in cell H5.
=IFERROR(INDEX(C:C, F5), "")
  1. Fill the formulas in cells G5 and H5 down to replace the original INDEX formulas with IFERROR/INDEX
Finished report with the errors hidden by IFERROR

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$3

If 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) + F4

If 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.

Dataset formatted as an Excel table for the structured reference version

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.