Lookup Data in Multiple Columns against lookup list?

[FuriousD]

On Sheet 1 I have names of people in cells B2 through O2.

On Sheet 2 I have a lookup list of names.

On Sheet 1 cell P2 I'd like to do an If/index/match formula that looks ad B2-O2 to see if any of the names in my lookup list are in any of those cells. If they are it will return "Yes". Even if only one of the names in B2-O2 are present.

Example

Sheet 1

J	K	L	M	N	O	P
Mark Mulder	Magic Johnson	Kirk Gibson	Steve Smith	Andre Rison	Zach Randolph	No
Charlie Bell	Mateen Cleaves	Andre Hutson	Antonio Smith	Morris Peterson	Jason Richardson	Yes

<tbody>
</tbody>

Sheet 2 - Lookup List

A

Morris Peterson

Matt Ishbia

Steve Cherry

<tbody>
</tbody>

So in the example above because Morris Peterson is in the lookup list on Sheet 2 cell P2 returns "Yes" because his name is in cell N2 on sheet 1.

TIA

 

[Aladin Akyurek]

In P2 of Sheet1 enter:

=IF(SUMPRODUCT(COUNTIFS(Sheet2!A:A,$B$:$O2)),"Yes","No")

 

[FuriousD]

Thanks. I got an error so I changed one part (assuming you meant , $B2 not $B$ highlighted in red below) but now I get "Yes" for every result whether or not any of the lookup list names are in B2-O2.

Thoughts? Did I mess it up by changing?

In P2 of Sheet1 enter:

=IF(SUMPRODUCT(COUNTIFS(Sheet2!A:A,$B$:$O2)),"Yes","No")

 

[Aladin Akyurek]

Thanks. I got an error so I changed one part (assuming you meant , $B2 not $B$ highlighted in red below) but now I get "Yes" for every result whether or not any of the lookup list names are in B2-O2.

Thoughts? Did I mess it up by changing?

It's obviously...

=IF(SUMPRODUCT(COUNTIFS(Sheet2!A:A,$B2:$O2)),"Yes","No")

See the workbook which implements the set up: https://www.dropbox.com/s/mc0bndph820xvac/FuriousD%20countifs%20as%20look%20up.xlsx?dl=0