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
Nov 12, 2024

Hello Lorenzo

I'm in need of this formula again, but this time I have a list of promotional codes used in different orders ("base.coupons") and I want to identify the type of promo each codes inside the cell is ("Code Source Channel"). 

I have a different excel where I have the list of codes and respective type, and in this list I have Unique Codes that I can identify just by looking at the first characters until the first "-" appears. In the excel "20241107_Promo Codes eD2C Kiehls.es.xlsx" if I know all of the variants of the Unique Codes I've added them to the file, but mostly I do not, so I Identify them only by the first characters until the first "-". Examples:
 

Because of this when I use the formula below, I only get results for exact matches, in this case Loyalty Unique Codes (which I have all of them identified) and Generic Codes. The rest of codes are Unique Codes and I only know the start of them, not the whole list of variants of the code, is there a way to change the formula so it can find all the codes even if I only have the part of the initial code?

=TEXTJOIN(", ";;XLOOKUP(TEXTSPLIT(A3921;",");'[20241107_Promo Codes eD2C Kiehls.es.xlsx]Sheet1'!$A$2:$A$89;'[20241107_Promo Codes eD2C Kiehls.es.xlsx]Sheet1'!$B$2:$B$89;"Not found"))

I would really appreciate your help!


  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 15, 2024

    Hi

    No time to look at your new issue these days. Feel free to start a new discussion with the above details

Resources