Multiple lookup values on XLOOKUP formula

[ydlazkov]

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?

[#2]

Hi @ydlazkov 

Assuming I understood (not sure...)

in E3:

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

[#3]

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"))

😞

[#4]

Hi @ydlazkov 

You did not mention the version of Excel you run, I assumed 365...

If you run 2021 TEXTSPLIT 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?

[#5]

Hi L z,
Apologies for not mentioning the excel version.
Yes, it is MS 365. Excel version 2008 (Build 13127.21624))

[#6]

Hi @ydlazkov 

If you run 365 what I shared must work. Please download the sample I shared & check

[#7]

@Lorenzo 

Hi L z,
First of all, I thank you for your patience and help.
I downloaded the file you shared and recreated it on my own actual file. Just a question though,...
What is the prefix "_xlfn." means? I think this is the keyword that's causing the syntax error.

Regards,

ydlazkov

[#8]

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 

[#9 · Accepted solution]

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

[#10]

Very clever.
That FILTERXML worked for my "jurasic-ly" version of excel 😉
Thanks heaps. Love your work.

[#11]

Glad you have a solution & Thanks for providing feedback
Take care...

[#12]

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?

[#13]

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