Formula Help: If then (Match)

[rentonhighlands]

Members:
Please help me write a formula

Sample Data
A B

1.1.1.1	1_V
1.1.1.1	2_V
1.1.1.2	1_V
1.1.1.3	1_V

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

If column A finds a match on IP address and column B has both 1_V & 2_V then say YES in column C otherwise NO
I want to find in column A if there is the same IP address and it has both 1_V and 2_V I want Yes

 

[Fluff]

How about

Excel 2013/2016
	E	F	G
2	1.1.1.1	1_V	YES
3	1.1.1.1	2_V	YES
4	1.1.1.2	1_V	NO
5	1.1.1.3	1_V	NO
Sheet1

Cell Formulas
Range	Formula
G2	=IF(SUMPRODUCT(($E$2:$E$6=E2)*(($F$2:$F$6="1_V")+($F$2:$F$6="2_V")))=2,"YES","NO")

 

[rentonhighlands]

Thank you how ever I can not get this to work in my data
I think it has something to do with (($E$2:$E$6=E2) "=E2" part

I want the formula to look down column E and each time it finds a match (the same IP address) then column F will have 1_V and 2_V then yes if not no

 

[Fluff]

Discovered a problem with the previous formula.
try

Excel 2013/2016
	E	F	G
2	1.1.1.1	1_V	YES
3	1.1.1.1	2_V	YES
4	1.1.1.2	1_V	YES
5	1.1.1.3	1_V	NO
6	1.1.1.2	2_V	YES
7	1.1.1.3	1_V	NO
Sheet1

Cell Formulas
Range	Formula
G2	=IF(AND(COUNTIFS($E$2:$E$7,E2,$F$2:$F$7,"1_V"), COUNTIFS($E$2:$E$7,E2,$F$2:$F$7,"2_V")),"YES","NO")

 

[rentonhighlands]

awesome work yep this formula works thanks

 

[rentonhighlands]

can I add ISBLANK so if column "E" is blank then keep column "G" blank
so if there is no data in column "E" then the result will be blank. The cell is blank

 

[Fluff]

Try
=IF(E2="","",IF(AND(COUNTIFS($E$2:$E$7,E2,$F$2:$F$7,"1_V"), COUNTIFS($E$2:$E$7,E2,$F$2:$F$7,"2_V")),"YES","NO"))

 

[rentonhighlands]

Fluff:
how can I change the formula to only look at columns T and W. The report changed.. Now I need the formula to look at column T and when it finds the same IP address then yes.

 

[Fluff]

Change all the E to T & F to W

 

[rentonhighlands]

thanks that works