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

I will in this article demonstrate several techniques that extract or filter records based on two conditions applied to a single column in your dataset. For example, if you use the array formula then the result will refresh instantly when you enter new start and end values.

The remaining built-in techniques need a little more manual work in order to apply new conditions, however, they are fast. The downside with the array formula is that it may become slow if you are working with huge amounts of data.

I have also written an article in case you need to find records that match one condition in one column and another condition in another column. The following article shows you how to build a formula that uses an arbitrary number of conditions: Extract records where all criteria match if not empty

This article Extract records between two dates is very similar to the current one you are reading right now, Excel dates are actually numbers formatted as dates in Excel. If you want to search for a text string within a given date range then read this article: Filter records based on a date range and a text string

I must recommend this article if you want to do a wildcard search across all columns in a data set, it also returns all matching records. If you want to extract records based on criteria and not a numerical range then read this part of this article.

**What is on this page?**

- Extract all rows from a range based on range criteria

[Array formula] - Extract all rows from a range based on multiple conditions

[Array formula] - Extract all rows from a range based on range critera

[Excel defined Table] - Extract all rows from a range based on range critera

[Filter] - Extract all rows from a range based on range criteria

[Advanced Filter] - Download Excel file

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

**Update 17 December 2020, **the new FILTER function is now available for Excel 365 users. Formula in cell B20:

It is a regular formula, however, it returns an array of values and extends automatically to cells below and to the right. Microsoft calls this a dynamic array and spilled array.

The array formula below is for earlier Excel versions, it searches for values that meet a range criterion (cell D14 and D15), the formula lets you change the column to search in with cell D16.

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

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

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.

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

#### Recommended reading

- Match two criteria and return multiple records
- Extract records where all criteria match if not empty
- Extract all rows that contain a value between this and that
- Extract records between two dates
- Filter records based on a date range and a text string
- Search for a text string in a data set
- Extract records containing negative values
- Extract records containing digits [Formula]

## Extract all rows from a range that meet criteria in one column [Array formula]

The array formula in cell B20 extracts records where column E equals either "South" or "East".

**Update 17 December 2020, **the new FILTER function is now available for Excel 365 users. Formula in cell B20:

It is a regular formula, however, it returns an array of values. Read here how it works: Filter values based on criteria

The formula extends automatically to cells below and to the right. Microsoft calls this a dynamic array and spilled array.

The following array formula in cell B20 is for earlier Excel versions:

To enter an array formula, type the formula in a cell 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.

### Explaining formula in cell B20

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

The COUNTIF function allows you to identify cells in range $E$3:$E$12 that equals $E$15:$E$16.

COUNTIF($E$15:$E$16,$E$3:$E$12)

becomes

COUNTIF({"South"; "East"},{"North"; "North"; "South"; "West"; "South"; "East"; "West"; "West"; "South"; "East"})

and returns

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

#### Step 2 - Return corresponding row number

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

The logical expression was calculated in step 1 , TRUE equals 1 and FALSE equals 0 (zero).

IF(COUNTIF($E$15:$E$16,$E$3:$E$12), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), "")

becomes

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

becomes

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

and returns

{""; ""; 3; ""; 5; 6; ""; ""; 9; 10}.

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

SMALL(IF(COUNTIF($E$15:$E$16,$E$3:$E$12), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20))

becomes

SMALL({""; ""; 3; ""; 5; 6; ""; ""; 9; 10}, ROWS(B20:$B$20))

becomes

SMALL({""; ""; 3; ""; 5; 6; ""; ""; 9; 10}, 1)

and returns 3.

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

The INDEX function returns a value based on a cell reference and column/row numbers.

INDEX($B$3:$E$12, SMALL(IF(COUNTIF($E$15:$E$16,$E$3:$E$12), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($B$2:B2))

becomes

INDEX($B$3:$E$12, 3, COLUMNS($B$2:B2))

becomes

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

and returns 3 in cell B20.

#### Recommended reading

- Match two criteria and return multiple records
- Extract records where all criteria match if not empty
- Extract all rows that contain a value between this and that
- Extract records between two dates
- Filter records based on a date range and a text string
- Search for a text string in a data set
- Extract records containing negative values
- Extract records containing digits [Formula]

## Extract all rows from a range that meet criteria in one column [Excel defined Table]

The image above shows a dataset converted to an Excel defined Table, a number filter has been applied to the third column in the table.

Here are the instructions to create an Excel Table and filter values in column 3.

- Select a cell in the dataset.
- Press CTRL + T

- Click check box "My table has headers".
- Click OK button.

The image above shows the Excel defined Table, here is how to filter D between 4 and 6:

- Click black arrow next to header.
- Click "Number Filters".
- Click "Between...".

- Type 4 and 6.

- Click OK button.

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

The image above shows filtered records based on two conditions, values in column D are larger or equal to 4 or smaller or equal to 6.

Here is how to apply Filter arrows to a dataset.

Black arrows appear next to each header.

Lets filter records based on conditions applied to column D.

- Click black arrow next to header in Column D, see image below.
- Click "Number Filters".
- Click "Between".

- Type 4 and 6 in the dialog box shown below.

- Click OK button.

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

[Advanced Filter]

The image above shows a filtered dataset in cell range B5:E15 using Advanced Filter which is a powerful feature in Excel.

Here is how to apply a filter:

- Create headers for the column you want to filter, preferably above or below your data set.

Your filters will possibly disappear if placed next to the data set because rows may become hidden when the filter is applied. - Select the entire dataset including headers.
- Go to tab "Data" on the ribbon.
- Click the "Advanced" button.

- A dialog box appears.

- Select the criteria range C2:D3, shown ithe n above image.
- Click OK button.

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

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 multiple records

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

Extract records where all criteria match if not empty

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

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 containing digits [Formula]

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

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

Extract records containing negative numbers

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

Filter records based on a date range and a text string

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

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

### Leave a Reply to Rick Rothstein (MVP - Excel)

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

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?

How does this work if the value you are looking for is a date? For example, I am trying to return rows whose dates match my search criteria. Also, why select rows A1 for the small formula for the "k" value when theres nothing in the cell. Whenever I try it i get a number error.

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!

This does precisely what I'm wanting to achieve, but how do I implement it using VBA?

Regards,

Ben

How does this work if the value you are looking for is a date? For example, I am trying to return rows whose dates match my search criteria. Also, why select rows A1 for the small formula for the "k" value when theres nothing in the cell. Whenever I try it i get a number error.

Oscar,

Quick question. can this exact same spreadsheet work if you remove the item number column? or in other words, can this work if it only has name, value, and company without some numerical way of tracking/identifying each row?

Ross,

Yes, this take a look at this example Extract all rows from a range based on multiple conditions

[Array formula]. Although the example has a item number column you don't have to reference that column in your workbook.

The formula becomes:

=INDEX($C$3:$E$12, SMALL(IF(COUNTIF($E$15:$E$16,$E$3:$E$12), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($B$2:B2))

Hello,

I am trying to extract all rows that contain any text. I have done this, following your example, in the following way:

=LOOKUP(2; 1/((COUNTIF($A$43:A43; 'Raw Data'!$Y$3:$Y$400)=0)*(MMULT(--(ISNUMBER(SEARCH(""; 'Raw Data'!$Y$3:$Y$400))); {1})=1)); 'Raw Data'!$Y$3:$Y$400)

(notice that my Excel is set up so that I must use ; where you would use ,)

This works fine for all strings up to 255 characters. Strings of more than 255 characters do not show up in my list. Is there any way to get around this?

Thank you!

Regards,

Adam

'Object Display Latest Premium Paid Date

'Original Formula in Policy Detail Worksheet ->

' =IFERROR(INDEX(Prem_Pay_Hist[Date of Premium Payment],

' LARGE(

' IF(Prem_Pay_Hist[Policy No]=Pol_details_Policy_no,

' ROW(Prem_Pay_Hist[Date of Premium Payment])-MIN(ROW(Prem_Pay_Hist[Date of Premium Payment]))+1),

' 1)

' ),

' "")

This cell formula works fine but I want to code it in vba

How to code this formula in excel vba

Thanks for this incredible formula! When I use this, it displays 0 for cells where the original data is empty cell. How do I avoid that? And display empty cell as original?

Is there a non array version for Extract all rows from a range that meet criteria in one column:

=INDEX($B$3:$E$12, SMALL(IF(COUNTIF($E$15:$E$16,$E$3:$E$12), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($B$2:B2))

Appreciate for the help over here. thanks

Hi

If all the information comes from one Column A & I have two difference criteria's names, how to retrieve a table between two difference names?

The table varies in row number & there are ten tables to retrieve.

$A$6:$A$300 is the criteria between name range. Helper column BV4 & BV34

$A$6:$J$300 is the table range.

No is start of table heading over ten columns.

1 to 24E is the numbers down table to retrieve. Helper column CL8, CL7 & CL6 for

1, 24E & 0

The formula below is the extract match of the table but table varies?

{=INDEX($A$35:$J$45,SMALL(IF((INDEX($A$35:$J$45,,$CL$8)$CL$6),MATCH(ROW($A$35:$J$45),ROW($A$35:$J$45)),""),ROWS($CM$6:CM6)),COLUMNS($A$34:A34))}

The formula below is the extract match of the criteria but can’t stop & add next table in same table.

{=INDEX($A$6:$J$300, SMALL(IF(COUNTIF($BU$9:$BV$34,$Z$6:$Z$300), MATCH(ROW($A$6:$J$300), ROW($A$6:$J$300)), ""), ROWS($A$6:A6)), COLUMNS(A$6:$A6))}

Any help would be greatly appreciated.

Regards

Tony

Mistake first formula should read

{=INDEX($A$35:$J$45,SMALL(IF((INDEX($A$35:$J$45,,$CL$8)$CL$6),MATCH(ROW($A$35:$J$45),ROW($A$35:$J$45)),""),ROWS($CM$6:CM6)),COLUMNS($A$34:A34))}

I tried to download the excel file and in Dataset - criteria tab when I press the enter key in B20 it becomes #Value. Why is it?

Des, I'm having exactly the same issue. I have a relatively big data set of 18 or so column.

When trying to get the k-th number it returns #Value on the rows to be excluded and #NUM on the rows to be included.

I figured it out. The cell needs to be indexed. F2+Cnrl+Enter

Hi Oscar,

This Function works great for me vertically however, when I drag right or left to capture the other information in the row, I get a reference error and the only change I can spot in in the final Rows and Columns range where it extends.

Do you know why this might be happening and how I can over come this?

My version of the function is:

{=INDEX('1. W2W Tracker'!$F$4:$F$176, SMALL(IF(COUNTIF($B$5:$B$7,'1. W2W Tracker'!$F$4:$F$176), MATCH(ROW('1. W2W Tracker'!$C$4:$K$176), ROW('1. W2W Tracker'!$C$4:$K$176)),""), ROWS($C$5:C5)), COLUMNS($C$5:C5))}

your instruction is very good.

just wondering if i just wanted only > certain value only without the 4 no upper limit do i still have to use small function?

what should the formula be, please advice, thank you.