Finding MIN text value from array excluding certain specified values

[logonwheeler]

I am looking to return the MIN alphabetical text value from a list, with certain values excluded.

I am currently using an array formula like this one to return the MIN alphabetical value in array A1:A9:

=INDEX($A$1:$A$9,MATCH(0,COUNTIF($A$1:$A$9,">"&$A$1:$A$9),)) (+ CSE)
Returns the value A

However, I would like to return the MIN alphabetical value in the array EXCLUDING all A & D values. This result should return the value B.

Can anyone suggest a nice way to achieve this?

Data Set:
1 A
2 D
3 A
4 C
5 B
6 D
7 C
8 B
9 A

 

[Domenic]

Actually, the formula I offered should also return the desired result in circumstances such as the following...

Values	Result	Exclusion List
P3-2	1	P5
2		COMP EVAL
P5		P2
P4		P1
2
P3-2
1
P4
PROTOTYPE

<TBODY>
</TBODY>

 

[markmzz]

Try this:

Use Ctrl+Shift+Enter and not just Enter to enter the formula

=INDEX($A$2:$A$10,MATCH(
MIN(MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0)*ISNA(MATCH($A$2:$A$10,$C$2:$C$5,0))),
MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0),0))

Markmzz

 

[Domenic]

Try this:

Use Ctrl+Shift+Enter and not just Enter to enter the formula

=INDEX($A$2:$A$10,MATCH(
MIN(MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0)*ISNA(MATCH($A$2:$A$10,$C$2:$C$5,0))),
MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0),0))

Markmzz

It doesn't look like it returns the desired result if the range contains an empty cell, or if the range contains only values from the exclusion list.

 

[markmzz]

It doesn't look like it returns the desired result if the range contains an empty cell, or if the range contains only values from the exclusion list.

Domenic,

You can try this:

Use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(COUNT(MATCH($A$2:$A$10,$C$2:$C$5,0))=SUM(--($A$2:$A$10<>"")),"",INDEX($A$2:$A$10,MATCH(
MIN(MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0)*($A$2:$A$10<>"")*ISNA(MATCH($A$2:$A$10,$C$2:$C$5,0))),
MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0),0)))

Anyway, let's wait for the user.

Markmzz

 

[logonwheeler]

Assuming that A2:A10 contains the data, and C2:C5 contains the exclusion list, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=IF(SUM(IF((A2:A10<>"")*(ISNA(MATCH(A2:A10,C2:C5,0))),1))>0,INDEX(A2:A10,MATCH(MAX(MMULT(IF(A2:A10<>"",IF(ISNA(MATCH(A2:A10,C2:C5,0)),(A2:A10<=TRANSPOSE(A2:A10))+0,0),0),ROW(A2:A10)^0)),MMULT(IF(A2:A10<>"",IF(ISNA(MATCH(A2:A10,C2:C5,0)),(A2:A10<=TRANSPOSE(A2:A10))+0,0),0),ROW(A2:A10)^0),0)),"N/A")

Hope this helps!

I can confirm that this solution works. Thanks Domenic for your contribution.

 

[logonwheeler]

Maybe...

	A​	B​	C​
1​	VALUES​	RESULT​	EXCLUSION LIST​
2​	COMP EVAL​	1​	P5​
3​	COMP EVAL​		COMP EVAL​
4​	P5​		P2​
5​	P4​		P1​
6​	1​
7​	P3-2​
8​	P2​
9​	P1​
10​	PROTOTYPE​

<tbody>
</tbody>

B2
=INDEX($A$2:$A$10,MATCH(MIN(IF(ISNA(MATCH($A$2:$A$10,$C$2:$C$5,0)),COUNTIF($A$2:$A$10,"<"&$A$2:$A$10&" "))),COUNTIF($A$2:$A$10,"<"&$A$2:$A$10&" "),0))

Ctrl+Shift+Enter

M.

Marcelo - I can confirm that this solution works as well as Domenic's solution. Thanks also for your contribution.

 

[logonwheeler]

Try this:

Use Ctrl+Shift+Enter and not just Enter to enter the formula

=INDEX($A$2:$A$10,MATCH(
MIN(MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0)*ISNA(MATCH($A$2:$A$10,$C$2:$C$5,0))),
MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0),0))

Markmzz

Marknmzz - thanks for offering this solution. As others have mentioned, if any of the "VALUES" cells are empty or blank, this solution will return a result of "0" instead of returning the min from the remainder of the data set.

 

[logonwheeler]

Domenic,

You can try this:

Use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(COUNT(MATCH($A$2:$A$10,$C$2:$C$5,0))=SUM(--($A$2:$A$10<>"")),"",INDEX($A$2:$A$10,MATCH(
MIN(MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0)*($A$2:$A$10<>"")*ISNA(MATCH($A$2:$A$10,$C$2:$C$5,0))),
MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0),0)))

Anyway, let's wait for the user.

Markmzz

Markmzz - Thanks for this solution - it also works similarly to the other two solutions previously posted. Thanks for your contribution to this topic.

 

[logonwheeler]

Thanks to everyone who has contributed to this topic thus far. Many creative solutions that are working quite well. You should pat yourselves on the back.

I have what I believe one final use case & solution request.

I would like to take this solution one step further. I would like to do the same find MIN value of alphanumeric value from a data set as before, but this time reference a "SPECIFICATION #" value in which to lookup prior to returning a value.

In other words - the user inputs a value for a SPECIFICATION # in cell D2, and the calculated value for E2 is the MIN alphanumeric value for all SPECIFICATION #s matching the value in D2. In the use case below, the user inputs "4" for SPECIFICATION #. The calculated value for E2 is P1 because for all SPECIFICATION # values equal to "4" (P3, P2, P1) the MIN value of those returned is P2 because both P3 and P1 are on the exclusion list.

Data Set:

	A	B	C	D	E	F
1	SPECIFICATION #	VALUES		SPECIFICATION #	RESULT	EXCLUSION LIST
2	1	COMP EVAL		4	P2	P5
3	1	P5				COMP EVAL
4	1	P2				P1
5	2	P4				P3
6	2	P4
7	2	P3
8	3	PROTOTYPE
9	3	P5
10	3	P1
11	4	P3
12	4	P2
13	4	P1
14	5	COMP EVAL
15	5	P2
16	5	P1

<tbody>
</tbody>

Who is up for this challenge? Thanks in advance for your time spent looking into this challenge.

 

[Aladin Akyurek]

Thanks to everyone who has contributed to this topic thus far. Many creative solutions that are working quite well. You should pat yourselves on the back.

I have what I believe one final use case & solution request.

I would like to take this solution one step further. I would like to do the same find MIN value of alphanumeric value from a data set as before, but this time reference a "SPECIFICATION #" value in which to lookup prior to returning a value.

In other words - the user inputs a value for a SPECIFICATION # in cell D2, and the calculated value for E2 is the MIN alphanumeric value for all SPECIFICATION #s matching the value in D2. In the use case below, the user inputs "4" for SPECIFICATION #. The calculated value for E2 is P1 because for all SPECIFICATION # values equal to "4" (P3, P2, P1) the MIN value of those returned is P2 because both P3 and P1 are on the exclusion list.

Data Set:

	A	B	C	D	E	F
1	SPECIFICATION #	VALUES		SPECIFICATION #	RESULT	EXCLUSION LIST
2	1	COMP EVAL		4	P2	P5
3	1	P5				COMP EVAL
4	1	P2				P1
5	2	P4				P3
6	2	P4
7	2	P3
8	3	PROTOTYPE
9	3	P5
10	3	P1
11	4	P3
12	4	P2
13	4	P1
14	5	COMP EVAL
15	5	P2
16	5	P1

<tbody>
</tbody>

Who is up for this challenge? Thanks in advance for your time spent looking into this challenge.

Just plug in the required term A2:A5=D2 in the formula Domenic proposed:

=IF(SUM(IF((B2:B15<>"")*(A2:A15=D2)*(ISNA(MATCH(B2:B15,F2:F5,0))),1))>0,
  INDEX(B2:B15,MATCH(MAX(MMULT(IF(B2:B15<>"",
  IF(ISNA(MATCH(B2:B15,F2:F5,0)),(B2:B15<=TRANSPOSE(B2:B15))+0,0),0),
  ROW(B2:B15)^0)),MMULT(IF(B2:B15<>"",IF(ISNA(MATCH(B2:B15,C2:C5,0)),
  (B2:B15<=TRANSPOSE(B2:B15))+0,0),0),ROW(B2:B15)^0),0)),"N/A")