Forum Discussion

ydlazkov's avatar
ydlazkov
Copper Contributor
Apr 04, 2023
Solved

Multiple lookup values on XLOOKUP formula

Hi all, Hoping that someone can help me.   I got 2 tables in Excel; 1 table holds data for incident tickets with columns like reference number, issue type, priority, status, and linked issues - wh...
excel
  • Lorenzo's avatar
    Lorenzo
    Apr 17, 2023

    Hi ydlazkov 

     

    Prefix "_xlfn." means a function (in use) isn't found/available to you and the picture you shared shows that you don't have TEXTSPLIT

     

    According to the doc. the function is available on Windows Version 2208 Build 15601 but you're not at that stage yet (Version 2008 Build 13127.21624)

     

    In the meantime you can replace TEXTSPLIT with FILTERXML as follow (sample attached):

    =TEXTJOIN(", ",,
  XLOOKUP(
    FILTERXML("<t><w>" & SUBSTITUTE(Change[@[Linked Issues]], ";", "</w><w>") & "</w></t>", "//w"),
    Incident[Key], Incident[Type], "Not found"
  )
)

    NB: FILTERXML won't work on Mac and Excel Web/Online

     

ydlazkov's avatar
ydlazkov
Copper Contributor
Apr 05, 2023

Hi L z.
thanks for your reply and suggestion. what am I doing wrong when I got a "#NAME?" error?
i checked all the variables in the formula and I can't seem to find what causing the error.

 

The formula looks like this...
=TEXTJOIN(", ",,XLOOKUP(TEXTSPLIT(Change[@[Linked issues]],";"),Incident[Key],Incident[Type], "Not found"))

 

😞

Lorenzo's avatar
Lorenzo
Silver Contributor
Apr 05, 2023

Hi ydlazkov 

 

You did not mention the version of Excel you run, I assumed 365...

If you run 2021 TEXTSPLIT isn't available to you, probably the reason of the #NAME? error you get

 

Could you clarify your version of Excel + if, in principle, the above proposal does what you expect?

  • ydlazkov's avatar
    ydlazkov
    Copper Contributor
    Apr 14, 2023
    Hi L z,
    Apologies for not mentioning the excel version.
    Yes, it is MS 365. Excel version 2008 (Build 13127.21624))
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Apr 14, 2023

      Hi ydlazkov 

      If you run 365 what I shared must work. Please download the sample I shared & check

      • ydlazkov's avatar
        ydlazkov
        Copper Contributor
        Apr 16, 2023

        Lorenzo 

        Hi L z,
        First of all, I thank you for your patience and help.
        I downloaded the file you shared and recreated it on my own actual file. Just a question though,...
        What is the prefix "_xlfn." means? I think this is the keyword that's causing the syntax error.

        Regards,

        ydlazkov

Resources