Forum Discussion
Multiple lookup values on XLOOKUP formula
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
Hi ydlazkov
Assuming I understood (not sure...)
in E3:
=TEXTJOIN(", ",,XLOOKUP(TEXTSPLIT([@[Linked Issues]],";"), ChangeRequests[Key], ChangeRequests[Type], "Not found"))
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"))
😞
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?