Min and If Statements Combined with VLookup?

[JohnSlider]

Hi,

VLookup is probably not the right starting point, but it's the best way I can describe what I'm trying to do.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Y/N[/TD]
[TD]Sales[/TD]
[TD]Formula 1[/TD]
[/TR]
[TR]
[TD]Best Buy[/TD]
[TD]No[/TD]
[TD]4[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]Yes[/TD]
[TD]5[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]No[/TD]
[TD]3[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Best Buy[/TD]
[TD]No[/TD]
[TD]9[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

Formula 1: Search for any "Yes" entries in column B, if at least 1 is present for the store listed in column A, then it enters "Yes" in column D on every row for that store.




[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Y/N[/TD]
[TD]Sales[/TD]
[TD]Formula 2[/TD]
[/TR]
[TR]
[TD]Best Buy[/TD]
[TD]No[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]Yes[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]No[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Best Buy[/TD]
[TD]No[/TD]
[TD]9[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

Formula 2: Search for the MIN entry in column C based on the store entered in column A, and return that MIN entry on every line for that store in column D.


What do you guys think? Doable?

 

[Aladin Akyurek]

In D2 enter and copy down:

=IF(AND(A2="best buy",B2="No"),"No",B2)

In E2 enter and copy down:

=MINIFS($C$2:$C$5,$A$2:$A$5,A2)

If MINIFS gives you the #NAME? error, control+shift+enter, not just enter, and copy down:

=MIN(IF($$A$2:$A$5=A2,C$2:$C$5))

Or just enter and copy down:

=AGGREGATE(15,6,$C$2:$C$5/($A$2:$A$5=A2),1)

 

[JohnSlider]

Excellent! Thank you!

 

[Aladin Akyurek]

Excellent! Thank you!

You are welcome.