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
- RSGraysonCopper Contributor
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.
- djclementsBronze Contributor
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
- RSGraysonCopper Contributor
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?
- djclementsBronze Contributor
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!
- Detlef_LewinSilver Contributor
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.
- haseebjadoon05Copper ContributorHi @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!