Filter records within two dates and search for a text string in excel

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Dates > Filter records within two dates and search for a text string in excel

← Previous post -

Filed in Dates, Excel, Search/Lookup on Mar.28, 2011. Email This article to a Friend

Murlidhar asks:

How i search text in cell within two dates
i.e st.Dt D1 end dt. D2 Search "soft" in entire column for" Microsoft"

Answer:

Array formula in cell F10:

=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), ROW($A$1:INDEX($A$1:$A$1000, ROWS($C$3:$C$29))), ""), ROW(A1)), COLUMN(A1))

How to create an array formula

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

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

Explaining array formula in cell F10

=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), ROW($A$1:INDEX($A$1:$A$1000, ROWS($C$3:$C$29))), ""), ROW(A1)), COLUMN(A1))

Step 1 - Find cells containing search string

=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), ROW($A$1:INDEX($A$1:$A$1000, ROWS($C$3:$C$29))), ""), ROW(A1)), COLUMN(A1))

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

=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), ROW($A$1:INDEX($A$1:$A$1000, ROWS($C$3:$C$29))), ""), ROW(A1)), COLUMN(A1))

($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

=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), ROW($A$1:INDEX($A$1:$A$1000, ROWS($C$3:$C$29))), ""), ROW(A1)), COLUMN(A1))

IF((ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE)*($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29), ROW($A$1:INDEX($A$1:$A$1000, ROWS($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}, ROW($A$1:INDEX($A$1:$A$1000, ROWS($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}, ROW($A$1:INDEX($A$1:$A$1000, 27)), "")

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}, ROW($A$1:$A$27), "")

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

=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), ROW($A$1:INDEX($A$1:$A$1000, ROWS($C$3:$C$29))), ""), ROW(A1)), COLUMN(A1))

SMALL(IF((ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE)*($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29), ROW($A$1:INDEX($A$1:$A$1000, ROWS($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

=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), ROW($A$1:INDEX($A$1:$A$1000, ROWS($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 example file

Search a column within two dates.xls
(Excel 97-2003 Workbook *.xls)

Functions:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROW(reference)
returns the rownumber of a reference

SMALL(array,k)
returns the k-th smallest number in this data set.

IF(logical_test,[value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

SEARCH(find_text, within_text, [start_num])
Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

Search for a text string and return multiple adjacent values

Search for a text string in an excel table

How to create a useful search formula

Filter unique distinct values where adjacent cells contain search string in excel

Text to columns: Split words in a cell (excel array formula)