At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called "names" which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this...
The SEQUENCE function is a dynamic array function that can generate multiple results. When used by itself on the worksheet, SEQUENCE outputs an array of results that "spill" onto the worksheet in a "spill range".
In...
Although Excel does not provide a formula function to append or combine ranges (you can append ranges with Power Query), you can use the LAMBDA function to create a custom function to combine ranges. In the example...
At the core, this formula uses the MID function to extract each character of a text string in reverse order. The starting character is given as a list of numbers in descending order hard-coded as array constant:
MID(...
The heart of this formula is the INDEX function, which is given the list as the array argument:
=INDEX(list
The second part of the formula is an expression that works out the correct row number as the formula is...
A MAC (Media Access Control) address is a unique identifier assigned to most network adapters. Two common IEEE 802 standards display a MAC address in 6 groups of 2 hexadecimal digits separated by a colon (:) or hyphen...
The goal of this formula is to return the next n birthdays in a set of 25 birthdays as shown, based on the current date. The complication is that the birthdays contain the year of birth, so we can't filter and sort the...
Excel doesn't have a way to cast the letters in a text string to an array directly in a formula. As a workaround, this formula uses the MID function, with help from the ROW and INDIRECT functions to achieve the same...
When working with Excel formulas, there are some situations where you want an array of sums rather than a single sum. Functions like SUM won't work, because they aggregate results and return a single value. The MMULT...
In this example, we have a table that contains 6 columns of codes, and each row of codes belongs to a group in column B. The goal is to lookup any code in C5:H15, and return the name of the group the code belongs to....
The first COLUMN function generates an array of 7 numbers like this:
{2,3,4,5,6,7,8}
The second COLUMN function generates an array with just one item like this:
{2}
which is then subtracted from the first array to...
The SEQUENCE function is a dynamic array function that can generate multiple results. Like other dynamic array functions, SEQUENCE outputs an array of results that "spill" onto the worksheet in a "spill range"....
The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by...
The SEQUENCE function is a dynamic array function that can generate multiple results. When used by itself on the worksheet, SEQUENCE outputs an array of results that "spill" onto the worksheet in a "spill range"....
Excel does not provide a formula function to append or combine ranges, either horizontally or vertically. You can use Power Query for this task, and this makes sense for data transformations that must be automated and...