The MATCH function returns the position or "index" of the first match based on a lookup value in a range.
MATCH supports wildcard matching with an asterisk "*" (one or more characters) or a question mark "?" (one character), but only when the third...Read more
Each item in rng is compared to each item in values and the result is an array of TRUE or FALSE values.
The double negative will force the TRUE and FALSE values to 1 and 0 respectively. Since SUMPRODUCT...Read more
The formula looks complex, but the mechanics are in fact quite simple.
As with most formulas that split or extract text, the key is to locate the position of the thing you are looking for. Once you have the position, you can use other functions to extract what...Read more
If you've ever tried to apply conditional formatting with a formula, you know the hardest part is making sure the formula actually works. Here's an easy way to test the formula, before you use it in a rule.Read more
This formula uses the TEXT function to concatenate the month and year of each date. Then, the two dates are tested for equality. TEXT is a useful function that allows you to convert a number to text in the text format of your choice. In this case the format is the custom date format "myyyy",...Read more
How do you figure out what formula to use? A teardown of 5 good formulas you can use to highlight dates based on a common month and year value using conditional formatting.Read more
The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. The TODAY function returns the current date. Dates in Excel are simply large serial numbers, so you can create a new relative date by adding or subtracting days. TODAY() + 30 creates a new date 30...Read more
The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values. Because the reference to B4 is fully relative, it will update as the rule is applied to each cell in the range,...Read more
The DATE function creates a proper Excel date with given year, month, and day values. Then, it's simply a matter of comparing each date in the range with the date created with DATE. The reference B4 is fully relative, so will update as the rule is applied to each cell in the range, and any...Read more
