Author: Oscar Cronquist Article last updated on February 20, 2019

Extract negative values and adjacent cells (array formula)
Extract negative values and adjacent cells (Excel Filter)

Array formula in B23:

=INDEX(\$B\$3:\$D\$18, SMALL(IF(\$D\$3:\$D\$18<0, ROW(\$D\$3:\$D\$18)-MIN(ROW(\$D\$3:\$D\$18))+1, ""), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER

copied down as far as needed and then copied to the right as far as needed.

### Explaining array formula in cell B23

Step 1 - Filter negative values and return their corresponding row number

IF(\$D\$3:\$D\$18<0, ROW(\$D\$3:\$D\$18)-MIN(ROW(\$D\$3:\$D\$18))+1, "")

becomes

IF({1;5;0;-2;-5;2;4;4;-3;-4;-4;1;-1;0;-5;1}<0, ROW(\$D\$3:\$D\$18)-MIN(ROW(\$D\$3:\$D\$18))+1, "")

becomes

IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, ROW(\$D\$3:\$D\$18)-MIN(ROW(\$D\$3:\$D\$18))+1, "")

becomes

IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}, "")

and returns

{"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}