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

     

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

Resources