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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Filter unique distinct values where adjacent cells contain search string in excel
  2. Extract dates and adjacent value in a range using a date critera in excel
  3. Find positive and negative amounts that net to zero in excel
  4. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
  5. Extract and sort text cells from a range containing both numerical and text values
  6. Unique list to be created from a column where an adjacent column has text cell values
  7. Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel
  8. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
  9. Extract cell values in a range using a criterion in excel
  10. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel