LOOKUPVALUE with IF statements

[Anonymous]

Hi All, 

I have two tables;

TABLE 1 contains a list of items, 

In TABLE 2, I want to result a "YES" IF,

Column 'SUB' = "ABC", 

and IF

Column 'ITEM' matches data in TABLE 1, Column 'ITEM'

If the 'SUB' column in TABLE 1 does not match "ABC", I want the cell to remain blank. 

Thanks in advance for your help.

T

TABLE 1

ITEM

a

b

d

e

TABLE 2
sub	item	match
abc	a	yes
abc	b	yes
abc	c	no
abc	d	yes
abc	e	yes
efg	a
efg	b
efg	c
efg	d
efg	e

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Try this column

Column =
SWITCH (
    TRUE (),
    [sub] <> "abc", BLANK (),
    [sub] = "abc"
        && COUNTROWS ( INTERSECT ( { [item] }, VALUES ( Table1[ITEM] ) ) ) > 0, "yes",
    "No"
)