which returns 0347, the order number of the first record that meets supplied criteria. Note XLOOKUP is not case-sensitive. In the example shown, we are looking for the order number of the first order to Chicago over $250. We are constructing a lookup array using the following expression and boolean logic: When this expression is evaluated, we first get two arrays of TRUE FALSE values like this: When the two arrays are multiplied by one another, the math operation results in a single array of 1’s and 0’s like this: We now have the following formula, and you can see why we are using 1 for the lookup value: XLOOKUP matches the 1 in 8th position, and returns the correponding 8th value from B5:B14, which is 0347.
With a single criteria
As seen above, math operations automatically coerce TRUE and FALSE values to 1’s and 0’s. Therefore, when using multiple expressions, a lookup value of 1 makes sense. In cases where you have only a single criteria, say, “amount > 250”, you can look for TRUE instead like this: Alternatively, you can force the TRUE FALSE values to 1’s and 0’s, and use 1 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.
