Check for match comma separated values in multiple cells

[Howard_Roark]

Hi,
I have always been able to figure out how to solve an excel problem by looking at forums but this one is impossible for me to figure out, so please help.

I have an excel sheet where I need to show which dog food is appropriate for which specific dog breed.
I have a table for pet breeds and another table with the pet food.
I need the column D, that is for each product, to be populated with all the breeds in one line (one cell), separated by columns.

The match is supposed to be based on size (XS, S, M, L, XL) and white color (yes or no).
Therefore, if any of the values separated by commas in cell B1 is present in a row of column H and C1 matches with column G at the same row, return from that row the value in column F. Run for each row for the D column in the product sheet.

Attached is an example of the Sheet.

I can also reconstruct the sheet if necessary.

Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Howard_Roark]

T

See if this comes close to what you're looking for. Put the Function in a standard module. Note the use of it in both a table & range below the code. Basically, it works like this:

=Woof(<cells to the left>,<table or range to the right absolute references>)

Function Woof(a, b As Range) As String
    Application.Volatile
    Dim i As Long, j As Long, Prods, Dogs As String
    Prods = Split(CStr(a(2)), ", ")
    Dim c: c = b
    For i = LBound(c, 1) To UBound(c, 1)
        For j = LBound(Prods) To UBound(Prods)
            If InStr(CStr(c(i, 3)), CStr(Prods(j))) And c(i, 2) = a(3) Then
                If Dogs = "" Then
                    Dogs = c(i, 1)
                Else
                    Dogs = Dogs & ", " & c(i, 1)
                End If
                Exit For
            End If
        Next j
    Next i
    Woof = Dogs
End Function

Woof.xlsm
	A	B	C	D	E	F	G	H
1	Product Name	Appropriate Breed Sizes	Appropriate for White Color	Appropriate for these Breeds		Breed	White Color	Breed Size
2	Product A	S, XS, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Affenpinscher	Yes	L, XL
3	Product B	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Afghan Hound	No	L, XL
4	Product C	S, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Africanis	Yes	L, XL
5	Product D	L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aidi	No	L, XL
6	Product E	L, M, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Airedale Terrier	Yes	L, XL
7	Product F	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Akbash	No	L, XL
8	Product G	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Akita	Yes	M
9	Product H	L, M, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aksaray Malaklisi	No	XL
10	Product I	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Alano Espanol	Yes	S
11	Product J	M, L	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Alapaha Blue Blood Bulldog	No	XL
12
13
14	Product Name	Appropriate Breed Sizes	Appropriate for White Color	Appropriate for these Breeds		Breed	White Color	Breed Size
15	Product A	S, XS, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Affenpinscher	Yes	L, XL
16	Product B	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Afghan Hound	No	L, XL
17	Product C	S, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Africanis	Yes	L, XL
18	Product D	L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aidi	No	L, XL
19	Product E	L, M, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Airedale Terrier	Yes	L, XL
20	Product F	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Akbash	No	L, XL
21	Product G	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Akita	Yes	M
22	Product H	L, M, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aksaray Malaklisi	No	XL
23	Product I	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Alano Espanol	Yes	S
24	Product J	M, L	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Alapaha Blue Blood Bulldog	No	XL
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=Woof(Table1[@[Product Name]:[Appropriate for White Color]],Table2)
D15:D24	D15	=Woof(A15:C15,$F$2:$H$11)

Also works great. Thank you