Filter records based on a date range and a text string

Answer:

Array formula in cell F10:

How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift simultaneously.
3. Press Enter once.
4. 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).

Get the Excel file

Search-a-column-within-two-dates.xlsx

Filter records category

Match two criteria and return multiple records
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

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

Excel categories

Comments (2)