Table of Contents

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.

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 (Excel Filter)

  1. Select B2:D18
  2. Click "Data" tab
  3. Click "Filter" on the Ribbon
  4. Click Black triangle in cell D2

  5. Select "Number Filters"
  6. Click "Less Than..."


  7. Type 0 (zero)
  8. Click OK!

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROW(reference) returns the rownumber of a reference

COLUMN(reference)
returns the column number of a reference

Related posts:

Extract duplicates and their adjacent values

Filter unique distinct values where adjacent cells contain search string in excel

Extract dates and adjacent value in a range using a date critera in excel

Excel udf: Find positive and negative amounts that net to zero

Find positive and negative amounts that net to zero in excel