Forum Discussion
XLOOKUP wildcard matching issue
RSGrayson There were no wildcards used in your original formula. To return the first matching record that contains the lookup_value, you need to concatenate an asterisk ("*") to both the start and end of the lookup_value as follows:
=XLOOKUP("*" & [@[Medical Conditions]] & "*", VA_List[VA List of Disablities], VA_List[VA List of Disablities], "Not found", 2)If further explanation is needed, please see: https://exceljet.net/formulas/xlookup-wildcard-match-example
RSGrayson There were no wildcards used in your original formula. To return the first matching record that contains the lookup_value, you need to concatenate an asterisk ("*") to both the start and end of the lookup_value as follows:
=XLOOKUP("*" & [@[Medical Conditions]] & "*", VA_List[VA List of Disablities], VA_List[VA List of Disablities], "Not found", 2)
If further explanation is needed, please see: https://exceljet.net/formulas/xlookup-wildcard-match-example
djclements , thanks for your help. I found about the need to use the wildcard syntax ("*") in the lookup value parameter after posting the message. I applied it, and it worked.
Now I have a different XLOOKUP wildcard matching issue. I can't get a lookup value that is different from the matching lookup array by only one character, a hyphen, to match. The lookup value is TC AIMS USER, and the lookup array I want to match it to is TC-AIMS USER. I applied the wildcard syntax in the lookup parameter value. Why can't I get it to match?
RSGrayson There are three wildcard characters that can be used to perform a wildcard character match, with the optional [match_mode] argument set to 2:
Wildcard Character Used to Find ? (question mark) Any single character * (asterisk) Any number of characters ~ (tilde) followed by ?, *, or ~ A question mark, asterisk, or tilde The question mark can be used in this case to match "TC?AIMS USER" with either "TC AIMS USER" or "TC-AIMS USER".
Having said that, the SUBSTITUTE function can also be used on both the lookup_value and lookup_array to treat all hyphens as spaces. For example, the formula in your original post could be modified as follows:
=XLOOKUP("*" & SUBSTITUTE([@[Medical Conditions]], "-", " ") & "*", SUBSTITUTE(VA_List[VA List of Disablities], "-", " "), VA_List, "Not found", 2)I hope that helps. Cheers!