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 https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7 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 17, 2023

Hi L z,
I think the issue is that my excel do not have the fomula TEXTSPLIT. I tried using the formula on its own, but I could not find it from the list of keywords. Even if I add the _xlfn. prefix, the error still persist.

 

 

ydlazkov 

Lorenzo's avatar
Lorenzo
Silver Contributor
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 https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7 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

 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 17, 2024

    princeakortsu56 

    #1 I always got "#NAME?" => which version of Excel do you run?

    #2 when the cell under linked issues is empty => which/what issue? Develop/re-explain please as I'm afraid I don't understand

  • princeakortsu56's avatar
    princeakortsu56
    Copper Contributor
    Oct 17, 2024
    Thank for this suggestion however I always got "#NAME?" when the cell under linked issues is empty. Tried using LET function but still got same results. Any idea how to catch this kind of error?
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Apr 18, 2023
    Glad you have a solution & Thanks for providing feedback
    Take care...
  • ydlazkov's avatar
    ydlazkov
    Copper Contributor
    Apr 18, 2023
    Very clever.
    That FILTERXML worked for my "jurasic-ly" version of excel 😉
    Thanks heaps. Love your work.

Resources