Excel: Awkward concatenate xlookup formula

LazyClouds

Is this the right forum to ask excel related questions?

I have created a clunky formula (see below) that translates a string of letters into a collection of terms using a table. The table has two columns, column A is letters A-E and column B is a collection of terminology, so in cell A2 is "A" and in B2 is "climate change /" and in A3 is "B" and B3 is "basketball /" and A4 is "C" and B4 is "barbie dolls", etc.

The formula below is using cell N23 as the reference, so in N23 if I write "CA" the result of the formula will be climate change /barbie dolls

=CONCAT((IF(ISNUMBER(SEARCH("A",N23)),XLOOKUP("A",Table1[Code],Table1[Term],"?"),"")),(IF(ISNUMBER(SEARCH("B",N23)),XLOOKUP("B",Table1[Code],Table1[Term],"?"),"")),(IF(ISNUMBER(SEARCH("C",N23)),XLOOKUP("C",Table1[Code],Table1[Term],"?"),"")),(IF(ISNUMBER(SEARCH("D",N23)),XLOOKUP("D",Table1[Code],Table1[Term],"?"),"")),(IF(ISNUMBER(SEARCH("E",N23)),XLOOKUP("E",Table1[Code],Table1[Term],"?"),"")))

This formula is awkward as is, but if another term is added to the table of codes, for example "F" and "flooding", I need to add an extra part to the formula to lookup F.

Is there a neat way of using a lookup table to translate codes like this? Please help!

Ps, please don't suggest writing any background code. That is beyond my capabilities.

Spectral.Instance

You can omit the / character from the entries in your table, and just use this formula:

=TEXTJOIN(" / ",TRUE,XLOOKUP(MID(N23,SEQUENCE(1,LEN(N23)),1),Table1[Code],Table1[Term],"?"))

LazyClouds

Oh my goodness - that worked! How did you come up with that? It was wrecking my head!

Thank you so much!