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.

	A	B
1	Grade	Pay
2	1,2	10,000
3	2	11,000
4	2	12,000
5	2	13,000
6	2,3	14,000

<tbody>
</tbody>

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.