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

*Article updated on October 27, 2017*

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

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

Recommended article

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.

#### How to create an array formula

- Select cell H2
- Type above array formula
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

Recommended article

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

#### How to copy array formula

- Select cell H2
- Copy (Ctrl + c)
- Select cell I2:J2
- Paste (Ctrl + v)
- Select cell H2:J2
- Copy
- Select cell range H3:J5
- Paste

#### Download excel sample file for this tutorial

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

(Excel 97-2003 Workbook *.xls)

#### Functions in this article:

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

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

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**ROW(**reference**)** returns the rownumber of a reference

**COLUMN(**reference**)
**returns the column number of a reference

Search for a text string in a data set and return matching records

Question: How do I find rows that contain a specific string value in a data set? Answer: Array formula in […]Filter records within two dates and search for a text string

Murlidhar asks: How i search text in cell within two dates i.e st.Dt D1 end dt. D2 Search "soft" in […]### 21 Responses to “Extract all rows that contain a value between this and that”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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. Right click on destinaton cell

4. Click "Paste Special..."

5. Click "Values"

6. Click OK!

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