## Extract all rows that contain a value between this and that

**Question:**I have a list and I want to filter out all rows that have a value (Column C) that is bigger or equal than zero and smaller or equal than three?

The picture above shows you the formula result in cell range B20:C25. The numerical search range is 0 to 3.

**Array formula in B20:**

To enter an array formula, type the formula in cell B20 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell B20 and paste to cell range B20:D25.

#### Watch a video where I explain the formula

### Explaining formula in cell B20

#### Step 1 - Check cell range D3:D12 if equal to or less than cell C16 and equal to or larger than C15

($C$16>=$D$3:$D$12)*($C$15<=$D$3:$D$12)

becomes

(3>={1;10;3;1;5;2;0;7;2;9})*(0<=${1;10;3;1;5;2;0;7;2;9})

becomes

{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE})*({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE})

and returns

{1;0;1;1;0;1;1;0;1;0}

Boolean |
Boolean |
Multiply |
Add |

FALSE | FALSE | 0 (zero) | 0 (zero) |

FALSE | TRUE | 0 (zero) | 1 |

TRUE | TRUE | 1 | 2 |

#### Step 2 - Convert array to row numbers

The IF function lets you use a logical expression to determine which value (argument) to return.

IF(($C$16>=$D$3:$D$12)*($C$15<=$D$3:$D$12), MATCH(ROW($C$3:$C$12), ROW($C$3:$C$12)))

becomes

IF({1;0;1;1;0;1;1;0;1;0}, MATCH(ROW($C$3:$C$12), ROW($C$3:$C$12)))

becomes

IF({1;0;1;1;0;1;1;0;1;0}, {1;2;3;4;5;6;7;8;9;10})

and returns

{1;FALSE;3;4;FALSE;6;7;FALSE;9;FALSE}

#### Step 3 - Get k-th smallest row number

To be able to return a single value from the array we need to use the SMALL function to extract a single row number. The second argument in the SMALL function uses the ROWS function with an expanding cell reference to extract a new value in each cell.

SMALL(IF(($C$16>=$D$3:$D$12)*($C$15<=$D$3:$D$12), MATCH(ROW($C$3:$C$12), ROW($C$3:$C$12))), ROWS($A$1:A1))

becomes

SMALL({1;FALSE;3;4;FALSE;6;7;FALSE;9;FALSE}, ROWS($A$1:A1))

becomes

SMALL({1;FALSE;3;4;FALSE;6;7;FALSE;9;FALSE}, 1)

and returns 1.

#### Step 4 - Return value based on row and column number

The INDEX function returns a value from a cell range based on a row and column number, our cell range is a single column so we need to only specify a row number in order to get the correct value.

INDEX($B$3:$D$12, SMALL(IF(($C$16>=$D$3:$D$12)*($C$15<=$D$3:$D$12), MATCH(ROW($C$3:$C$12), ROW($C$3:$C$12))), ROWS($A$1:A1)), COLUMN(A1))

becomes

INDEX($B$3:$D$12, 1, COLUMN(A1))

becomes

INDEX($B$3:$D$12, 1, 1)

and returns "North" in cell B20.

### Exclude blank values

The image above shows some records contain a blank value, the following formula ignores records that contain a blank value.

Formula in cell B20:

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

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) […]

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 […]

Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]

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

### 33 Responses to “Extract all rows that contain a value between this and that”

### 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

There appears to be an error in your formula.

Row 2 of the list has values of North, 1, 1

The "1" falls in the extract range but it is not included in the results.

Thank you!

I have updated the array formula and the attached file.

this is very very useful, and thanks so much.

q: how would i change the formula, if the "ITEM" is in column A, the "VALUE" is in column B and the "COMPANY" is in column C?

and what is i have another column "NAME" in between columns and ITEM and VALUE

James,

see this blog post: https://www.get-digital-help.com/2009/09/28/extract-all-rows-from-a-range-that-meet-criteria-in-one-column-in-excel/

Is it possible to have the data on one sheet and the sorted information on another sheet using this formula?

Paul,

Yes!

See file!

Extract all rows that contain a value between this and that.xlsx

Thanks thats great. Is it possible to sort the data in the array eg if the search criteria was dates, nearest dates first. If not how can I copy the data from the array to sort using sort and filter.

Thank you very much!

Paul,

How to copy data from the array1. Select the cell range

2. Copy (Ctrl + c)

3. Press with right mouse button on a destination cell

4. Press with left mouse button on "Paste Special..."

5. Press with left mouse button on "Values"

6. Press with left mouse button on the OK button!

Now you can sort and filter copied values!

thanks a lot for this, do you have an example where need to check for 2 conditions?, many thanks!!

Hi,

First: I´ve been analyzing your formula since it could be very useful in a worksheet I´m working, and using the exact same format, I found that it doesn´t work. When pressing CTRL+SHIFT+ENTER, I get a message "You´ve entered to many arguments for this function." I changed the comas for semi-colens, and yet the error persists.

On the other hand, how did you limit to a maximum of 3 (cell F2), this cell doesn´t appear in your formula.

Thanks for the help.

it doesn't work if the range is as C2:F30000. The formula return REF error.

How can I fix it ?

Andrea,

Can you post your formula?

can i have the option not to display #num! error message. display blank or "-"?

Hi,

I tried to copy and paste the formula and adjust it for my use, but it's not returning what I would expect - instead it's returning a single row from the tale that is outside of my range...

I feel like I could guess the logic for most of the inputs, except for the match part of the function, so I feel like that may be a key reason for why it's not working for me. Could someone please explain the logic behind the inputs of the Match function to me (and how that impacts the results of the function small)? Or if someone is willing to share what they did in hopes that I can compare the examples and figure out what I need to change...

Thanks

I've figured out the logic behind the functions and inputs...but for some reason, while I can copy and paste the cells, the minute I try either editing the cells I get an error. I feel like it might be that while the original is somehow able to evaluate into an array of T/F, or {1,....,n} when I try to write the formula it will try to condense the T/F arrays into a single value and/or have issues with dimensions/orientations of the array values. Anyone know of a way to fix this?

Hi Oscar

This is an excellent solution that does not require data refresh like power query.

I modified your query to match two values which suits my requirement: {=IFERROR(INDEX(Data,SMALL(IF(($G$1=Value1)*($G$2=Value2),MATCH(ROW(Item),ROW(Item))),ROW(A1)),COLUMN(A1)),"")"

I am trying to understand the purpose of the Item column. Changing the values in this column does not seem to affect the result.

Thanks

Hi Arthur

Thank you.

I am trying to understand the purpose of the Item column. Changing the values in this column does not seem to affect the result.If you are asking why I use the Item column in the formula the answer is that I use the range to create numbers for each row. As long as the Item column has as many rows as the Data column you can use both (not at the same time), it doesn't matter.

Oscar:

Thanks very much for this tutorial! It has been VERY helpful. However, I must be doing something incorrectly. I have been able to get to a certain point, but I cannot get the formula to return the data in subsequent columns. As I copy the formula across (from column A to B to C, etc.), it just keeps repeating the data from my very first column. Any suggestions would be greatly appreciated!

Peter,

My guess is that the cell ref in your column() function is an absolute cell ref?

Is your formula entered as an array formula? Can you see a beginning and ending curly bracket, like this: {=array_formula} in the formula bar?

Nice website..am glad i have learned lots of stuff .Av managed to complete my excel vba accounting software.keep it up...Dedan frok Kenya

Hi I have one GRN no ex: GRN1 and this will be having one Invoice number i.e 123 in this invoice 4 items will be available i,e Item 1 Item 2 Item 3 Item 4 and each item will be having respecting Qty details in master data.

So My question is How can I extract qty details by considering GRN no and Invoice No.

"GRN No and Invoice No will be unique and will not repeat"

Please help me.

Can you use negative values, or how do you exclude values that may be blank?

Tom,

Negative values seem to work?

I have added content to this article regarding excluding values that may be blank.

Thank you for this addition, this site is very informative and helpful. Great Resource!!

Can this same logic be used to obtain list based on multiple criteria?

To clarify, how do you extract row(s) if matching a value and a price?

Hi.

I need to create a etoll spreadsheet and am struggling!

I have a report telling me what date was the toll charged and the car plate.

I also have another spreadsheet with customer names, plates of the car they rented, start day of the rental and end day for the rental.

what I need is a Result spreadsheet where I can see: on that date that the toll was charged, who was driving that particular car.

Do you have an easy way of doing this? Tried using your "Match two criteria and return multiple records [Array Formula]" but had no success.

Please help!

Thanks and regards,

Stephany

Hi

For some reason whenever I enter =MATCH(ROW($C$3:$C12),ROW($C$3:$C$12)) verbatim I get an N/A error message. I even tried the file to get it to work, and the numbers were there initially, but after I tried copying the formula the number switched to an error message.

Hi

For some reason whenever I enter =MATCH(ROW($C$3:$C12),ROW($C$3:$C$12)) verbatim I get an N/A error message. I even tried the attached file to get it to work, and the numbers were there initially, but after I tried copying the formula the number switched to an error message.

Can we sort the output, ie the solution return rows/columnw which fall betn two values. My query is whether the output can in ascending/descending order of that value. Eg if we search for values betn 3 & 6 in array {1:5:6:2:3:9:4}, out put will be 5:6:3:4, i require it to be 3:4:5:6 or viceversa

Hello Oscar,

Thank you a lot for this explanation! I spent almost two days trying to figure out a way to search a way to return multiple matches based on several criteria involving ranges. This was the solution to this.

Thanks again !

Marvy,

thank you for commenting!