lookup value from table with multiple values

[vmakhija]

Hello

I have 2 tables as below -

Table A

ID ProductCat
123 Appliances
131 Laptops
130 Mobiles
129 Appliances
128 Laptops
127 Mobiles
126 Appliances
125 Laptops
124 Mobiles

Table B

Category ID
Mobiles 123
Appliances 124
Laptops 124
Appliances 125
Laptops 125
Appliances 126
Mobiles 126
Laptops 127
Appliances 128
Mobiles 127
Laptops 128
Appliances 129
Mobiles 129
Laptops 130
Mobiles 130
Appliances 131
Laptops 131

I want to do the following - 

Create a flag column in Table A which will set value to Yes if a particular category in Table A for an ID matches with any one of categories in Table B for that ID.

Lookup won't work here as it will give error - "A table of multiple values...."

I tried this 

CATEGORY_FLAG =
IF(SEARCH(TableA[ProductCat],CONCATENATEX(DISTINCT(TableB[Category]),TableB[Category]),1,-1)>0,
"Yes","No")

But somehow I want to filter the result of concatenatex to be just for a particular ID. However, above column will always take all values of category.

Any thoughts?

Regards

[Zubair_Muhammad · Accepted solution]

@vmakhija

Try this pattern

=
IF (
    COUNTROWS (
        FILTER ( TableB, TableA[ID] = TableB[ID] && TableA[ProdCat] = TableB[ProdCat] )
    )
        > 0,
    "yes",
    "no"
)