With XLOOKUP’s default settings for match_mode (exact) and search_mode (first to last) the formula matches the first record where: account begins with “x” AND region is “east”, and month is NOT April. The first match is the fourth record (row 8) in the example shown. In this example, the required criteria is: account begins with “x” AND region is “east”, and month is NOT April. For each of the three separate criteria above, we use a separate logical expression. The first expression uses the LEFT function to test if Account begins with “x”: Because we are checking 12 values, the result is an array with 12 TRUE FALSE values like this: The second expression tests if Region is “east” using the equal to (=) operator: As before, we get another array with twelve TRUE FALSE values: The third expression needs to exclude the month of April. The easiest way to do this is to test for the month of April directly with the MONTH function: Then use the NOT function to to reverse the result: which creates an array correctly describing “not April”: Next, all three arrays are multiplied together, and the math operation coerces the TRUE and FALSE values to 1s and 0s: In Boolean arithmetic, multiplication works like the logical function AND, so the final result is a single array like this: The formula can now be rewritten like this: With 1 as a lookup value, and default settings for match_mode (exact) and search_mode (first to last), XLOOKUP matches the first 1 (fourth position) and returns the corresponding row in the result array, which is B8:E8.
Last match
By setting the optional search mode argument to -1, you can locate the “last match” with the same criteria like this:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
