Forum Discussion
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 - which contains the linked change request tickets, having multiple values and separated by semicolon.
Then another table that holds data for change request tickets. It has similar columns as incidents table, but the linked issues columns is for the incidents tickets. (Refer to attached sample file screenshots)
I'm using XLOOKUP to get retrieve the incident reference id and issue type values. But since my lookup value is multiple values, I got a lot of 'not found'.
How can I have a XLOOKUP with lookup value is multiple values separated by semicolon? What would the formula be like?
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
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!
Hi
No time to look at your new issue these days. Feel free to start a new discussion with the above details
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!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
- 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?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
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"))😞
