## Extract records containing negative numbers

**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.

### Get 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
- Press with left mouse button on "Data" tab
- Press with left mouse button on "Filter" on the Ribbon
- Press with left mouse button on Black triangle in cell D2

- Select "Number Filters"
- Press with left mouse button on "Less Than..."

- Type 0 (zero)
- Press with left mouse button on 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

### Filter records category

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

Lookup with criteria and return records.

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]

Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]

Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 7 Responses to “Extract records containing negative numbers”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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.

Can you return this values in ascending or descending order

Yes, it is possible.

Ascending:

Descending:

These dynamic array formulas contain two new functions available for Excel 365 subscribers:

How to use the FILTER function

How to use the SORT function

Thank you for this article.

Can you please help in how can I get data in Descending order using this your formula.

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

Abhijit Mishra

=INDEX($B$3:$D$18, LARGE(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1)), COLUMN(A1))