which matches on "RED" (case-sensitive) and returns the entire row.
By itself, the XLOOKUP function is not case-sensitive. A lookup value of "RED" will match "red", "RED", or "Red". We can work around this limitation by constructing a suitable lookup array for XLOOKUP with a logical expression.
Working from the inside out, to give XLOOKUP the ability to match case, we use the EXACT function like this:
Here, because each of the two expressions returns an array of TRUE FALSE values, and because these arrays are multiplied together, the math operation coerces the TRUE and FALSE values to 1s and 0s. It is not necessary to use the double-negative.
As the lookup value remains 1, as in the formula above.
First and last match
Both formulas above will return the first match of "RED" in a data set. If you need the last match, you can perform a reverse lookup by setting the search mode argument for XLOOKUP to -1:
XLOOKUP can handle arrays natively, which makes it a very useful function when constructing criteria based on multiple logical expressions. In the example shown, we are looking for the order number of the first order to Chicago over $250. We are...
One of the nice advantages of XLOOKUP over VLOOKUP is that XLOOKUP can work with arrays directly, instead of requiring ranges on a worksheet. This makes it possible to assemble arrays in the formula, and push these into the function. Working one...
The XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2. In the example shown, XLOOKUP is configured to match the value entered in cell E5, which may appear...
The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal...
The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.