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!


[TABLE="width: 642"]
<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>[TR]
[TD="class: xl63, width: 87"]Team[/TD]
[TD="class: xl65, width: 87"]Jake[/TD]
[TD="width: 21"][/TD]
[TD="class: xl63, width: 87"]Name[/TD]
[TD="class: xl64, width: 360, colspan: 4, align: center"]Scores[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"]Jake[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"]Ben[/TD]
[TD="class: xl65, align: right"]-5[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"]Tim[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]-2[/TD]
[/TR]
</tbody>[/TABLE]

 

[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