Multistep: Lookup values based on another table and then concatenate by an ID

[Joe_JL]

Hi Everyone, thank you in advance for any help you are able to provide! I consider myself good at excel and power query but this is quite a beast in my opinion. 

Device and OS platform: PC, Windows 10

Excel product name and version number: Excel 2102

As much context of what you're trying to do - and why:

• (I added notes in the excel file as well that should be useful) 
• The overall goal of this is to generate a series of key words at the ID level based off comparing the lookup table (name) to the descriptions within each Key_ID. 
• I am trying to use a lookup table to identify certain words in a Description (1 description per row) and then in a new column (Results) have the "keyword" appear. Then In another new column (All Results per ID) concatenate all keywords together by key_ID from every row within that key_ID.
• Then finally, I want to take the All Results per Key_ID, and translate that into Type (same lookup table) summarized at a Key_ID level

Expected Result and Method:

• I believe Power Query or VBA is probably the best route to go, but if it can be done in normal excel that would also work. I had envisioned this as almost a conditional column in Power Query with a few tweaks but I have not had any success in that regard. An important point to note is that tables cannot be merged it won't align properly given the data I'm working with. I am not as familiar with VBA vs Excel & Power Query, so I have not explored that option. 

[#2 · Accepted solution]

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,"")))

[#3]

@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.

[#4]

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,"")))

[#5]

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?

[#6]

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."