Forum Discussion
Multistep: Lookup values based on another table and then concatenate by an ID
Assuming you have Microsoft 365 (subscription version), you may use the following formulas for cells C3 to E3:
=TEXTJOIN(", ",TRUE,FILTER(J$3:J$6,ISNUMBER(SEARCH(J$3:J$6,B3)),"")) =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(C$3:C$8,A$3:A$8=A3,""))) =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(VLOOKUP(C$3:C$8,J$3:K$6,2,FALSE),A$3:A$8=A3,"")))
Assuming you have Microsoft 365 (subscription version), you may use the following formulas for cells C3 to E3:
=TEXTJOIN(", ",TRUE,FILTER(J$3:J$6,ISNUMBER(SEARCH(J$3:J$6,B3)),""))
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(C$3:C$8,A$3:A$8=A3,"")))
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(VLOOKUP(C$3:C$8,J$3:K$6,2,FALSE),A$3:A$8=A3,"")))
byundt Thank you! That worked almost perfectly. The only issue I'm having now is with the 3rd statement if there are blanks in the rows it returns N/A. Do you have any advice for that issue? Thanks again.
The suggested formulas return an empty string (looks like a blank) if the corresponding values in columns A or B are blank.
I can make the third formula return #N/A if your lookup table contains blank cells (not a good practice). If that's your problem, you can overcome it using:
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(IFNA(VLOOKUP(C$3:C$8,J$3:K$6,2,FALSE) & "",""),A$3:A$8=A3,"")))
The formula I you just added worked great, thank you! Out of curiosity why is having blanks not best practice? My lookup table does not have blanks but some of the rows might contain no key words and so the row would be blank. Do you have a recommended best practice instead?