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

 

[logonwheeler]

Apologies. Noticed an error in my formula. Should be:

=IFERROR(INDEX($A$1:$A$9,MATCH(SMALL(IF(MMULT(--(ISNUMBER(FIND({"A","D"},$A$1:$A$9))),{1;1})=0,COUNTIF($A$1:$A$9,"<"&$A$1:$A$9)),ROWS($1:1)),COUNTIF($A$1:$A$9,"<"&$A$1:$A$9),0)),"")

Regards

This formula works really well. Thanks for providing this solution.

Now - let's add to it. What if I want to find any value in a range, let's say from $C$1:$C$5. I tried to change

...FIND({"A","D"},...

to

...FIND({"A","B","D"},...

(this produced an error)

to

...FIND($C$1:$C$5,...

without any luck.

Any chance we can enhance the functionality of the formula to exclude any elements in a range? To make things even easier, we can name the range $C$1:$C$5 as "EXCLUDELIST" or something similar.

 

[logonwheeler]

This won't work at all when the value it calculates as the Min value is part of the exclude list, as Marcelo Branco pointed out to me in a PM.

Aladin - correct. I tried this on my data set and it produced an error.

 

[XOR LX]

No worries. Just change to:

=IFERROR(INDEX($A$1:$A$9,MATCH(SMALL(IF(MMULT(--(ISNUMBER(FIND(TRANSPOSE($C$1:$C$5),$A$1:$A$9))),ROW($C$1:$C$5)^0)=0,COUNTIF($A$1:$A$9,"<"&$A$1:$A$9)),ROWS($1:1)),COUNTIF($A$1:$A$9,"<"&$A$1:$A$9),0)),"")

Change the ranges in red as required.

Regards

 

[XOR LX]

P.S. You never answered my question about whether we were looking for matches with entire cell contents, e.g. "A", "D", etc., or with locating those letters anywhere within the string, e.g. if the string is "Add" or "O' Driscoll" then it is not to be considered.

Although the solution I gave you should work in the former case as well, in that case it would be unnecessarily complicated, and so a solution such as Aladin's would be far more efficient.

Could you please clarify this (important) point?

Regards

 

[logonwheeler]

P.S. You never answered my question about whether we were looking for matches with entire cell contents, e.g. "A", "D", etc., or with locating those letters anywhere within the string, e.g. if the string is "Add" or "O' Driscoll" then it is not to be considered.

Although the solution I gave you should work in the former case as well, in that case it would be unnecessarily complicated, and so a solution such as Aladin's would be far more efficient.

Could you please clarify this (important) point?

Regards

XOR - Sorry, I should have replied sooner.

I am looking to exclude the entire cell contents, not just any letter or number contained within the cell's contents. IOW I need EXACT match for an exclusion.

Does that clarify?

 

[logonwheeler]

No worries. Just change to:

=IFERROR(INDEX($A$1:$A$9,MATCH(SMALL(IF(MMULT(--(ISNUMBER(FIND(TRANSPOSE($C$1:$C$5),$A$1:$A$9))),ROW($C$1:$C$5)^0)=0,COUNTIF($A$1:$A$9,"<"&$A$1:$A$9)),ROWS($1:1)),COUNTIF($A$1:$A$9,"<"&$A$1:$A$9),0)),"")

Change the ranges in red as required.

Regards

Just tried this solution and it works out well - however only if my list contains alphanumeric or alpha characters only.

Here is an example that does not return the correct result:

=IFERROR(INDEX($A$1:$A$9,MATCH(SMALL(IF(MMULT(--(ISNUMBER(FIND(TRANSPOSE($C$1:$C$4),$A$1:$A$9))),ROW($C$1:$C$4)^0)=0,COUNTIF($A$1:$A$9,"<"&$A$1:$A$9)),ROWS($1:1)),COUNTIF($A$1:$A$9,"<"&$A$1:$A$9),0)),"")

Data Set:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]VALUES[/TD]
[TD]RESULT[/TD]
[TD]EXCLUSION LIST[/TD]
[/TR]
[TR]
[TD]COMP EVAL[/TD]
[TD]COMP EVAL[/TD]
[TD]P5[/TD]
[/TR]
[TR]
[TD]COMP EVAL[/TD]
[TD][/TD]
[TD]COMP EVAL[/TD]
[/TR]
[TR]
[TD]P5[/TD]
[TD][/TD]
[TD]P2[/TD]
[/TR]
[TR]
[TD]P4[/TD]
[TD][/TD]
[TD]P1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P3-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PROTOTYPE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any ideas?

 

[XOR LX]

"XOR - Sorry, I should have replied sooner.

I am looking to exclude the entire cell contents, not just any letter or number contained within the cell's contents. IOW I need EXACT match for an exclusion.

Does that clarify?"

Ah, then it will be a solution along the lines of Aladin's. And I apologise for having misinterpreted your original question.

Regards

 

[Domenic]

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!

 

[Marcelo Branco]

Disregard

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

VALUES​

[/TD]
[TD]

RESULT​

[/TD]
[TD]

EXCLUSION LIST​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

COMP EVAL​

[/TD]
[TD]

1​

[/TD]
[TD]

P5​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

COMP EVAL​

[/TD]
[TD][/TD]
[TD]

COMP EVAL​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

P5​

[/TD]
[TD][/TD]
[TD]

P2​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

P4​

[/TD]
[TD][/TD]
[TD]

P1​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

P3-2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

P2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

P1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

PROTOTYPE​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


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.