Basic array formula example
The example on this page shows a simple array formula. Working from the inside out, the expression:
C5:C12-D5:D12
Results in an array containing seven values:
{17;19;32;25;12;26;29;22}
...Read moreDave Bruns
Return array with INDEX function
It is surprisingly tricky to get INDEX to return more than one value to another function. To illustrate, the following formula can be used to return the first three items in the named range "data", when entered as a multi-cell array formula.
{=INDEX(data,{1,...Read moreSum text values like numbers
The heart of this formula is a basic INDEX and MATCH formula, used to translate text values into numbers as defined in a lookup table. For example, to translate "EX" to the corresponding number, we would use:
=INDEX(value,MATCH("EX",code,0))
...Read moreMost frequent text with criteria
In this example, the goal is to return the most frequently occurring text based on one or more supplied criteria. Working from the inside out, we use the MATCH function to match the text range against itself, by giving MATCH the same range for lookup value...Read more
Conditional mode with criteria
The MODE function has no built-in way to apply criteria. Given a range, it will return the most frequently occurring number in that range.
To apply criteria, we use the IF function inside MODE to filter values in a range. In this example, the IF function filters values by group with an...Read more
Data validation require specific multiple
In this example, the goal is to create a data validation rule that will only accept numbers that are a specific multiple of another number. In the worksheet shown, the multiple is 100. Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns...Read more
Conditional formatting based on another cell
Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells greater than a specific value. However, by using your own formula, you have more flexibility and control.
In this example, a conditional formatting...Read more
Find and replace multiple values
There is no built-in function to perform a series of find and replace operations in Excel, but this article explains how you can cobble together a formula that does the same thing. The text to look for and replace with is stored directly on the worksheet in a table, and retrieved with the INDEX...Read more
Count keywords cell contains
Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only counts instances of different keywords.
The core of this formula is the ISNUMBER + SEARCH approach to finding text in a cell, which is...Read more
Count values out of tolerance
This formula counts how many values are not in range of a fixed tolerance. The variation of each value is calculated with this:
ABS(data-target)
Because the named range "data" contains 10 values,...Read more