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?
- When you import data from elsewhere, it often has blanks. If so, Excel formulas have to deal with them. A formula returning a value from a blank cell will display the result as 0. A formula lookup up a value in a blank cell returns #N/A error value. Excel formulas have to deal with these problems because you cannot avoid the blank cells at design time.
It's when you are creating the lookup table yourself that the "good practice" assertion comes into play. There is no need for blank rows in the lookup table. If you want white space between groups, change the row height to provide it. The #N/A problem you encountered is a bit of an "own goal" problem to my mind. Sure, someone like me can redesign the formula to overcome it. But you were stuck, and entirely due to something under your control. That's why I said "not a good practice."