Search results

In this example the goal is to return rows in the range B5:E15 that have a specific state value in column E. To make the example dynamic, the state is a variable entered in cell H4. When the state in H4 is changed, the formula should return a new set of records. This is a perfect...Read more

Note: FILTER is a new dynamic array function in Excel 365. In other versions of Excel, there are alternatives, but they are more complex...Read more

This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D14, which contains the full set of data without headers. The include argument is based on a...Read more

In this example, the goal is to filter data based on multiple criteria with the FILTER function. Specifically, we want to select data where (1) the group = "A" and (2) the Score is greater than 80. At first glance, it's not obvious how to do this with the FILTER function. Unlike older...Read more

This formula relies on the FILTER function to retrieve data based on a logical test created with the MONTH function. The array argument is provided as the named range data, which contains...Read more

Note: FILTER is a new dynamic array function in Excel 365. In other versions of Excel, there are alternatives, but they are more complex...Read more

In this example, the goal is to list values in a given group that are within a given tolerance. The group is set in cell G4, and the target value is entered in cell G5. The allowed tolerance is entered in cell G6. The data comes from an Excel Table called...Read more

In this example, the goal is to filter the values in one array by the values in another array. For convenience, data (B4:D11) and filter (F4:F6) are named ranges. The solution is based on the...Read more

The goal is to extract records with dates that are greater than or equal to a start date in F5 and less than or equal to an end date in G5. You might think we can use the AND function inside FILTER to solve this problem. However, because AND returns...Read more

This formula relies on the FILTER function to retrieve data based on a logical test built with simple expressions and boolean logic:

(D5:D14="red")+(D5:D14="blue")
...Read more

In this example, the goal is to use a single formula to extract high-value projects and list them in a simple table. We also want to remove unnecessary columns to create a clean, uncluttered view. The solutions explained below are based on a combination of several functions in Excel, including...Read more

The FILTER function is designed to extract data that matches one or more criteria. In this case, we want to apply criteria that requires all three columns in the source data (Name, Group, and Room) to have data. In other words, if a row is missing any of...Read more

This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE and IF functions. The result is the top 3 scores in group B.

The FILTER function applies criteria with...Read more

This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D15, which contains all of the data without headers. The include argument is...Read more

The goal is to filter the horizontal data in the range C4:L6 to extract members of the group "fox" and display results with data transposed to a vertical format. For convenience and readability, we have two named ranges to work with: data (C4:L6) and group...Read more

The FILTER function is designed to filter and extract information based on logical criteria. In this example, the goal is to extract every 3rd record from the data shown, but there is no row number information in the data.

Working from the inside...Read more

This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE function.

The LARGE function is a simple way to get the nth largest value in a range. Simply provide a range for the first argument (array...Read more

Although FILTER is more commonly used to filter rows, you can also filter columns, the trick is to supply an array with the same number of columns as the source data. In this example, we construct the array we need with boolean logic, also called Boolean...Read more

In this example, the goal is to calculate an average for any given group ("A", "B", or "C") across all three months of data in the range C5:E16. For convenience only, data (C5:E16) and group (B5:B16) are named ranges. In...Read more

In this example, the goal is to extract the first 3 values or the last 3 values from the named range data (B5:B15). We also want to exclude any empty cells from our results. In the worksheet shown the formula in cell D5 is:

=INDEX(FILTER(...Read more

In this example, we need to construct logic that filters data to include:

account begins with "x" AND region is "east", and month is NOT April.

The filtering logic of this formula (the include argument) is created by chaining together three expressions that use...Read more

The goal in this example is to display the last 3 valid entries from the table shown, where "valid" is defined as a temperature of less than 75 in the "Temp" column. At a high level, the FILTER function is used to filter entries based on a logical test, and the INDEX function is used to extract...Read more

In this example, the goal is to filter data to show rows where dates have expired or will be expiring soon. In the table to the left, we have equipment that needs to be replaced every x months, where x appears in the "Months" column. The "Replaced" column shows the date equipment was...Read more

In this example, criteria are entered in the range F5:H6. The logic of the formula is:

item is (tshirt OR hoodie) AND color is (red OR blue) AND city is (denver OR seattle)

The filtering logic of this formula (the include argument) is applied with the...Read more

In this example, the goal is to extract a set of records that match a partial text string. To keep things simple, we are only matching one field in the data, the last name ("Last"). The core operation of this formula comes from the...Read more

The FILTER function can filter data using a logical expression provided as the include argument. In this example, this argument is created with an expression that uses the ISNUMBER and MATCH functions like this:

...Read more