## 'SMALL function' category

How to ignore error values using the SMALL function
The image above shows you a formula in cell D3 that tries to get the smallest number from cell range […]
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 […]
How to replace part of formula in all cells
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
SMALL function with multiple cell ranges
Today I learned how to sort numbers from multiple cell ranges thanks to Sam Miller. It is surprisingly simple and easy. […]
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 […]
SMALL function – multiple conditions
This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]
SMALL function for text
This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]
SMALL function ignore duplicates
INDEX MATCH – multiple results
This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]
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 […]
Distribute values across numerical ranges
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
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). […]
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 […]
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 […]
Reverse two-way lookups
This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]
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 […]
Sort values by corresponding text arranged in a column
This article describes a formula that sorts values arranged in a column from A to z by every other value. […]
Follow stock market trends – Moving Average
In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]
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, […]
Extract the most repeated adjacent values in a column
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A.  Cell […]
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 […]
How to group items by quarter using formulas
This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]
Lookup value based on two critera – second criteria is the adjacent value and its position in a given list
This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]
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 […]
Lookup with any number of criteria
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
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, […]
How to use the SMALL function
The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.
How to sort a data set using three different approaches, built-in tools, array formulas, and VBA
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
Convert array formula to a regular formula
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
VLOOKUP and return multiple values across columns
This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]
Locate lookup values in an Excel table [HYPERLINK]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]
Working with three relational tables
I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]
Extract unique distinct values from a relational table
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]
Lookups in relational tables
Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]
Search related table based on a date and date range
I will in this article demonstrate how to search a table for a date based on a condition and then […]
Excel calendar [VBA]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Populate drop down list with filtered Excel Table values
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
Shift Schedule
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
Plot date ranges in a calendar part 2
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
Extract table headers based on a condition
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]
Use a drop down list to filter and concatenate unique distinct values
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
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 […]
Dependent drop-down lists in multiple rows
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
Watch schedule that populates vacation time
This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]
Extract week ranges based on a given date range
The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]
Use a drop down list to search and return multiple values
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
Count cells between specified values
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
VLOOKUP in a filtered Excel Table and return multiple values
This post describes how to search filtered values in an Excel defined Table using a condition given in cell 12 and return […]
Remove common records between two data sets
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
Filter unique distinct values, sorted and blanks removed from a range
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
Vlookup across multiple sheets
This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]
Vlookup a cell range and return multiple values
VLOOKUP a multi-column range and return multiple values.
Search each column for a string each and return multiple records – OR logic
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]
Bill reminder in excel
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]
Filter records based on a date range and a text string
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
Fuzzy VLOOKUP
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
Text to columns: Split words in a cell
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Highlight closest number
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
Merge matching rows
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]
Filter overlapping date ranges
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
Create a drop down calendar
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
Compare tables: Filter records occurring only in one table
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
Filter shared records from two tables
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
Extract duplicate records
This article describes how to filter duplicate rows with the use of a formula.  It is, in fact, an array […]
Return multiple matches with wildcard vlookup
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
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 […]
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Create a drop down list containing alphabetically sorted values
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
True round-robin tournament
Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]
Sort records based on two columns
Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]
Case sensitive lookup and return multiple values
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]
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 dates from a cell block schedule
Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]
Create numbers based on numerical ranges
The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]
Find empty hours in a weekly schedule
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]
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 […]
Convert date ranges into dates
Convert dates into date ranges
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
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 […]
Sort numeric values based on proximity to a given number
The image above demonstrates an array formula in cell C25 that extracts numbers based on how far off they are […]
Filter duplicates within same date, week or month
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
Remove duplicates within same month or year
The array formula in cell B15 extracts dates from B4:B12 if it is not a duplicate item in the same […]
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 […]
Extract records containing negative numbers
Return multiple values if in range
The image above shows a formula in cell C11 that extracts values from column D if the number in cell […]
Sort two columns
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
Vlookup with 2 or more lookup criteria and return multiple matches
VLOOKUP and return multiple matches based on many criteria.
Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
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.
Filter unique values sorted from A to Z
A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]
Extract records where all criteria match if not empty
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Identify missing numbers in a range
Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]
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 […]
Identify missing three character alpha code numbers
This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]
List values with past date
Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]
Perform multiple partial matches and return records – AND logic
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]
Filter duplicate values and sort by corresponding date
Array formula in D2: =INDEX(\$A\$2:\$A\$21, MATCH(SMALL(IF(COUNTIF(\$B\$2:\$B\$21, \$B\$2:\$B\$21)>1, COUNTIF(\$A\$2:\$A\$21, "<"&\$A\$2:\$A\$21), ""),ROWS(\$A\$1:A1)), COUNTIF(\$A\$2:\$A\$21, "<"&\$A\$2:\$A\$21), 0)) Array formula in E2: =INDEX(\$B\$2:\$B\$21, MATCH(SMALL(IF(COUNTIF(\$B\$2:\$B\$21, \$B\$2:\$B\$21)>1, […]
Sort a range from A to Z [Array formula]
Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range \$B\$2:\$E\$5 contains text values in random […]
Extract a unique distinct list sorted from A-Z from range
The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]
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 […]
Search for a cell value in a dataset
Question: How do I identify rows that have a specific cell value in an Excel defined table? Array formula in […]
Partial match based on two conditions
Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]
Identify missing numbers in two columns based on a numerical range
Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
Sort dates within a date range
Array formula in D5: =SMALL(IF((\$B\$3:\$B\$12<=\$E\$3)*(\$B\$3:\$B\$12>=\$E\$2), \$B\$3:\$B\$12, "A"), ROWS(\$A\$1:A1)) How to create an array formula Copy array formula Select cell D5 […]
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
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. […]
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 […]
Extract a unique distinct list sorted from A to Z
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
Sort a column alphabetically
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
Extract all rows that contain a value between this and that
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
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 […]
How to calculate a date based on specific weekday in a month
Question: How to calculate the date of the third Monday of a given month? Answer: Column B contains dates of […]
How to extract not shared values in two columns
Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]
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 search for a string in a column
Question: How do i create a flexible search formula to search a list? Answer: The following formula let´s you search […]
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 […]
Find closest value
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
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. Table […]
Find the smallest number in a list that is larger than a given number
This article demonstrates formulas that lets you extract the smallest number larger than a given number. The example above specifies […]