Extract all rows that contain a value between this and that
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:
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 […]
Excel categories
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.
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/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 array
1. 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!