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; 7^{th} cell, then 6^{th} cell, then 5^{th} 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 2^{nd} 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 2^{nd} 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

**1**row (

^{st}*row 8*) of the updated range starting on row 8.

In the 3^{rd} 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

**6**row (

^{th}*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.

## Locating the Related App Name

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.

## Locating the Related 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.

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

- 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**” - Create the following rule:
**=ISNA(F5)**

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

- Click
**OK**twice

### Suppressing the Remaining Error Messages

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**

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

### Leila Gharani

I'm a 5x 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.