Find Negative Numbers based on criteria

[jhwallace]

This seems like a simple problem, but I'm stuck. Right now I'm on the verge of a giant, if(and(or statement to look at every possible outcome, but I think there has to be an easier way. I'm assuming this is an array formula of some type, but I am not skilled enough to figure it out.

I have a drop down where I can pick a name. I have chosen "Jake." I want a formula in a separate cell that now looks at all of Jake's scores (1, 2, 5, -1) and simply tells me "YES" if there is a negative number in Jake's row of scores, or "NO" if there is not. If I pick a different name from the Team drop-down, the formula updates for the new team/name?

Thank you!

Team	Jake		Name	Scores
			Jake	1	2	5	-1
			Ben	-5	1	3	2
			Tim	3	2	1	-2

<colgroup><col width="87" span="2" style="width: 65pt;"><col width="21" style="width: 16pt;"><col width="87" style="width: 65pt;"><col width="99" style="width: 74pt;"><col width="87" span="3" style="width: 65pt;"></colgroup><tbody>
</tbody>

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Name	Scores
2	Jake	1	2	5	-1		Jake	YES
3	Ben	-5	1	3	2
4	Tim	3	2	1	2
Data

Cell Formulas
Range	Formula
H2	=IF(MIN(INDEX(B2:E4,MATCH(G2,A2:A4,0),0))<0,"YES","NO")

 

[jhwallace]

Perfect! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback