Finding a MAX and MIN value with criteria within a ALPHA-Numeric cloumnm

[JChaney17]

I am looking for a couple of formulas that will return a MAX and MIN value from a Alpha-numeric column with criteria. This is what I am looking at:

No.</SPAN>	Tag No</SPAN>	Type</SPAN>
1</SPAN>	A001678</SPAN>	P</SPAN>
2</SPAN>	A001679</SPAN>	S</SPAN>
3</SPAN>	A001680</SPAN>	S</SPAN>
4</SPAN>	A001681</SPAN>	P</SPAN>
5</SPAN>	A001682</SPAN>	E</SPAN>
6</SPAN>	A001683</SPAN>	E</SPAN>
7</SPAN>	A001684</SPAN>	S</SPAN>
8</SPAN>	A001685</SPAN>	S</SPAN>
9</SPAN>	A001686</SPAN>	S</SPAN>
10</SPAN>	A001687</SPAN>	S</SPAN>
11</SPAN>	A001688</SPAN>	S</SPAN>
12</SPAN>	A001689</SPAN>	P</SPAN>
13</SPAN>	A001690</SPAN>	P</SPAN>
14</SPAN>	A001691</SPAN>	P</SPAN>
15</SPAN>	A001692</SPAN>	E</SPAN>
16</SPAN>	A001693</SPAN>	E</SPAN>
17</SPAN>	A001694</SPAN>	S</SPAN>
18</SPAN>	A001695</SPAN>	S</SPAN>
19</SPAN>	A001696</SPAN>	S</SPAN>
20</SPAN>	A001697</SPAN>	S</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

I am wanting to look for "S" in the "TYPE" column, and then have MAX and MIN formula's look for the MAX and MIN values in the "Tag No" column. I can get the number to display, but I would like to have the MAX and MIN values displayed as "A00####".

Any help would be appreciated.

Thanks.

 

[Firefly2012]

Hi

One formula option:

Excel 2007
	A	B	C	D	E	F	G	H
1	No.	Tag No	Type			S	P	E
2	1	A001678	P		Max	A001697	A001691	A001693
3	2	A001679	S		Min	A001679	A001678	A001682
4	3	A001680	S
5	4	A001681	P
6	5	A001682	E
7	6	A001683	E
8	7	A001684	S
9	8	A001685	S
10	9	A001686	S
11	10	A001687	S
12	11	A001688	S
13	12	A001689	P
14	13	A001690	P
15	14	A001691	P
16	15	A001692	E
17	16	A001693	E
18	17	A001694	S
19	18	A001695	S
20	19	A001696	S
21	20	A001697	S
Sheet3

Cell Formulas
Range	Formula
F2	{=TEXT(MAX(IF($C$2:$C$21=F$1,MID($B$2:$B$21,2,255)+0)),"\A000000")}
F3	{=TEXT(MIN(IF($C$2:$C$21=F$1,MID($B$2:$B$21,2,255)+0)),"\A000000")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JChaney17]

Thanks...that worked great. Since I am not real familiar with these formula's, can you tell me what the MID and the "2,255" are doing?

 

[Firefly2012]

The MID is used to remove the "A" from the value (leaving just numeric digits):

MID(A1,2,255)

Says take all characters from the second character (255 used because it will always be longer than the actual Tag No so will always capture the remaining characters).

 

[JChaney17]

Thanks again for the help and explanation.