Check for match comma separated values in multiple cells

Howard_Roark

New Member
Joined
Apr 8, 2023
Messages
5
Office Version
  1. 2021
Platform
  1. MacOS
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

Screenshot 2023-04-08 at 12.25.55.png
 
Glad we could help & thanks for the feedback.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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:
Excel Formula:
=Woof(<cells to the left>,<table or range to the right absolute references>)

VBA Code:
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
ABCDEFGH
1Product NameAppropriate Breed SizesAppropriate for White ColorAppropriate for these BreedsBreedWhite ColorBreed Size
2Product AS, XS, M, LYesAffenpinscher, Africanis, Airedale Terrier, Akita, Alano EspanolAffenpinscherYesL, XL
3Product BM, L, XLNoAfghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood BulldogAfghan HoundNoL, XL
4Product CS, M, LYesAffenpinscher, Africanis, Airedale Terrier, Akita, Alano EspanolAfricanisYesL, XL
5Product DL, XLNoAfghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood BulldogAidiNoL, XL
6Product EL, M, XLYesAffenpinscher, Africanis, Airedale Terrier, AkitaAiredale TerrierYesL, XL
7Product FM, L, XLNoAfghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood BulldogAkbashNoL, XL
8Product GM, L, XLYesAffenpinscher, Africanis, Airedale Terrier, AkitaAkitaYesM
9Product HL, M, XLNoAfghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood BulldogAksaray MalaklisiNoXL
10Product IM, L, XLYesAffenpinscher, Africanis, Airedale Terrier, AkitaAlano EspanolYesS
11Product JM, LNoAfghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood BulldogAlapaha Blue Blood BulldogNoXL
12
13
14Product NameAppropriate Breed SizesAppropriate for White ColorAppropriate for these BreedsBreedWhite ColorBreed Size
15Product AS, XS, M, LYesAffenpinscher, Africanis, Airedale Terrier, Akita, Alano EspanolAffenpinscherYesL, XL
16Product BM, L, XLNoAfghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood BulldogAfghan HoundNoL, XL
17Product CS, M, LYesAffenpinscher, Africanis, Airedale Terrier, Akita, Alano EspanolAfricanisYesL, XL
18Product DL, XLNoAfghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood BulldogAidiNoL, XL
19Product EL, M, XLYesAffenpinscher, Africanis, Airedale Terrier, AkitaAiredale TerrierYesL, XL
20Product FM, L, XLNoAfghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood BulldogAkbashNoL, XL
21Product GM, L, XLYesAffenpinscher, Africanis, Airedale Terrier, AkitaAkitaYesM
22Product HL, M, XLNoAfghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood BulldogAksaray MalaklisiNoXL
23Product IM, L, XLYesAffenpinscher, Africanis, Airedale Terrier, AkitaAlano EspanolYesS
24Product JM, LNoAfghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood BulldogAlapaha Blue Blood BulldogNoXL
Sheet1
Cell Formulas
RangeFormula
D2:D11D2=Woof(Table1[@[Product Name]:[Appropriate for White Color]],Table2)
D15:D24D15=Woof(A15:C15,$F$2:$H$11)

Also works great. Thank you
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top