'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 […]
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 […]
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 […]
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. […]
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 […]
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 […]
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 […]
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 […]
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 […]
This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]
SMALL function ignore duplicates
This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]
This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]
INDEX MATCH – multiple results
This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]
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 […]
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 […]
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 […]
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). […]
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. […]
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 […]
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 […]
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. […]
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 […]
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. […]
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 […]
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. […]
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 […]
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 […]
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, […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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
The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]
The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]
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.
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 […]
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 […]
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 […]
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 […]
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 […]
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, […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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. […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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.
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" […]
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 […]
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 […]
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
Fuzzy vlookup [Array formula]
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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. […]
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 […]
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 […]
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, […]
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 […]
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 […]
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 […]
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 […]
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 […]
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. […]
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 […]
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
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
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 […]
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 […]
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 […]
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 […]
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 […]
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. […]
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 […]
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 […]
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
Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]
Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]
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 […]
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 […]
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.
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 […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
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. […]
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.
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 […]
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) […]
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Extract all rows from a range that meet criteria in one column
Lookup with criteria and return records.
Lookup with criteria and return records.
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 […]
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 […]
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 […]
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: […]
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"... […]
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 […]
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, […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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. […]
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 […]
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. […]
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 […]
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 […]
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
Delete blanks and errors in a list
Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]
Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]
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 […]
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 […]
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 […]
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 […]
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 […]
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
Improve your VLOOKUP formula and return multiple values
Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]
Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]
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 […]
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 […]
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 […]
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
Remove blank cells
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
Find the most recent date in a list
The image above shows a formula in cell D3 that extracts the most recent date in cell range B3:B15. =MAX(B3:B15) […]
The image above shows a formula in cell D3 that extracts the most recent date in cell range B3:B15. =MAX(B3:B15) […]
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
Latest updated articles.
More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.
More than 1300 formulas organized in subcategories.
Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.
Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.
Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.
Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.
Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.
The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.
An Excel feature that lets you visualize data in a graph.
Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.
Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.
VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.
A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.
UDF stands for User Defined Functions and is custom built functions anyone can create.
A list of all published articles.