How to use an index to find all values ​​that exceed

I am a little versed in my situation, both on this site and on others, and this is the closest to my problem / solution:

Find all values ​​greater than or equal to some value

However, using this solution in my situation does not give me the correct results. I have a list of 83 names with sanctions that are given to each name. On a separate tab, I want to display the output of all names that have any penalty (> 0).
I have only four possible penalties, so if I need to refer to them in a formula (match or search), that would be fine too. Data reduction and layout, here is an example of what I have:

+----------+---------+
|  Name    | Penalty |
+----------+---------+
| Name 1   |    0    |
| Name 2   |    0    |
| Name 3   |    5    |
| Name 4   |    0    |
| Name 5   |    0    |
| Name 6   |    10   |
| Name 7   |    0    |
| Name 8   |    0    |
| Name 9   |    0    |
| Name 10  |    20   |
+----------+---------+

Using this formula, then CSEdrag down:

=INDEX($R$4:$R$13,SMALL(IF($S$4:$S$13>0,ROW($S$4:$S$13)),ROW(1:1)))

:

+---------+
| Name 6  |
| Name 9  |
| #REF!   |
| #NUM!   |
| #NUM!   |
| #NUM!   |
| #NUM!   |
| #NUM!   |
| #NUM!   |
| #NUM!   |
+---------+

, IFERROR , , > 0

edit: "ROW" , , , , . "k" "SMALL".

. !

+4
1

MATCH() SMALL():

=INDEX($R$4:$R$13,MATCH(1,($S$4:$S$13>0)*(COUNTIF($U$3:U3,$R$4:$R$13)=0),0))

, Ctrl-Shift-Enter.

, , , , , .

enter image description here


SMALL(), :

=INDEX($R$4:$R$13,SMALL(IF($S$4:$S$13>0,ROW($S$4:$S$13)-ROW($S$4)+1),ROW(1:1)))

@dirk , - SMALL(), INDEX, INDEX SMALL , :

=INDEX($R:$R$,SMALL(IF($S$4:$S$13>0,ROW($S$4:$S$13)),ROW(1:1)))

Ctrl-Shift-Enter.

enter image description here

- AGGREGATE, CSE :

=INDEX($R:$R,AGGREGATE(15,6,ROW($R$4:$R$13)/($S$4:$S$13>0),ROW(1:1))

. - , .

, , , COUNTIF() .

+4

Source: https://habr.com/ru/post/1655338/


All Articles