Author: Oscar Cronquist Article last updated on April 16, 2019

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:

=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))

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

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!