We need to give credit to our long-time friend Bob Umlas.

Bob sent this solution some time ago when the new FILTER function in Office 365 was being released, and FILTER ended up garnering all my attention.

I realize that not everyone will subscribe to Office 365, thus the FILTER function remains out of reach.

Non-Office 365 users shouldn’t be left with complicated solutions.  I like this alternate solution because of its simplicity.

Let’s Begin with Some Context

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

First of Two Steps

The first component (actually, it’s the second, but it’s shorter and we’ll point it out first) is a small IFERROR/INDEX formula.

Starting in cell G5:

=IFERROR(INDEX(B:B,F5), “”)

The key to this formula is the reference to cell F5.

Second of Two Steps

Cell F5 contains what is affectionately known as a “helper column”.  Cell F5 contains:

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

Alternate Solutions

The downloadable file (the link is below for the downloadable file) contains alternate solutions.

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.

The Key to It All

The solution we will examine begins the search for the selected Division at the top of the column (row 1).

The key to this solution lies in the MATCH/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.

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.

Constructing the “Helper Column”

To locate the position of the selected Division in a list, a great function to perform this act is 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)

We are informed that the Division “Game” is located on row 7.

Problem #1

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($F$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($F$4, A1:A20, 0)

The result is now something a bit odd.

Problem #2

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 just make the search range absolute ($A$1:$A$20) because that’s no different than selecting the entire column (A:A).

Also, we are constantly stopping the search when we encounter the first instance of the selected Division.

The Magical Solution

The way to solve all the above issues is to use the OFFSET function.

OFFSET allows us to dynamically relocate the search range location and/or size.

OFFSET has the following structure and arguments:

OFFSET(reference, rows, cols, [height], [width])
  • reference is the starting cell for all the following arguments (A1) {no default}
  • rows is the number of rows you wish to move, up or down, to establish the new range (0) {default=0}
  • cols is the number of columns you wish to move, left or right, to establish the new range (0) {default=0}
  • [height] is the number of rows to select for our range (1000) {default=1}
  • [width] is the number of columns to select for our range (1) {default=1}

Arguments that have defaults and can be skipped if using the defaults.

=MATCH($G$4, OFFSET($A$1, 0, 0, 1000, 1), 0)

You could also write the formula another way, omitting the defaults.

=MATCH($G$4, OFFSET($A$1, , , 1000), 0)

Close, but not quite there yet

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.

Tweaking the dynamic nature of OFFSET

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.

NOTE: This is the point where Bob is awarded the “Nobel Prize for Creativity”.

There’s a Catch

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.

The Final Adjustment

The way to get the search range’s start position (row) to begin just after the last discovered Division’s row…

(and this is where Bob earned his second Nobel Prize)

… is to add the prior OFFSET’s result to the succeeding OFFSET’s result.

=MATCH($G$4, OFFSET($A$1, F4, 0, 1000, 1), 0) + F4

Updating the existing formulas with the updated formula, we see that the results are now working as expected.

Filling down the rest of the report

We need to fill the formula in cell F5 down an expected number of rows of our largest report.  This produces an undesirable result when the report length exceeds the number of match results.

For now, we will tolerate them.

The hardest part is behind us.

Now that we know exactly what row each matching Division exists; we can use that information to find each related App.

Starting in cell G5, we will use the INDEX function to return the App located on each discovered row.

Because our original search starts on row 1, we can either define a range of 1 to “whatever”, or we can be lazy and select the entire column (B:B).

=INDEX(B:B, F5)

We see the following results when we fill the formula down our report.

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.

Cleaning Up the Errors In the Report

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.

Conditionally Formatting the “Helper Column”

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. Create the following rule: =ISNA(F5)
  1. Click Format
  2. In the Format Cells dialog box, set the font color to white.
  1. Click OK twice

Suppressing the Remaining Error Messages

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

Thoughts On the Process

Because these two formulas are much shorter than the ones showcased in earlier posts, these will be easier to remember, create, and troubleshoot.

Alternate Versions

Plain Tables with Traditional References

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.

Proper Tables 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], [Apps]], F5), "")

For Those of You That Like a Challenge

If you’re interested, I’ve included the original solution on the sheet named “Solution” that showcases the single-solution using the AGGREGATE function and traditional cell references to discover the 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))), "")

… as well as the TEXTJOIN function used to discover the matching Revenue.  (This is built as an array function using CTRL-Shift-Enter syntax.)

{ =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(A5:A14  =G4, C5:C14, ""))}

Using FILTER in Office 365

If you are fortunate enough to be using Office 365 and have access to the FILTER function, this is the easiest method.  No helper columns are needed, no crazy array syntax, and all is handled by a single formula. (The table of data has been named “TableDA”.)

In cell G5:

=FILTER(TableDA[[Apps]:[Revenue]], TableDA[Division] = G4, "")

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file
Published on: May 28, 2020
Last modified: February 26, 2023
Category: Excel,Formulas
Tagged as: , , , , , , , ,
Microsoft Most Valuable Professional
LinkedIn Twitter YouTube

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.

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