Excel Alternatives to FILTER Function: A Simple Solution for Beginners

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

Microsoft Excel makes the impossible possible. By allowing users to accomplish a wide range of tasks, including data analysis and manipulation, working with data has never been easier than it is today. One common function in Excel is the FILTER function, which helps users sort and sift through large datasets. However, there might be times when you cannot use the FILTER function and you’re looking for an alternative method that offers similar results.

One such method involves using a combination of the INDEX, AGGREGATE, and ROW(S) functions. This formula, =INDEX($B$8:$B$13,AGGREGATE(15,6,ROW($B$8:$B$13)-ROW($B$8)+1/($B$8:$D$13=$G$7),ROWS(I$10:I10))), provides the same output as the FILTER function in the attached sample dataset.

Key Takeaways

  • Excel’s FILTER function is a valuable tool for data analysis, but sometimes an alternative approach is needed.
  • Combining INDEX, AGGREGATE, and ROW(S) functions can provide a flexible alternative to the filter function.

What Is the Excel Filter Function?

First, let’s state the obvious. 

The FILTER function is much more compact than this Excel alternative. If you have the opportunity to use it, I suggest you do.

This function works by applying specific criteria to an array, helping you find the relevant data in your spreadsheet quickly and efficiently. To use the FILTER function, you’ll need to input the array (the range of data you want to filter) and then specify the criteria for the filter. 

The syntax looks like this: =FILTER(array, include, [if_empty]). The “include” parameter is where you define the criterion for filtering the data. Optionally, you can also specify what to display if there are no results that match.

For example, we have player scores for each team in the provided dataset. Shoutout to my Halo brethren!

excel alternative filter data 1

If you want to display only the players and scores for a specific team, you would enter the formula =FILTER(B8:C13,D8:D13=G7,”Not my team”). This would create a new filtered array of data with only the names and scores that meet your parameters.

FYI, cell G7 is a drop-down for you to alter your “include” without needing to adjust your FILTER formula.

using the filter formula 2

There’s no need to worry about complicated jargon or complex formulas when using the FILTER function in Excel. Once you get the hang of it, you’ll find it incredibly useful for managing and analyzing your data. 

But if you’re here, I’m guessing that’s not an option.

Why Can’t I Use the FILTER Function?

The most likely reason you can’t use the FILTER function is because you have an older version of Excel you’re working with.

The FILTER function was introduced in 2019 as part of their dynamic array bundle. It is available in Microsoft 365 subscriptions and Excel 2021. If you have an older version, you won’t have access to this function.

Best Excel Alternative to FILTER Function

If you’re looking for an alternative to the FILTER function in Excel, combining INDEX, AGGREGATE, and ROW(S) functions is one of the best options. This alternative formula is designed to be compatible with older Excel versions or to accommodate specific user preferences. 

The completed formula is: =INDEX($B$8:$B$13,AGGREGATE(15,6,ROW($B$8:$B$13)-ROW($B$8)+1/($B$8:$D$13=$G$7),ROWS(I$10:I10))).

alternative to the filter function formula 3

While the formula performs the same task as the FILTER function, it is not dynamic. This means you’ll have to use absolute references ($) and copy your formula into the cells needed. In addition, for this sample data, your column references must be changed to pull related columns over.

So let’s break down this nested formula alternative and demystify each step.

Understanding How It Works

From a high-level view, this formula enables you to filter data based on a specific condition. 

To start, the INDEX function retrieves values from the specified range of columns (in this case, $B$8:$B$13). Next, the AGGREGATE function manages the array formula and sorts the results in descending order. The ROW function is applied to calculate the cell reference with the help of the initial row number.

Here’s a breakdown of what each part of the function does:

INDEX($B$8:$B$13, – This specifies the range of cells in column B that the function will search for a match.

AGGREGATE(15,6, – This specifies the type of aggregate calculation to perform (in this case, SMALL) and the option to ignore error values and hidden rows.

ROW($B$8:$B$13)-ROW($B$8)+1/($B$8:$D$13=$G$7), – This calculates the row number of each match by subtracting the row number of the first cell in the range ($B$8) from the row number of each cell in the range and then adding 1. 

The result is divided by a logical test that checks whether the value in each cell in the range $B$8:$D$13 equals the value in cell G7. This returns an array of row numbers where the criteria are met.

ROWS(I$10:I10))) – This returns the k-th smallest value in the array of row numbers, where k is equal to the number of rows in the range I$10:I10. As the formula is copied down, the range I$10:I10 will expand to I$10:I11, I$10:I12, and so on, allowing the formula to return multiple matches.

copied down rows in column i 4

Overall, this formula searches for a match in a table based on a value in cell G7 and returns the corresponding value from column B. 

Again, this is one way and could be made more functional. So if you’ve got any suggestions or alternate solutions, drop them in the comments! I’d love to hear your thoughts.

Leave a Comment