Why XLOOKUP formula does not work with implicit wild cards?

Question

In the following example the implicit wild cards do work when COUNTIF is involved.

=XLOOKUP(1,1/COUNTIF(D2,"asterisk"&A1&"asterisk"),B1)

Please explain why it doesn't work int he following manner

=XLOOKUP(D1,"asterisk"&A1&"asterisk",B1,,2)

Answer 1

The order of the arguments is different for all the various ...IF() functions and the XLOOKUP() function:

=COUNTIF( range , lookup_value)

=XLOOKUP( lookup_value , lookup_range , return_range )

You correctly added the wildcard match_mode to the XLOOKUP. But you need to re-swizzle your arguments to be:

=XLOOKUP( "*"&A1&"*" , lookup_range, return_range , , 2 )