get-digital-help.com

Table of Contents

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

Array formula in B23:

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;""}

Step 2 - Return the k-th smallest row number

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

becomes

SMALL({"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}, ROW(A1))

becomes

SMALL({"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}, 1)

and returns 4.

Step 3 - Return value from cell range

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))

becomes

INDEX($B$3:$D$18, 4, 1)

becomes

INDEX({"MARGARET","ROBINSON",1; "DAVID","DAVIS",5; "MICHAEL","JONES",0; "MARIA","GARCIA",-2; "LISA","RODRIGUEZ",-5; "PATRICIA","JACKSON",2; "MARY","THOMAS",4; "THOMAS","TAYLOR",4; "MICHAEL","JONES",-3; "RICHARD","MILLER",-4; "JOSEPH","MOORE",-4; "LINDA","WHITE",1; "BARBARA","HARRIS",-1; "MARY","THOMAS",0; "JOHN","JOHNSON",-5; "DOROTHY","CLARK",1} 4, 1)

and returns "Maria" in cell B23.

Download excel sample file for this tutorial.

extract negative values and adjacent cells.xls
(Excel 97-2003 Workbook *.xls)

Extract negative values and adjacent cells (Exce