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

*Article last updated on August 24, 2018*

The picture above shows you a dataset in cell range B3:E12, the search parameters are in D14:D16. The search results are in B19:E23.

The formula in cell B20 searches for values that meet a range criteria (cell D14 and D15), you can change the column to search in with cell D16.

The picture above shows the array formula and it uses column three (D16) in cell range B3:E12 to find values that match, the formula returns the record if there is a match.

This formula can be used with whatever size and shape of range**. **To search the first column, type 1 in cell D16.

**Update 20 Sep 2017, a ****smaller**** formula in cell A19.**

**Array formula in cell A20:**

See this video to learn more about the formula:

### How to enter this array formula

- Select cell A20
- Paste above formula to cell or formula bar
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- Release all keys

The formula bar now shows the formula with a beginning and ending curly bracket, that is if you did the above steps correctly. Like this:

{=array_formula}

Don't enter these characters yourself, they appear automatically.

Now copy cell A20 and paste to cell range A20:E22.

Recommended post:

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

### Explaining array formula in cell A20

You can follow along if you select cell A19, go to tab "Formulas" on the ribbon and click "Evaluate Formula" button.

**Step 1 - Filter a specific column in cell range $A$2:$D$11**

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

becomes

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

and returns C2:C11

Gets a value in a specific cell range based on a row and column number.

**Step 2 - Check which values are in range**

(INDEX($B$3:$E$12, , $D$16, 1)<=$D$15)*(INDEX($B$3:$E$12, , $D$16, 1)>=$D$14)

becomes

({2;6;4;5;3;9;3;2;0;1}<=$C$14)*({2;6;4;5;3;9;3;2;0;1}>=$C$13)

becomes

({2;6;4;5;3;9;3;2;0;1}<=3)*({2;6;4;5;3;9;3;2;0;1}>=0)

becomes

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE}*{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

and returns

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

**Step 3 - Return corresponding row number**

IF((INDEX($B$3:$E$12, , $D$16)<=$D$15)*(INDEX($B$3:$E$12, , $D$16)>=$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), "")

becomes

IF({1;0;0;0;1;0;1;1;1;1}, MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), "")

becomes

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

and returns

{1;"";"";"";5;"";7;8;9;10}

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

**Step 4 - Find k-th smallest row number**

SMALL(IF((INDEX($B$3:$E$12, , $D$16)<=$D$15)*(INDEX($B$3:$E$12, , $D$16)>=$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20))

becomes

SMALL({1;"";"";"";5;"";7;8;9;10}, ROWS(B20:$B$20))

becomes

SMALL({1;"";"";"";5;"";7;8;9;10}, 1)

and returns 1.

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

**Step 5 - Return entire row from cell range**

INDEX($B$3:$E$12, SMALL(IF((INDEX($B$3:$E$12, , $D$16)<=$D$15)*(INDEX($B$3:$E$12, , $D$16)>=$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($A$1:A1))

becomes

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

and returns cell range $B$3:$E$12 in cell range B20:E20: {1,"John Doe",2,"North"}

Gets a value in a specific cell range based on a row and column number.

#### Download Excel *.xlsx file

Extract-all-rows-that-contain-a-value-between-this-and-that-part-21v3.xlsx

### Recommended posts

Read this post and see how to extract duplicate records:

This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]

Learn how to filter unique distinct records:

Filter unique distinct records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Filter records that contain numeric values

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

Search values distributed horizontally and return corresponding value

Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

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

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

Extract records between two dates

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

### 61 Responses to “Extract all rows from a range that meet criteria in one column”

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

No need for array formulas. Extracting rows that match certain criteria would probably be better done by an advanced filter.

Yes, probably.

There are a lot of steps to create an advanced filter. Doing this multiple times might be timeconsuming? Creating an array formula can also be timeconsuming but when in place only two cell values (C14 and C15) need to be changed to create a new search.

The above array formula is quite small and can be used with a range of any size and shape.

I might be wrong, what do you think?

Hi - I have tried to follow your solution above however only the first record that matches the criteria is displayed over and over again - can you think of what i might be doing wrong?

Hi - bit more clarity - when i downloaded your example, I highlighted the search results section and pressed Ctril + Shift + Enter and the same error came up?

John,

1. Select A19:D19

Copy the array formula to A19:D19 into the formula field + CTRL + SHIFT + Enter , in the example above.

2. Select A19:D19

3. Copy (Ctrl + C)

4. Select B19:D25

5. Paste (Ctrl + V)

Copying cells changes relative references in formulas.

Here is an excellent explanation of absolute and relative references:

https://www.cpearson.com/excel/relative.aspx

I am trying to get this to work with about 3x the columns that you have here, up to the third column it works as it's supposed to but after the third column it continues to display the first columns name, the same as Johns problem instead of displaying the number that column should contain.. any help?

Hello David,

Did you get a reply on this I am experiencing the same problem.

Thank you

The formulas themselves look exactly the same going across the row, but what is displayed doesnt work..

Hello,

i know this post is very old. but did you get an answer for this?

george,

The formulas themselves look exactly the same going across the row, but what is displayed doesn't work..No, the formula changes as you copy the cell (not the formula) and paste to cell range B20:E22.

I have bolded the part that changes automatically because of the use of relative cell references:

=INDEX($B$3:$E$12, SMALL(IF((INDEX($B$3:$E$12, , $D$16)< =$D$15)*(INDEX($B$3:$E$12, , $D$16)>=$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""),

ROWS(B20:$B$20)), COLUMNS($A$1:A1))Oscar, how do i get the formulas in cell e21 thru h21 to read the data from cells e3 thru h3? thanks. the current data displayed correctly is in a21 thru d21. I attached a file yesterday using the form.

George,

Oscar, how do i get the formulas in cell e21 thru h21 to read the data from cells e3 thru h3? thanks. the current data displayed correctly is in a21 thru d21. I attached a file yesterday using the form.I have replied to your email.

Sorry for the confusion, I realized that the attached file was an old file. I have now uploaded a new workbook that better demonstrates the formula in this post.

Does anyone know of a function that can list data from cells relating to a certain criteria?

Here's what I'm trying to do:

On sheet 1 I have a spreadsheet containing publications with various columns for each publication such as title, author, topics covered etc. Each publication is given a reference number.

On sheet 2 I want to create a list by author where it automatically lists the publications (by reference number), that s/he appears in, within one cell.

Oscar perhaps you could help?

@Dan,

If I am reading your question correctly, I believe the UDF (user defined function) I posted in my mini-blog article here will do what you want...

https://www.excelfox.com/forum/f22/lookup-value-concatenate-all-found-results-345/

Hi,

Could you help me so have the results in another sheet (eg: Sheet2). When I tried, getting an error "You Cannot change part of an array".

Thanks

How would I tweak the above formula to look at 2 criteria? In the example above say I wanted the value = 3 AND the company to = south.

My exact problem is I have a data sheet of all invoices for our company. Column headings include customer, invoice number, PO Number, units sold, open/closed, ect. So on a separate sheet I have the formula set up so that based on a drop down you can select the customer, and from there it pulls all invoices from my data sheet. My question is how do I tweak the formula to look at both my customer column and the open/closed column. So I want to be able to see the customers with open invoices. The two cells filled with yellow are my input cells and are the criteria for my table below. You can see based on the yellow box with “AAA Sales” selected, my table is returning the correct customer but now I want only open invoices.

https://postimg.org/image/k012ex1ih/

John Cejka,

Download *.xlsx fileExtract-all-rows-that-contain-a-value-between-this-and-that-part-21-John-Cejka.xlsx

Thanks Oscar, that is what I need and it works perfectly on your sheet, but I cannot copy and or paste the formula, or even manually type it. I think it has something to do with the fact that on your sheet I see { at the beggining and } at the end of your formula. Any suggestions?

Your formula and template work fantastic. I am having one issue that I have spent more time than I wish to say on trying to alter it, to no avail. What I need to do is expand the amount of columns it pulls. I need it to pull 2 in front (to the left) of the search number and 7 after (to the right) of the search number. The immediate 2 columns to the right of the searched number is actually hidden and not used, but I wasn't sure if it would make it to complicated to pull the 5 after the 2 hidden. Please help. Thanks in advance.

I use filters like this every day, there's a really simple way to hide all rows that have a certain value in one column: Simply add filters to every column.

> Don't select any cells - just click one in the middle of your spreadsheet somewhere.

> Hit Ctrl+Shift+L (You should see drop down arrows appear at the top of every column - if you don't have column headers you will want to put some in, otherwise your first row of data will be treated like column headers.)

> Go to the column with the data you're trying to remove, click the drop down, and uncheck that data in the list.

> All rows containing that data will be automatically hidden.

Jason,

Yes, filters are very useful.

Thank you for commenting!

Using this as an example I want to extract all records with Company = East and place those records in sheet 2. Can you show me how to do that?

Ultimately I want to have multiple sheets showing subsets of records from sheet 1 that match a text criteria. So again, using this example, sheet 2 would show all records for Company = East, sheet 3 would have all records for company = West, etc.

=IF(ISERROR(INDEX(EOD.xlsx!$A$1:$I$1648,SMALL(IF(AND(EOD.xlsx!$I$1:$I$1648>=40000,EOD.xlsx!$I$1:$I$1648=40000,EOD.xlsx!$I$1:$I$1648=40000), it gives the desired result , BUT while using AND operator(to match if the value is greater than or less than), it doesn't give desired results. What am I doing wrong here?

=IF(ISERROR(INDEX(EOD.xlsx!$A$1:$I$1648,SMALL(IF(AND(EOD.xlsx!$I$1:$I$1648>=40000,EOD.xlsx!$I$1:$I$1648=40000,EOD.xlsx!$I$1:$I$1648<50000),ROW(EOD.xlsx!$I$1:$I$1648)),ROW(1:1)),1))

When I use single condition, it gives the desired results BUT while using AND operator(to match if the value is greater than or less than), it doesn't give desired results. What am I doing wrong here?

[…] = window.adsbygoogle || []).push({}); Dear friends, I found this site (Extract all rows from a range that meet criteria in one column in excel | Get Digital Help - Microso…) with a very good formula and very helpful for my task that I have to complete. I attached these […]

if i have a range of values and i am asked to display values greater than a specific value only,what do i do?

daniel,

Use this formula:

=INDEX(tbl, SMALL(IF((INDEX(tbl, , $C$15, 1)<=$C$14), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(A19:$A$19)), , 1)

I have been trying to make this code work for me but I am at a total loss. I have Colomn C and when it turns to GA (through an IF Function) I would like excel to copy the row and paste it into another sheet in the file. How can I accomplish this.

Good day

I have table 1

Jan Feb Mar Apr May Jun

Ben 1 1

Ben 1

Ben 1

Rick 1

Rick 1

Rick

then I have Table 2

Jan Feb Mar Apr May Jun

Ben

Rick

I need a formula in table 2 that will look in table 1

and bring back the number 1 for Ben under Jan , Feb , May and June

and the same for Rick under Mar and Apr

thanks

Hello.

I have a drop down list in column H. Values = A, B and C

I would like to have a dynamic column I, whereby, if I select 'A' in column H, than a defined list would be presented in the cell next to it. If I select 'B', a different predefined list would be presented.

Any help would be greatly appreciated.

Hi Oscar

Your solution/ formula is fantastic. I tried to modify it to my range but it only extracts the first four columns only.

Hence I added one more col. to your sheet (thinking that I might be doing something wrong)but here to it only copies the first four cols only.

What I did was ( in the result area) copied formula from D19 to E19 but it extracted the value from col A instead from from col E.

Please let me know what I am doing wrong.

Many thanks

I have the exact same problem. It only works for 4 columns. When I try to put an additional column to the right I get the values of the 1st column instead of the 5th.

Hi How do I get rid of the #NUM and show a blank please?

how to extract opposite?

a given date through a range of dates: B1 lower date, c1 higher date

only values where given date, ex $d$10, is between b1 and c1

extract a1....

You need to use the IFERROR function

=IFERROR(INSERT YOUR CODE HERE,"")

Hi, I am trying to use this code to copy rows to a different sheet column 4 must equal "A". i dont want to use the 3 range cells C13,14 &15 to set the criteria. i have named my source table tbl same as above and is located in sheet1 and i want to copy all the rows that meet the criteria to sheet2.

thank you

Stephen, you don't have to use a formula to extract records where column 4 is equal to A.

1. Select the range you want to filter

2. Go to tab "Home" on the ribbon

3. Click "Sort & Filter" button

4. Click the arrow in column 4 and only select "A"

Now you can copy filtered rows to sheet2.

However, if you do want to use a formula this post describes how to:

https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#multiple

Remember to change the cell ref to column 4 in the formula.

Hi,

I am trying to modify this formula so that I display only the rows where the value in the '$C$15' column are blank. What do I do?

Hi Oscar

Really love your approach.

I added one more column......but....the data on my 5th column gives the results of the first column...

I know you have been asked many a times...i read carefully what you replied...but its still does not work...

Please Please help.

thanks

if i have 3 range of values and i am asked to display that 1st condition is equal to specific value, 2nd condition not equal to specific value only, extract row based all values without repeat what do i do?

repeat x times based on cell value and based on condition

Hi Oscar! I like your stuff very much but this formula can't seem to work for me. It returns #N/A's..

{=IF((INDEX($A$2:$D$11, , $C$15, 1)=$C$13), MATCH(ROW($A$2:$D$11), ROW($A$2:$D$11)), "")}

Also, why are you using $A$2:$D$11 as array? Shouldn't it be $B$3:$E$12?

Thanks

Hi Martin

this formula can't seem to work for me. It returns #N/A's..Did you enter the formula as an array formula?

Also, why are you using $A$2:$D$11 as array? Shouldn't it be $B$3:$E$12?Yes, the explanation uses $A$2:$D$11 but it should be $B$3:$E$12. I have updated the article.

Sorry for the confusion.

Oscar i freaking love you right now. You helped me solve the issue with my spreadsheet that i have been struggling with for a week.

Alec

Thank you, I am happy I could help you out.

I feel like I could figure the rest of this out, but I still don't understand how, in the first example, when he is extending down the index formula (right around the 3:10 mark), it is not just returning the first result over and over. I see other people have asked, and he responded regarding absolute vs relative, but I don't see how that would apply, since in his example, he is using absolute references on all the cell references.

Can Oscar or someone else explain in another way? I'm really lost.

Hi Tim

he is using absolute references on all the cell references.Not all cell references, this part of the formula is different:

ROWS(B20:$B$20)), COLUMNS($A$1:A1))

The first part is absolute and the second part of the cell reference is relative or vice versa.

What if you have this data (table data) and you need to get the Total stone wt. (highlighted in blue box)(pls. click link for the screenshot https://postimg.cc/image/mlu9yty93/) How would you formulate it for Index Match? Is it possible to get the desired result?

Let me know. Thanks.

Aiz

Sorry if I don't understand.

Why can't you use the SUM function?

Hi ,

I have the data in below format:

A -100

B -234

C -32

A -123

B -221

D -456

A -145

B -245

C -312

D -478

I want to format this data as:

A B C D

100 234 32

123 221 456

145 245 312 478

Could you please help me how it can be done in excel?

Regards,

Neeraj Sharma

Hi Oscar - Thanks for this amazing formula. Really appreciate the clear explanation on the video.

Tribhuvan Gupta,

thank you!

MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12))

Could you please explain this part.

I am getting only 1 in all rows or this

Jinson,

MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)) creates an array {1;2;3;4;5;6;7;8;9;10} used to identify which row to get values from.

I believe you get 1 in all rows because you didn't enter the formula as an array formula. CTRL + SHIFT + ENTER

=IF(FIND(",",C6,1)>0,LEFT(C6,(FIND(",",C6,1)-1)),C6) where there is no comma in cell am getting error

This is a great formula and I've almost solved my issue using it, so thank you! The last piece of my problem requires me to further narrow down the search results using not only one category, but two categories. Is it possible to further narrow down the results to only show one type of company (ie only show results between values of 4 and 6 who's company is called North?

Fotini,

Yes, it is possible.

=INDEX($B$3:$E$12, SMALL(IF((INDEX($B$3:$E$12, , $D$16)< =$D$15)*(INDEX($B$3:$E$12, , $D$16)>=$D$14)*($E$3:$E$12="North"), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($A$1:A1))

Hi,

How do you get the "#NUM!" to hide like in your other table. Is it possible to make it look empty or contain a dash rather than that?

Also thank you! This was very helpful.

DIana,

I use the IFERROR function to catch errors, however, use with caution, it takes all kinds of errors.

=IFERROR(INDEX($B$3:$E$12, SMALL(IF((INDEX($B$3:$E$12, , $D$16)< =$D$15)*(INDEX($B$3:$E$12, , $D$16)>=$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($A$1:A1)),"")

or a dash:

=IFERROR(INDEX($B$3:$E$12, SMALL(IF((INDEX($B$3:$E$12, , $D$16)< =$D$15)*(INDEX($B$3:$E$12, , $D$16)>=$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($A$1:A1)),"-")

Oscar,

I wanted to take a moment to say a massive thank you for your work above.

I am relatively a novice and have been struggling for weeks to get something exactly as above.

I will be reading with interest your other articles following this, as I suspect they will all be of equal quality and a great opportunity to expand my own knowledge.

Many thanks again.

MJB.

MJB,

thank you!