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

     

Lorenzo's avatar
Lorenzo
Silver Contributor
Apr 04, 2023

Hi ydlazkov 

Assuming I understood (not sure...)

 

in E3:

=TEXTJOIN(", ",,XLOOKUP(TEXTSPLIT([@[Linked Issues]],";"), ChangeRequests[Key], ChangeRequests[Type], "Not found"))

 

  • inescastilho's avatar
    inescastilho
    Copper Contributor
    Oct 17, 2024

    Hi Lorenzo,

     

    I've used this formula, and it seems to work, at least I do get a result, but not the expected one, as I only get value "Not Found", when I should get the values of column Product Description for each EAN:

    Can you tell me what I'm doing wrong? Is it because the columns of EANs don't have the same formatting? One is TEXT the other is Number?...

    Would really appreciate your help!

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

      Hiinescastilho 

      Late to the party (lol) vs SergeiBaklan reply. If you have a possible scenario like below this might be a bit more complex, but doable to a certain extent... - set clear rules (+ expected result) in such a case

       

      Cheers

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 17, 2024

      inescastilho 

      Yes, if EAN are numbers only, convert split texts into numbers. As variant you may use

      ...  1*TEXTSPLIT(...

      • inescastilho's avatar
        inescastilho
        Copper Contributor
        Oct 18, 2024
        Thank you so much, it worked perfectly!

        First time user of this community, but I'll definitely be coming back 🙂
  • kimmy0404's avatar
    kimmy0404
    Copper Contributor
    Nov 01, 2023
    Hi L z,
    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
      Nov 01, 2023

      Hi kimmy0404 

       

      Any idea how to catch this kind of error?

      Not with the too few information you shared I'm afraid. Follow the guidelines in Welcome to your Excel discussion space! please - at least:

      - Excel version and operating system

      - Picture showing how your data are strucured

      - Formula currently used

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

Resources