Forum Discussion

Kristin2185's avatar
Kristin2185
Copper Contributor
Dec 08, 2022
Solved

XLOOKUP partial match text

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:cod...
excel
Excel on Mac
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Dec 08, 2022

    Hi 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")

     

farukyazar's avatar
farukyazar
Copper Contributor
May 12, 2024
Hi,
What do the rightmost 2 and -2 do in the XLOOKUP formula?
=XLOOKUP(A6,A2:A3,B2:I3,"--",1,-2)
Lorenzo's avatar
Lorenzo
Silver Contributor
May 13, 2024

Hi farukyazar 

 

Take a look at the https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929. where the 6th and last (optional) argument is search_mode

  • farukyazar's avatar
    farukyazar
    Copper Contributor
    May 13, 2024
    L.z. I couldn't find the answer in this link for =XLOOKUP(A6,A2:A3,B2:I3,"--",1,-2)

Resources