Match 2 values in columns and reurtn yes/no

[Krivo666]

Hi all,

this is doing my head in.
What I'm trying to do is: if name in A2 is in Sheet1!A:A and value in B1 is in Sheet1!I:I (same row) return Yes, otherwise leave blank.

I tried different approaches using MATCH, VLOOKUP, IF but cannot figure it out.
All my attempts do is return yes if A2 is in Sheet1!A:A

I have attached example spreadsheet of the data

This is layout of data

Category <tbody> </tbody>	Name <tbody> </tbody>
Cat1 <tbody> </tbody>	Martin Kelly <tbody> </tbody>
Cat1 <tbody> </tbody>	Mark Henry <tbody> </tbody>
Cat3 <tbody> </tbody>	Martin Kelly <tbody> </tbody>
Cat2 <tbody> </tbody>	Mark Henry <tbody> </tbody>
Cat2 <tbody> </tbody>	John Black

<tbody>
</tbody>

and this what I want the result to look like.

Name	Cat1	Cat2	Cat3
Martin Kelly	Yes		Yes
Mark Henry	Yes	Yes
John Black		Yes

<tbody>
</tbody>

What I tried: =IF(MATCH(A2,Sheet1!$a$2:$a$4,0)*MATCH($b$1,Sheet1!$b$2:$b$4,0),"yes","")


This is small example of what I'm doing, will have to apply this to bigger sets of data and multiple worksheets, but I just can't figure out right approach to MATCH function in this case.

All your help will be appreciated

 

[Weazel]

maybe something like...

b9=IF(COUNTIFS($B$2:$B$6,$A9,$A$2:$A$6,B$8),"Yes","")

Row\Col	A​	B​	C​	D​
1​	Category	Name
2​	Cat1	Martin Kelly
3​	Cat1	Mark Henry
4​	Cat3	Martin Kelly
5​	Cat2	Mark Henry
6​	Cat2	John Black
7​
8​	Name	Cat1	Cat2	Cat3
9​	Martin Kelly	Yes		Yes
10​	Mark Henry	Yes	Yes
11​	John Black		Yes

<tbody>
</tbody>

 

[rayk8301]

i dont think you need to make it complicated. for example, =a2&b2=a2&i2 if the result is true or false, then just build an If statement to change true to yes and false to blank.

 

[Krivo666]

That worked like charm. thanks a mil

 

[Krivo666]

That would only work if I was comparing row by row I think, not when needing to lookup value anywhere in the column

 

[rayk8301]

it'll work