Excel: how to skip a specific row in an array

I’m trying to skip a specific row in an array, not the value.

For instance, assume we’re on the A column:

0
3
0 <--- Row 3
0

how can I skip the 3rd row in A1:A4, so small(A1:A4, 2) can equal to 0, and small(A1:A4, 3) would equal to 3.

I would like to skip a specific row, not the value. I cannot switch the rows, I require a formula that ignores a specific row in an array

Thank you for the assistance!

Another approach is with the INDEX function creating an array of the rows you wish to retain (and there are many ways to create this array). In your specific example, using AGGREGATE instead of SMALL:

=AGGREGATE(15,4,INDEX($A$1:$A$4,IF(1,N({1,2,4}))),2)

If you have just four rows and want to ignore just one, the approach doesn’t really matter. If your ranges are larger, and your selection of rows to ignore more complex (and more than just one), creating an array of row numbers to retain may be simpler.

If you are going to sum the numbers use SUMPRODUCT:

=SUMPRODUCT((ROW(A1:A4)<>3)*(A1:A4))

If you are going to do anything else then most likely you will need an array entered formula with and IF inside:

IF(ROW(A1:A4)<>3,A1:A4)

This will make the formula an array formula that needs to confirmed with Ctrl-Shift-enter instead of Enter when exiting edit mode.

So for example if we wanted the MAX:

=MAX(IF(ROW(A1:A4)<>3,A1:A4))

Entered with Ctrl-Shift-Enter.

For the Small:

=SMALL(IF(ROW(A1:A4)<>3,A1:A4),3)

Entered with Ctrl-Shift-Enter would return 3 not 0

Another form of AGGREGATE that is the same as the small above:

=AGGREGATE(15,7,A1:A4/(ROW(A1:A4)<>3),3)

To skip the specified 3rd row in A1:A4

Try to enter this normal formula :

=SMALL(IF({1;1;0;1},A1:A4),3)

will return result 3

and,

=SMALL(IF({1;1;0;1},A1:A4),2)

will return result 0