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

*Article updated on February 13, 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:

Learn the basics of Excel arrays

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 sample file for this tutorial

Extract all records that meet criteria in a column.xls

(Excel 97-2003 Workbook *.xls)

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

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 […]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 […]### 45 Responses to “Extract all rows from a range that meet criteria in one column”

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

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

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?