Dec 08 2022 04:26 AM
Hi,
I'm having an issue with partial matching text in the lookup cell of my X lookup function.
Formula:
=XLOOKUP("*"&A2&"*";$I$6:$I$13;$J$6:$J$13;"not found";2)
A2 = app:android:sv:code=0
Lookup array has "app" as value (I13) (though written without "", ie app) and return array value is also app (J13).
I only get "not found" as output when executing this formula. I want the output to be "app" for A2 in the output cell.
I'm on a Mac, Swedish computer with English language Excel.
What can I do??
Dec 08 2022 04:41 AM
Hi @Kristin2185,
You can do that with the FILTER function.
I combined it with the TAKE function here in case there are more than one partial match, then it will only return the first match.
=TAKE(FILTER($J$6:$J$13,IFERROR(FIND($I$6:$I$13,A2),0)=1,"not found"),1)
Regards,
Zach
Dec 08 2022 05:40 AM
Hi @Zach Prins
Thanks for your reply!
I tried your formula without much success, unfortunately.
See attached my spreadsheet (my wanted outcome is exemplified in column D (I want all cells to populate with the text in J6:J13 when they match the list in I6:I13))
Should the formula you suggested still work or will I need to do something differently?
Best,
Kristin
Dec 08 2022 06:06 AM
Dec 08 2022 06:21 AM
I use Office365, on a MacBook.
Got the wanted results now, thank you so much!!
I think the issue was that this formula populates two columns with data and so I got a #spill error that I didn't understand. Worked perfectly when I made sure there were two empty columns available.
Dec 08 2022 06:23 AM
Dec 08 2022 07:15 AM
SolutionHi @Kristin2185
Slightly different approach that should work if your Source strings are as consistant as those you shared where there's a semi-column following the keywords you want to match
in C2:
=FILTER(TBL_Lookup[Mapping], ISNUMBER(SEARCH(TBL_Lookup[Keyword],A2)), "No match")
Dec 08 2022 08:59 AM
@Kristin2185, in that first reference of your formula you're referencing columns I and J, it should be only column J.
=TAKE(FILTER($J$6:$J$13...
Dec 09 2022 03:55 AM
Dec 08 2022 07:15 AM
SolutionHi @Kristin2185
Slightly different approach that should work if your Source strings are as consistant as those you shared where there's a semi-column following the keywords you want to match
in C2:
=FILTER(TBL_Lookup[Mapping], ISNUMBER(SEARCH(TBL_Lookup[Keyword],A2)), "No match")