Find Minimum Value in an Array depending on string in adjacent cell

[music_al]

Hi

I need to find the minimum value in an array depending on a certain string being present (even in part) in the adjacent cell. So, in the example below, I'd like to return the MINIMUM value from cells B2:B6 only if any cell in column A contains a "2". In this example rows 2 and rows 6 are valid as they contain a 2.

This example should return 10,000.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grade[/TD]
[TD]Pay[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1,2[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]11,000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]13,000[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2,3[/TD]
[TD]14,000[/TD]
[/TR]
</tbody>[/TABLE]

I need to do the same for the maximum value.

Thank you in advance.

Al

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MIN(IF(ISNUMBER(FIND(","&D2&",",","&$A$2:$A$6&",")),$B$2:$B$6))

=MAX(IF(ISNUMBER(FIND(","&D2&",",","&$A$2:$A$6&",")),$B$2:$B$6))

where D2 = 2.