This is a classic data mismatch problem, and the issue isn't with your logic but with how XLOOKUP handles wildcards in this specific scenario. Your formula is correct in structure; the failure likely stems from a subtle mismatch in data types or hidden characters between the two worksheets.
The core challenge here is that you're asking XLOOKUP to match a partial string, but the function's behavior with wildcards depends entirely on the lookup array being text. If Worksheet2's Column D contains numbers, dates, or values stored as text with trailing spaces, the asterisk wildcard won't behave as expected. Additionally, XLOOKUP requires an exact match mode when using wildcards, you've set this to 0, which is correct, but if the lookup value itself contains characters like parentheses or punctuation that interfere with the wildcard syntax, the search fails silently.
For practical resolution, start by confirming that both columns are formatted as text. Use the `=ISTEXT()` function on a sample cell in each column to verify. If Worksheet2's Column D is not text, wrap your lookup array in `TEXT()` or use a helper column with `=TEXT(D2,"@")`. Also, consider using `=TRIM()` on both the lookup value and the lookup array to eliminate invisible spaces. A more robust alternative is `=FILTER(Worksheet2!C:C,ISNUMBER(SEARCH(B2,Worksheet2!D:D)))`, this function avoids wildcard syntax entirely and searches for the substring within each cell, returning the corresponding ID when found.
Your instinct to use partial matching is exactly right for this scenario. The solution is simply to strip away the assumptions about data cleanliness and handle the text comparison explicitly. Once you apply `TRIM` and confirm text formatting, that XLOOKUP will work, or the FILTER approach will give you the same result without the wildcard headaches.