XLOOKUP wildcard matching issue

[RSGrayson]

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?

[#2]

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.

[#3]

@RSGrayson 

XLOOKUP only ever returns one result. What you probably need is the FILTER function.

[#4 · Accepted solution]

@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 

[#5]

@RSGrayson 

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.

[#6]

@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?

[#7]

@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!