Check cell against multiple others, Output Matches "," Delimited

Question

I have the following Data

SKU Cell (M5) 
SKU Check Range(Table 1, Column J2:J8000) 
Model Range(F2:F8000)

I need to have the following logic converted to the formula for it

IF SKU = SKU Check, Output all models that match from Model Column with ", " delimiters for multiple matches

I hope this makes sense Any help on this would be incredibly appreciated

Scott Craner Scott Craner · Accepted Answer · 2021-01-22T15:30:26

With Office 365 use:

=TEXTJOIN(", ",TRUE,FILTER(F2:F8000,J2:J8000 = M5,""))

Without O365, vba will be needed. There are many UDF that mimic TEXTJOIN in which one can use an Array IF() to return the correct values. Here is one:

VLOOKUP with multiple criteria returning values in one cell

Or this one that does TEXTJOINIFS:

Merge values of column B based on common values on column A

Check cell against multiple others, Output Matches "," Delimited

1 Answers