Dynamically Filtered Ranges in Excel

We use Excel a lot around Ignia. And a challenge we've bumped up against repeatedly is the ability to dynamically filter ranges. There are a lot of reasons one might want to do this. One common example is the ability to provide a dropdown list based on values the user has entered. For this post, we're going to use the example of an inventory spreadsheet which contains a column for manufacturer and a column for models; the goal is to have the models cell provide a list of models dependent on the manufacturer. So, for instance, a user selects "BMW" from manufacturer and the corresponding model cell provides a list of options including "M5", "Z4" and "X6". (This is sometimes referred to as "Cascading Drop Downs").

Before we begin, a quick look at other options. The most obvious is to use Auto-Filter; unfortunately, though, that only affects the user interface, not references to the data. If you're comfortable with VBScript, this can also be done via VBA using the AdvancedFilter() method; this approach, however, is dependent on the user enabling macros. Our goal, thus, is to exclusively use built-in functions to accomplish this.

The approach we use is pretty straight forward, although it may not be immediately obvious. At a high-level, it uses the OFFSET function to create a dynamic range. Offset allows a range to be created of a specified size, offset from a starting position. Specifically:

OFFSET (
Reference: Starting position of models list,
Rows: Starting position of models associated with the selected manufacturer,
Columns: The column containing the model names, relative to the reference,
Height: The number of models associated with the selected manufacturer,
Width: The number of columns we want to return; for lookups, this will be 1.
)

So, for this example we're going to assume that we have a worksheet which contains the following columns:

Model Name
Manufacturer Name

In practice, you'll likely have additional metadata - perhaps some IDs (from a database) and attributes for each model. For the purpose of this example, those are irrelevant. One thing that is critical, however, is that this data be sorted by manufacturer. In addition, we're going to assume for simplicity that there are named ranges for each column - let's say, "ModelNames" and "ModelManufacturers". In this case, our final function might look like:

OFFSET (
INDEX(ModelNames, 1, 1),
MATCH(manufacturer, ModelManufacturers, 0)-1,
0,
IF (
    ISERROR (MATCH(A1, ModelManufacturers,1)),
    COUNT(ModelManufacturers),
    MATCH(A1, ModelManufacturers, 1)
    ) - MATCH (A1, ModelManufacturers, 0) + 1,
1
)

For those of you unfamiliar with these functions, we'll walk through this step by step.

Reference: This simply establishes that the offset will be measured from the first cell and the first column of the ModelNames range. INDEX() allows us to grab a reference to any cell in a range based on ordinal values - so in this case, "1, 1" might represent the equivalent of "A1", assuming the named range begins at A1.
Rows: The searches the ModelManufacturers column for the first row number that matches the name of the manufacturer exactly (as signified by parameter "0"). In this case, the value of the manufacturer is likely a cell reference, such as "A1" (assuming manufacturers are selected in column A).
Columns: Since the reference began with the first cell of ModelNames and the value we want to return is model names, we set the offset to 0. This might change if your data model looks different; for example, if you had a ModelID in the first column.
Height: This is a bit tricky. Normally, we'd just use MATCH() with the "1" parameter to get the row number of the last instance of a record containing the manufacturer name, and then subtract from it the starting row calculated previously. Unfortunately, MATCH() will return an error in this case if the manufacturer is the last record in the range since there is not a subsequent record for it to return. To get around this, we use the ISERROR() function to detect the error and, if it exists, return the total record count for the range instead.
Width: The width of the resulting range. Since we want to bind this to a drop-down menu for validation, this needs to be 1 column wide. For other applications, however, you may want to return a filtered range that includes additional metadata