Reply
Member
Posts: 124
Registered: ‎01-21-2018
Accepted Solution

lookup value from table with multiple values

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


Accepted Solutions
Highlighted
Super User
Posts: 2,669
Registered: ‎09-27-2017

Re: lookup value from table with multiple values

@vmakhija

 

Try this pattern

 

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

View solution in original post


All Replies
Highlighted
Super User
Posts: 2,669
Registered: ‎09-27-2017

Re: lookup value from table with multiple values

@vmakhija

 

Try this pattern

 

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