In this example, we need to construct logic that filters data to include:
account begins with "x" AND region is "east", and month is NOT April.
The filtering logic of this formula (the include argument) is created by...

Sometimes you encounter data that mixes units directly with numbers (i.e. 8km, 12v, 7.5hrs). Unfortunately, Excel will treat the numbers in this format as text, and you won't be able to perform math operations on such...

Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use boolean logic to...

In the first formula above, we are using the FIND function to locate the starting position of the number. For the find_text, we are using the array constant {0,1,2,3,4,5,6,7,8,9}, this causes the FIND function to...

Inside SUMPRODUCT, we use the LEFT function on the range of numbers like this:
LEFT(B5:B11,2)
This creates an array of results like this:
{"25";"25";"35";"45";"25...

At the core, this formula relies on the SUMPRODUCT function to sum values in matching columns in the named range "data" C5:G14. If all data were provided to SUMPRODUCT in a single range, the result would be the sum of...

We need a way to split text at a certain marker that corresponds to a certain number of words. Excel doesn't have a built-in function to parse text by word, so are using the SUBSTITUTE function's "instance" argument to...

Data validation rules are triggered when a user adds or changes a cell value.
In this formula, the LEFT function is used to extract the first 3 characters of the input in C5.
Next, the EXACT function is used to...

FIND returns the position (as a number) of the first occurrence of a space character in the text. This position, minus one, is fed into the LEFT function as num_chars.
The LEFT function then extracts characters...

To get the date, we extract the first 10 characters of the value with LEFT:
LEFT(B5,10) // returns "2015-03-01"
The result is text, so to get Excel to interpret as a date, we wrap LEFT in DATEVALUE, which...

The FIND function finds the first space character (" ") in the name and returns the position of that space in the full name. The number 1 is subtracted from this number to account for the space itself. This number is...

The first expression uses LEFT and UPPER to capitalize the first letter:
=UPPER(LEFT(B5))
No need to enter 1 for num_chars in LEFT, since it will default to 1. The second expression extracts the remaining characters...

The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a #VALUE error.
To workaround this limit you can use boolean logic and the LEFT, MID, and EXACT...

Working from the inside out, this formula first concatenates the last name in column C with the first letter of the first name from column B:
LEFT(B5)&C5
The LEFT function is normally configured with num_chars,...

The 1st dimension
To get the first dimension, we are using this formula in C4:
=LEFT(B4,FIND("x",B4)-1)
This works by extracting text starting at the LEFT. The number of characters is calculated by...