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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl67, width: 128"]Category[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl67, width: 99"]Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]Cat1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl66, width: 99"]Martin Kelly[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]Cat1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl66, width: 99"]Mark Henry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]Cat3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl66, width: 99"]Martin Kelly[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]Cat2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl66, width: 99"]Mark Henry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]Cat2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]John Black

[/TD]
[/TR]
</tbody>[/TABLE]













and this what I want the result to look like.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Cat1[/TD]
[TD]Cat2[/TD]
[TD]Cat3[/TD]
[/TR]
[TR]
[TD]Martin Kelly[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Mark Henry[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Black[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








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","")

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Category[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Cat1[/TD]
[TD]Martin Kelly[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Cat1[/TD]
[TD]Mark Henry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Cat3[/TD]
[TD]Martin Kelly[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Cat2[/TD]
[TD]Mark Henry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Cat2[/TD]
[TD]John Black[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Name[/TD]
[TD]Cat1[/TD]
[TD]Cat2[/TD]
[TD]Cat3[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Martin Kelly[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Mark Henry[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]John Black[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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