XLOOKUP Part 4 - Partial and Exact Matching

Seeking partial matches (sounds like an unfussy dating agency!) suddenly became a lot easier too.  You can use wildcards if you want to – just set the match_mode to 2:

Here, I am searching for J?n*n* - which is fine as long as you know what the wildcard characters mean:

• ? means “any character”, but just one character. If you wanted to make space for two and only two characters you would use ??
•  * means “any number of characters’ – including zero.

For example, M?n*m* would identify “Manmade”, “minimum” and “Manikum” but would not accept “millennium”. Here, our formulae

=XLOOKUP(G184,H174:H179,I174:I179,,2)

=XLOOKUP(G184,H174:H179,I174:I179,,2,-1)

would locate the first and last items that satisfied the condition J?n*n* (i.e. “Jonathan” and “Jonny” respectively).

But what if you wanted an exact match with case sensitivity?  You just have to think a little but outside of the proverbial box:

Here, we use another feature of XLOOKUP – its ability to search a virtual vector, i.e. one that has been constructed in memory, rather than physically within the spreadsheet cells. Consider the formula

=XLOOKUP(TRUE,EXACT(H145:H154,G159),I145:I154)

 Here, the interim calculation =EXACT(H145:H154,G159), looks at the range H145:H154 and deduces whether the cells are an exact match for the selection ‘Sum Product’ in cell G159. The EXACT function would evaluate as 

{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}

 Therefore, the formula coerces to

=XLOOKUP(TRUE,{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE},I145:I154)

and then the formula becomes simple to understand.