## 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 end dt. D2 Search "soft" in entire column for" Microsoft"

### Answer:

Array formula in cell F10:

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- Release all keys.

Copy cell F10 and paste to cell range F10:H12.

### Explaining array formula in cell F10

#### Step 1 - Find cells containing the search string

The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive), if string is not found the function returns an error value. If the ISERROR function returns FALSE the logical expression returns TRUE.

ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE

becomes

ISERROR(SEARCH("soft", {"Microsoft"; "IBM"; "Microsoft"; "Microsoft"; "3M"; "GE"; "3M"; "GE"; "Microsoft"; "GE"; "Oracle"; "Oracle"; "3M"; "IBM"; "Microsoft"; "Microsoft"; "IBM"; "Microsoft"; "3M"; "IBM"; "GE"; "GE"; "Oracle"; "GE"; "IBM"; "IBM"; "GE"}))=FALSE

becomes

ISERROR({6; #VALUE!; 6; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 6; 6; #VALUE!; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!})=FALSE

becomes

{FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}=FALSE

and returns

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

#### Step 2 - Find cells matching dates criteria

The following two logical expressions calculates which records have dates inside the given date range. These two arrays are multiplied to apply AND logic meaning if both logical expressions return TRUE the formula returns TRUE or the equivalent numerical value which is 1. 0 (zero) is FALSE.

($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29)

becomes

(40513<={40544; 40485; 40563; 40482; 40576; 40482; 40481; 40509; 40504; 40512; 40611; 40560; 40509; 40448; 40461; 40607; 40453; 40459; 40574; 40607; 40625; 40465; 40604; 40490; 40569; 40528; 40641})*(40575>={40544; 40485; 40563; 40482; 40576; 40482; 40481; 40509; 40504; 40512; 40611; 40560; 40509; 40448; 40461; 40607; 40453; 40459; 40574; 40607; 40625; 40465; 40604; 40490; 40569; 40528; 40641})

becomes

({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE})*({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE})

and returns

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

#### Step 3 - Convert matching cells and cells containing search string into row numbers

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). If the ISERROR function returns FALSE (zero) then the IF function returns the corresponding row number.

IF((ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE)*($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29), MATCH(ROW($C$3:$C$29), ROW($C$3:$C$29)), "")

becomes

IF({1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, MATCH(ROW($C$3:$C$29), ROW($C$3:$C$29)), "")

becomes

IF({1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27}, "")

and returns

{1; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""}

#### Step 4 - Return the k-th smallest number

The SMALL function returns the k-th smallest value in the array based on the COLUMN function and a relative cell reference. Then the cell is copied to cells below the relative cell reference changes, this makes the SMALL function return a new value in each cell.

SMALL(IF((ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE)*($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29), MATCH(ROW($C$3:$C$29), ROW($C$3:$C$29)), ""), ROW(A1))

becomes

SMALL({1; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""}, ROW(A1))

becomes

SMALL({1; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""}, 1)

and returns 1.

#### Step 5 - Return a value of the cell at the intersection of a particular row and column

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

INDEX($B$3:$D$29, SMALL(IF((ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE)*($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29), MATCH(ROW($C$3:$C$29), ROW($C$3:$C$29)), ""), ROW(A1)), COLUMN(A1))

becomes

=INDEX($B$3:$D$29, 1, COLUMN(A1))

becomes

=INDEX($B$3:$D$29, 1, 1)

becomes

=INDEX({40544, "Microsoft", 72,9; 40485, "IBM", 11,1; 40563, "Microsoft", 29,6; 40482, "Microsoft", 44,1; 40576, "3M", 13,7; 40482, "GE", 87,9; 40481, "3M", 84,4; 40509, "GE", 93,1; 40504, "Microsoft", 32,9; 40512, "GE", 21,8; 40611, "Oracle", 19,6; 40560, "Oracle", 88; 40509, "3M", 25,2; 40448, "IBM", 52,1; 40461, "Microsoft", 26,4; 40607, "Microsoft", 76,4; 40453, "IBM", 55,7; 40459, "Microsoft", 37,6; 40574, "3M", 42,9; 40607, "IBM", 52,6; 40625, "GE", 54,7; 40465, "GE", 64,8; 40604, "Oracle", 25,6; 40490, "GE", 49,2; 40569, "IBM", 72,1; 40528, "IBM", 3,5; 40641, "GE", 53,6}, 1, 1)

and returns 40544 (1-Jan-2011).

### Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.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 […]

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

Lookup with criteria and return records.

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

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

**Contact Oscar**

You can contact me through this contact form