Forum Discussion
XLOOKUP wildcard matching issue
Hello,
I can't figure out why the XLOOKUP formula in the attached file is returning only 1 match when there should be 3. Any ideas?
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
By the way, to the people to whom I sent messages saying I couldn't post a message, my apologies. For whatever reason, I finally was able to post a message, but I'm posting this one to see if it is still working.
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!
For which text should there be 3 matches? I see only unique values.
Other issues:
Table "VA_List" contains bad data.
1. Most entries have a period at the end - but not all. The search criteria in table "MedConV" is without period.
2. Some entries have extra spaces at the end.
I would suggest to clean up the data.
You could use data validation in table "MedConV" to ensure that only valid text entries are possible.
- Hi @OP! 👋
The issue might be due to wildcard matching behavior in XLOOKUP. Here are a few things to check:
Ensure correct wildcard usage:
Use * to match any number of characters and ? to match a single character.
Example: XLOOKUP("*search*", A2:A10, B2:B10)
Check for leading or trailing spaces:
Sometimes extra spaces in your data can prevent matches. You can use TRIM() on your lookup array to clean it up.
Verify data type compatibility:
Make sure both your lookup value and lookup range are formatted as text or the same data type.
I ran into a similar issue automating menu updates on Albaik Menu Website, https://menualbaik.com where I had to clean up raw data and match menu items using wildcards. If you'd like, I can help you troubleshoot further—just let me know!
