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. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
  4. Find positive and negative amounts that net to zero in excel
  5. Extract and sort text cells from a range containing both numerical and text values
  6. Sum adjacent values using multiple lookup text values in a column in excel
  7. Extract cell values in a range using a criterion in excel
  8. Sum adjacent values from a range using multiple lookup values in excel
  9. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
  10. Extract a unique distinct list by matching items that meet a criterion in excel