Finding MIN text value from array excluding certain specified values

L

logonwheeler

New Member
Joined
Feb 1, 2014
Messages
17
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? :confused:

Data Set:
1 A
2 D
3 A
4 C
5 B
6 D
7 C
8 B
9 A
 
Sort by date Sort by votes
logonwheeler said:
Markmzz - Thanks for this solution - it also works similarly to the other two solutions previously posted. Thanks for your contribution to this topic.
Click to expand...

You are welcome.

And try this for your new problem:

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

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

Markmzz
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Aladin Akyurek said:
Just plug in the required term A2:A5=D2 in the formula Domenic proposed:
Rich (BB code): 
=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")
Click to expand...

Aladin,

For some reason, I am not able to get this to work with the data set that I presented. Here are a few things that I picked up with your code you provided.

A2:A15 should be A2:A16
B2:B15 should be B2:B16

C2:C5 should be F2:F5 (please confirm)

Here is a copy/paste of the code with these updates:
Rich (BB code): 
=IF(SUM(IF((B2:B16<>"")*(A2:A16=D2)*(ISNA(MATCH(B2:B16,F2:F5,0))),1))>0,
  INDEX(B2:B16,MATCH(MAX(MMULT(IF(B2:B16<>"",
  IF(ISNA(MATCH(B2:B16,F2:F5,0)),(B2:B16<=TRANSPOSE(B2:B16))+0,0),0),
  ROW(B2:B16)^0)),MMULT(IF(B2:B16<>"",IF(ISNA(MATCH(B2:B16,C2:C5,0)),
  (B2:B16<=TRANSPOSE(B2:B16))+0,0),0),ROW(B2:B16)^0),0)),"N/A")

But still not working. Any ideas why?
 
Upvote 0
markmzz said:
You are welcome.

And try this for your new problem:

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

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

Markmzz
Click to expand...

Markmzz,

Your solution is working (mostly). However it is treating "COMP EVAL" later in the alphabetic list than P5, P4, P3, P2, P1, & PROTOTYPE. Shouldn't it be treating it as 1st in the alphabetical list?

Try to remove all exclusions from the list and input 1, 2, 3, 4, & 5 in the specification input box. You will be this behavior demonstrated.

Which has me thinking. Would it be better to have a "VALUES" list that is manually sorted so we do not have to rely on alphabetical order? That would make the formula more robust to handle many use cases. Any ideas / opinions on that?

Thanks.
 
Upvote 0
logonwheeler said:
Aladin,

For some reason, I am not able to get this to work with the data set that I presented. Here are a few things that I picked up with your code you provided.

A2:A15 should be A2:A16
B2:B15 should be B2:B16

C2:C5 should be F2:F5 (please confirm)

Here is a copy/paste of the code with these updates:
Rich (BB code): 
=IF(SUM(IF((B2:B16<>"")*(A2:A16=D2)*(ISNA(MATCH(B2:B16,F2:F5,0))),1))>0,
  INDEX(B2:B16,MATCH(MAX(MMULT(IF(B2:B16<>"",
  IF(ISNA(MATCH(B2:B16,F2:F5,0)),(B2:B16<=TRANSPOSE(B2:B16))+0,0),0),
  ROW(B2:B16)^0)),MMULT(IF(B2:B16<>"",IF(ISNA(MATCH(B2:B16,C2:C5,0)),
  (B2:B16<=TRANSPOSE(B2:B16))+0,0),0),ROW(B2:B16)^0),0)),"N/A")

But still not working. Any ideas why?
Click to expand...

I get P2...
Rich (BB code): 
=IF(SUM(IF((B2:B16<>"")*(A2:A16=D2)*(ISNA(MATCH(B2:B16,F2:F5,0))),1))>0,
 INDEX(B2:B16,MATCH(MAX(MMULT(IF(B2:B16<>"",IF(ISNA(MATCH(B2:B16,F2:F5,0)),
 (B2:B16<=TRANSPOSE(B2:B16))+0,0),0),ROW(B2:B16)^0)),
 MMULT(IF(B2:B16<>"",IF(ISNA(MATCH(B2:B16,F2:F5,0)),
 (B2:B16<=TRANSPOSE(B2:B16))+0,0),0),ROW(B2:B16)^0),0)),"N/A")
 
Upvote 0
logonwheeler said:
Markmzz,

Your solution is working (mostly). However it is treating "COMP EVAL" later in the alphabetic list than P5, P4, P3, P2, P1, & PROTOTYPE. Shouldn't it be treating it as 1st in the alphabetical list?

Try to remove all exclusions from the list and input 1, 2, 3, 4, & 5 in the specification input box. You will be this behavior demonstrated.
Click to expand...

Here all is ok. Look at this:

Layout

[TABLE="width: 290"]
<tbody>[TR]
[TD="width: 82, bgcolor: transparent"]SPECIFICATION #[/TD]
[TD="width: 59, bgcolor: transparent"]VALUES[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]SPECIFICATION #[/TD]
[TD="width: 67, bgcolor: transparent"]RESULT[/TD]
[TD="width: 78, bgcolor: transparent"]EXCLUSION LIST[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]COMP EVAL[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: yellow"]COMP EVAL[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]P5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: yellow"]P3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]P2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: yellow"]P1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]P4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: yellow"]P1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]P4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: yellow"]COMP EVAL[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]P3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]PROTOTYPE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]P5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]P1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]P3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]P2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]P1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]COMP EVAL[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]P2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]P1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]***************[/TD]
[TD="bgcolor: transparent"]**********[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]***************[/TD]
[TD="bgcolor: transparent"]************[/TD]
[TD="bgcolor: transparent"]**************[/TD]
[/TR]
</tbody>[/TABLE]


Formula

Code: 
In E2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

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

And copy down until E6.

Markmzz
 
Upvote 0
Maybe...
(assumes all values are alphanumeric or numeric inserted as text)

=IF(SUM(IF(ISNUMBER(MATCH($A$2:$A$16,$D$2:$D$16,0)),IF(ISNA(MATCH($B$2:$B$16,$F$2:$F$16,0)),IF($B$2:$B$16<>"",1))))>0,INDEX($B$2:$B$16,MATCH(MIN(IF(ISNUMBER(MATCH($A$2:$A$16,$D$2:$D$16,0)),IF(B2:B16<>"",IF(ISNA(MATCH($B$2:$B$16,$F$2:$F$16,0)),COUNTIF($B$2:$B$16,"<"&$B$2:$B$16))))),COUNTIF($B$2:$B$16,"<"&$B$2:$B$16),0)),"N/A")

Ctrl+Shift+Enter

M.
 
Upvote 0
It might be easier to use SQL for this - SQL is made for working with datasets.

This gives the full dataset - the minimum for each 'specification #' value. I've used defined names "tblMain" and "tblExclusions" for the source data.

Code: 
SELECT A.[SPECIFICATION #], MIN(A.VALUES) AS [VALUES]
FROM (SELECT M.[SPECIFICATION #], M.VALUES, E.[EXCLUSION LIST]
FROM {oj tblMain M LEFT OUTER JOIN tblExclusions E ON M.VALUES = E.[EXCLUSION LIST]}
WHERE E.[EXCLUSION LIST] Is Null) A
GROUP BY A.[SPECIFICATION #]
 
Upvote 0
logonwheeler said:
Markmzz,

Which has me thinking. Would it be better to have a "VALUES" list that is manually sorted so we do not have to rely on alphabetical order? That would make the formula more robust to handle many use cases. Any ideas / opinions on that?

Thanks.
Click to expand...

In this case, do some tests with this:

Layout

[TABLE="width: 290"]
<colgroup><col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <tbody>[TR]
[TD="width: 82, bgcolor: transparent"]SPECIFICATION #[/TD]
[TD="width: 59, bgcolor: transparent"]VALUES[/TD]
[TD="width: 17, bgcolor: transparent"] [/TD]
[TD="width: 82, bgcolor: transparent"]SPECIFICATION #[/TD]
[TD="width: 67, bgcolor: transparent"]RESULT[/TD]
[TD="width: 78, bgcolor: transparent"]EXCLUSION LIST[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]COMP EVAL[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: yellow"]P2[/TD]
[TD="bgcolor: transparent"]P5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]COMP EVAL[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: yellow"]P4[/TD]
[TD="bgcolor: transparent"]COMP EVAL[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]P1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: yellow"]PROTOTYPE[/TD]
[TD="bgcolor: transparent"]P1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]P1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: yellow"]P2[/TD]
[TD="bgcolor: transparent"]P3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]P1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: yellow"]P2[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]P2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]P2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]P2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]P3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]P3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]P4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]P5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]P5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]P5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]PROTOTYPE[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]***************[/TD]
[TD="bgcolor: transparent"]**********[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]***************[/TD]
[TD="bgcolor: transparent"]************[/TD]
[TD="bgcolor: transparent"]**************[/TD]
[/TR]
</tbody>[/TABLE]


Formula

Code: 
In E2

=IFERROR(INDEX($B$2:$B$16,MATCH(1,INDEX(($B$2:$B$16<>"")*($A$2:$A$16=$D2)*ISNA(MATCH($B$2:$B$16,$F$2:$F$5,0)),),0)),"")

And copy down.

Markmzz
 
Upvote 0
Aladin Akyurek said:
I get P2...
Rich (BB code): 
=IF(SUM(IF((B2:B16<>"")*(A2:A16=D2)*(ISNA(MATCH(B2:B16,F2:F5,0))),1))>0,
 INDEX(B2:B16,MATCH(MAX(MMULT(IF(B2:B16<>"",IF(ISNA(MATCH(B2:B16,F2:F5,0)),
 (B2:B16<=TRANSPOSE(B2:B16))+0,0),0),ROW(B2:B16)^0)),
 MMULT(IF(B2:B16<>"",IF(ISNA(MATCH(B2:B16,F2:F5,0)),
 (B2:B16<=TRANSPOSE(B2:B16))+0,0),0),ROW(B2:B16)^0),0)),"N/A")
Click to expand...

Aladin - If I change the SPECIFICATION # from 1 to 2 to 3 and so on, the result does not calculate properly. Can you confirm?

For example, if I change the SPECIFICATION # to 3, the result is P2 which is not even a possibility.
 
Upvote 0
Marcelo Branco said:
Maybe...
(assumes all values are alphanumeric or numeric inserted as text)

=IF(SUM(IF(ISNUMBER(MATCH($A$2:$A$16,$D$2:$D$16,0)),IF(ISNA(MATCH($B$2:$B$16,$F$2:$F$16,0)),IF($B$2:$B$16<>"",1))))>0,INDEX($B$2:$B$16,MATCH(MIN(IF(ISNUMBER(MATCH($A$2:$A$16,$D$2:$D$16,0)),IF(B2:B16<>"",IF(ISNA(MATCH($B$2:$B$16,$F$2:$F$16,0)),COUNTIF($B$2:$B$16,"<"&$B$2:$B$16))))),COUNTIF($B$2:$B$16,"<"&$B$2:$B$16),0)),"N/A")

Ctrl+Shift+Enter

M.
Click to expand...

Marcelo - Working quite well. The only issue I found so far is that this formula is treating the result "COMP EVAL" later than all the P5, P4, P3 etc values. Do you know why this is?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Spill array that only shows rows that have value in certain cells
Replies
1
Views
78
Alex Blakenburg
A
A
  • Solved
Index Match With Multiple Criteria
Replies
3
Views
170
acool
A
K
  • Solved
help with formatting a cell (for TIME, custom format) that retrieves its value from another cell...
Replies
2
Views
342
kbishop94
K
W
  • Solved
Using MIN with IF resulting in #VALUE error
2
Replies
15
Views
992
myall_blues
myall_blues
E
  • Solved
Find lowest LEN difference & return contents of cell to left
Replies
5
Views
94
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,223,099
Messages
6,170,109
Members
452,302
Latest member
TaMere

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top