which matches on "RED" (case-sensitive) and returns the entire row.
How this formula works
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:
To use XLOOKUP with multiple logical, build expressions with boolean logic and then look for the number 1. In the example XLOOKUP is used to lookup the first sale to Chicago over $250. The formula in G6 is: = XLOOKUP ( 1 ,( D5:D14 = "chicago...
To use XLOOKUP with multiple criteria, you can concatenate lookup values and lookup arrays directly in the formula. In the example shown, the formula in H8 is: = XLOOKUP ( H5 & H6 & H7 , B5:B14 & C5:C14 & D5:D14 , E5:E14 ) XLOOKUP...
To use XLOOKUP to match values that contain specific text, you can use wildcards and concatenation . In the example shown, the formula in F5 is: = XLOOKUP ( "*" & E5 & "*" , code , quantity , "no match" , 2 )...
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.