## Extract negative values and adjacent cells in excel

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

- Select B2:D18
- Click "Data" tab
- Click "Filter" on the Ribbon
- Click Black triangle in cell D2
- Select "Number Filters"
- Click "Less Than..."

- Type 0 (zero)
- 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

### Category: Sort values

Comments(81) Filed in category: Excel, Sort values

Sort dates within a date range using excel array formula

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]Comments(23) Filed in category: Excel, Sort values

Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values

Sort values in parallel (array formula)

Table of contents How to sort a table by Column 1 and then by Column 2 (array formula) How to […]Comments(16) Filed in category: Excel, Sort values

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]Comments(13) Filed in category: Excel, Sort values, Vlookup

Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]Comments(13) Filed in category: Excel, Sort values

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]Comments(12) Filed in category: Dates, Excel, Sort values

Comments(7) Filed in category: Excel, Mod, Sort values

Comments(7) Filed in category: Excel, Sort values

Sort values in a cell using a custom delimiter (vba)

The following macro let´s you select a cell range and a delimiting character. The macro sorts the values in each […]Comments(5) Filed in category: Excel, Sort values

### 3 Responses to “Extract negative values and adjacent cells in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hi,

the formula for Extracting negative values and adjacent cells (array formula) is very useful for me and saved me a lot of time. Now I have one additional question - how can I change the formula to extract only positive value?

Thanks for help

Milan

good...but can you explan...itin detail

yashwant

I have added an explanation to this post.