Jan 14 2022 06:44 AM
Can I use a wildcard to return data based on a partial match?
A | B | C | D | ||
Adobe Acrobat DC | 7 | Adobe Acrobat DC | 7 | ||
Adobe After Effects 2021 | #N/A | Adobe Acrobat DC (64-bit) | 168 | ||
PLSQL Developer 13 (32 bit) | 1 | Adobe Acrobat Reader DC | 1954 | ||
Apro Banking Gateway | 1 | Adobe Acrobat SCCL Windows 64BIT | 4 | ||
Articulate 360 | 1 | Adobe After Effects 2022 | 1 | ||
ASG-Remote Desktop 2018 | 15 | Adobe AIR | 2 | ||
Jira Core (Server) | 1 | Adobe Animate 2022 | 1 | ||
Jira Software (Server) | 4 | Adobe Bridge 2020 | 1 |
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?
Jan 14 2022 08:08 AM
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.