Question Details

No question body available.

Tags

excel macos excel-formula excel-2010 excel-2021

Answers (3)

Accepted Answer Available
Accepted Answer
May 19, 2026 Score: 5 Rep: 3,240 Quality: High Completeness: 70%

The variant with the TEXTJOIN and FILTER functions can be used, you just need to rearrange the arguments. In addition, you need to protect yourself in case one phrase is part of another.
=TEXTJOIN(", ",,FILTER(NPCIDs, ISNUMBER(SEARCH(", " & NPCNames & ", ",", " & $D2 & ", ")), ""))
Names

May 19, 2026 Score: 2 Rep: 66 Quality: Low Completeness: 40%

Should work using the TEXTSPLIT function

=TEXTJOIN(", ", TRUE, XLOOKUP(TEXTSPLIT($D2,", "),NPCNames,NPCIDs,"NPC invalid",0,-1)&"")
May 20, 2026 Score: 2 Rep: 62,128 Quality: Low Completeness: 70%

You can use XLOOKUP except you don't have TEXTSPLIT in Excel for MAC 2021. So you need to split the comma separated string into an array differently as we do in steps a, b and c in the formula below:

=LET(
    a, SUBSTITUTE(D2, ",", REPT(" ", 99)),
    b, IF(SEQUENCE(, 10) = 1, 1, INDEX(SEQUENCE(, 9, 99, 99), SEQUENCE(, 10) - 1)),
    c, TRIM(MID(a, b, 99)),
    d, XLOOKUP(c, $A$3:$A$10, $B$3:$B$10, ""),
    e, TEXTJOIN(", ", TRUE, d),
    e
)

enter image description here

If you have more than ten phrases, you must change the SEQUENCE function arguments.