I have list of partner name codes, delimited by space. Like the one shown in below,

I have another table(E:F), from where I have to map them to show the partner names like the column C, perhaps i am not able to understand how to make it happen,

I have tried using this formula which brings only one partner name but when there are multiple it does not shows up, do i need to add another function like TEXTJOIN or what I am doing wrong here.

=IFERROR(VLOOKUP(IFERROR(REGEXEXTRACT(A2,JOIN("|",FILTER($E$2:$E,$E$2:$E<>""))),""),$E$2:$F,2,0),"")

Link To GS

CodePudding user response：

See my sheet ("Erik Help"). The following formula is in cell B1:

=ArrayFormula({"PARTNER NAMES";IF(A2:A="",,REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(VLOOKUP(SPLIT(A2:A," ",0,1),D:E,2,FALSE)&",")),,COLUMNS(SPLIT(A2:A," ",0,1))))),",$",""))})

This one formula produces the header (which you can change within the formula itself as you like) and all results for all rows.

IF(A2:A="",,...) means if a cell in Col A is blank, then the result in the same row of Col B will also be blank (i.e., null).

SPLIT (the first time in the formula) will split the Col-A values at the spaces.

VLOOKUP will try to find each split value in the D:E list. If found, the full name will replace the initials. If not found, IFERROR will return null.

You will see &",". That is appending a comma to any full names that are returned.

TRANSPOSE(QUERY(TRANSPOSE...),,COLUMNS())) is what many call "QUERY Smash." It basically, flips the remaining results of the VLOOKUP into columns instead of rows, turns everything into headers (to get them in one cell per column) and then flips them back to row orientation.

TRIM gets rid of spaces where no names were found in the full list.

REGEXREPLACE(... ,",$","") replaces any final comma that has no name after it with null.