The SUMPRODUCT function multiplies arrays together and returns the sum of products. The trick is to use simple array expressions to "cancel out" the irrelevant rates in the table. SUMPRODUCT then simply sums the rates that remain.
Working from...Read more
In this example, the goal is to perform a case-sensitive lookup on the color in the "Color" column. In other words, a lookup value of "RED" must return a different result from a lookup value of "Red". By default, Excel is not case-sensitive and this applies to standard lookup...Read more
At a high level, this example is about finding a minimum value based on multiple criteria. To do that, we are using the MIN function together with two nested IF functions:
{=MIN(IF(...Read moreNormally, 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 create a lookup array on the fly composed...Read more
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 chaining together three expressions that use...Read more
In this example, criteria are entered in the range F5:H6. The logic of the formula is:
item is (tshirt OR hoodie) AND color is (red OR blue) AND city is (denver OR seattle)
The filtering logic of this formula (the include argument) is applied with the...Read more
This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D15, which contains all of the data without headers. The include argument is...Read more
The XMATCH function offers new features not available with the MATCH function. One of these is the ability to perform a "reverse search", by setting the optional search mode argument. The default value for search mode is 1, which specifies a normal "first to last" search. In this mode, XMATCH...Read more
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 functions to parse and compare text.
Note: this formula performs an...Read more
Note: a simpler approach would be to alter the table used by VLOOKUP directly. But this example explains the mechanics of testing and overriding output from VLOOKUP.
This formula is based on a simple grading example explained in...Read more