XLOOKUP with Wildcards

Occasional Visitor

Can I use a wildcard to return data based on a partial match?


Adobe Acrobat DC7  Adobe Acrobat DC7
Adobe After Effects 2021#N/A  Adobe Acrobat DC (64-bit)168
PLSQL Developer 13 (32 bit)1  Adobe Acrobat Reader DC1954
Apro Banking Gateway1  Adobe Acrobat SCCL Windows 64BIT4
Articulate 3601  Adobe After Effects 20221
ASG-Remote Desktop 201815  Adobe AIR2
Jira Core (Server)1  Adobe Animate 20221
Jira Software (Server)4  Adobe Bridge 20201


I have been trying to use XLOOKUP to return the value of the relevant cell in column D into column B.  This works perfectly when column A and column C are an exact match.

However when there is a difference - such as Adobe After Effects 2021 in column A and Adobe After Effects 2022 in column C  the XLOOKUP fails as can be seen with the #N/A in column B.

I have been trying =XLOOKUP("*"&A2&"*",C2:C9,D2:D9,,2).

Is XLOOKUP the best way of achieving this or is there a better option? 

1 Reply


In general formula works. What you try to do is find 

"anything" & "Adobe After Effects 2021" & "anything" in column C - there is no such text in it.

Perhaps you need helper column where to keep only "core" text from column A (e.g. "Adobe After Effects") and lookup based on it.