'FILTER function' category



Extract a unique distinct list across multiple columns and rows sorted based on frequency
This article demonstrates a formula that creates a frequency distribution table from a multi-column cell range which is useful in […]
How to use the FILTER function
The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category […]
Get date ranges from a schedule
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
INDEX and MATCH – multiple criteria and multiple results
This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel […]
SMALL function – INDEX MATCH
This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]
How to ignore zeros using the SMALL function
This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]
If cell contains text from list
This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]
INDEX MATCH with multiple criteria
This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]
How to extract a case sensitive unique list from a column
This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]
Count unique distinct values based on a condition
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
Lookup based on a date range and a condition return multiple values
This article demonstrates a formula that extracts values from a column based on a date range criteria and another condition. […]
Extract a list of alphabetically sorted duplicates based on a condition
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
Extract duplicate values with exceptions
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
Filter duplicate values based on criteria
This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]
Unique distinct list sorted alphabetically based on a condition
This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z. […]
Use VLOOKUP and return multiple values sorted from A to Z
This article demonstrates how to extract multiple values based on a search value and display them sorted from A to […]
How to extract rows containing digits [Formula]
This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]
Calendar – monthly view
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
Find empty dates in a set of date ranges
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
Split expenses calculator
This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]
Search for a sequence of cells based on wildcard search
This article demonstrates array formulas that perform a wildcard search based on a sequence of values. The formulas return the […]
Search for a sequence of values
This article demonstrates array formulas that identify two search values in a row or in a sequence. The image above […]
Lookup and return multiple sorted values based on corresponding values in another column
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
Identify rows of overlapping records
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
Compare two columns and extract differences
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
Wildcard lookups and include or exclude criteria
This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]
Find all sequences of consecutive dates
This article demonstrates formulas that display dates that follow each other in sequence. Dates June 5, 2025 and June 6, […]
Lookup multiple values in one cell
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
Lookups in relational tables
Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]
Partial match and return multiple adjacent values
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
Extract unique distinct values in a filtered list
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
VLOOKUP – Return multiple unique distinct values
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Filter unique distinct records case sensitive
This article demonstrates two ways to extract unique and unique distinct rows from a given cell range. The first one […]
Filter words containing a given string in a cell range
This post describes two ways to extract all matching strings from cells in a given cell range if they contain […]
Filter overlapping date ranges
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
Compare two columns and return differences
This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]
Vlookup with multiple matches returns a different value
Table of Contents Vlookup with multiple matches returns a different value Lookup with multiple matches returns different values - Excel […]
Extract a unique distinct list sorted from A to Z ignore blanks
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
Extract dates from overlapping date ranges
The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]
List dates outside specified date ranges
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Merge two columns with possible blank cells
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]
List all unique distinct rows in a given month
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
Match two criteria and return multiple records
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
VLOOKUP/XLOOKUP of three columns to pull a single record
Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Table of Contents VLOOKUP […]
Vlookup with 2 or more lookup criteria and return multiple matches
VLOOKUP and return multiple matches based on many criteria.
Find the most recent date that meets a particular condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
Search values distributed horizontally and return corresponding value
Table of Contents Search values distributed horizontally and return the corresponding value Filter values distributed horizontally - Excel 365 1. […]
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
Extract unique distinct values sorted based on sum of adjacent values
Filtering unique distinct text values and sorting them based on the sum of adjacent values can easily be done by […]
Extract numbers from a column
I this article I will show you how to get numerical values from a cell range manually and using an […]
Extract unique distinct values A to Z from a range and ignore blanks
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
Find min and max unique and duplicate numerical values
Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]
Reverse a list ignoring blanks
This article demonstrates two different formulas, one for Excel 365 and one for earlier versions. Table of Contents Reverse a […]
Filter common values between two ranges
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
Extract duplicates from a multi-column cell range
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
Search for a text string in a data set and return multiple records
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
Extract a list of duplicates from three columns combined
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
Extract a list of duplicates from a column
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
Partial match with two conditions and return multiple results
This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]
Filter unique distinct values based on a date range
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Partial match for multiple strings – AND logic
This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Extract a unique distinct list and ignore blanks
This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]
Extract records between two dates
This article presents methods for filtering rows in a dataset based on a start and end date. The image above […]
Extract shared values between two columns
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
How to extract email addresses from an Excel sheet
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]
Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
What values are missing in List 1 that exists i List 2?
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
Cleaning Up Excel Worksheets: Eliminating Blank Cells, Rows, and Errors
In this blog post I will demonstrate methods on how to find, select, and deleting blank cells and errors. Why […]

Excel formula categories

AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique distinct recordsUnique distinct valuesUnique recordsUnique valuesVlookupVlookup return values

Excel categories