Boolean logic is an elegant way to apply multiple criteria. In this worksheet we have sample order data in a table called “data”. Let’s use the XLOOKUP function to find the first order in March where the color is red. To make things clear, I’m going to work out the logic in helper columns first. Then, I’ll move that logic into the XLOOKUP function, to make an all-in-one formula. First, we’ll test for dates in March with the MONTH function. When we give MONTH the full set of dates in a range, we get back a month number for each date in a dynamic array. Since we only want dates in March, I simply need to compare this result to the number 3. When I update the formula, we get TRUE for all dates in March, and FALSE for all other dates. Next, I’ll test for the color red. This is just a simple expression that compares values in the color column to the string “red”. Again, we get a list of TRUE and FALSE values. Only orders where the color is Red return TRUE. Now, since we want Red and March, we need to use AND logic, which means we use multiplication. When I multiply these two helper columns together, the math operation automatically coerces the TRUE and FALSE values to 1s and 0s. This will become our lookup array. Notice this array is dynamic. If I temporarily change a color in March, the results update. We now have everything we need to configure the XLOOKUP function. For lookup_value, we use one. For lookup_array, we use our last helper column. For return_array, we use the full set of data. When I enter the formula, we get details for the first order in March where the color is Red. By default, XLOOKUP will find the first match. In other words, the first 1 in the array. Now to move this into an all-in-one formula, I’ll need to replicate this logic inside the lookup_array argument. To do this, I need to add parentheses, and use the same expressions we used in columns I and J, multiplied together. When I enter the formula, we get the same result. And if I check the LOOKUP array with the F9 key, you can see we have exactly the same array we have in column K. I can now delete the helper columns, and everything keeps working.
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.
