excel formula for searching string across columns and filtering results

March 4, 2024

I’m trying to write an excel formula that allows me to filter results searching across all columns and sheets in my document.

For example, let’s say my excel file has two sheets (Sheet1 and Sheet2) with the following data:

Sheet1:

A       B       C
Col1    Col2    Col3
foo     1       NA
bar     2       y
baz     3       bar
foo     4       z
bar     5       NA
baz     6       foo

Sheet2:

A       B       C
Col1    Col2    Col3
foo     7       bar
bar     8       bar
baz     9       bar
foo     10      z
bar     11      y
baz     12      NA

I can write the formula:

=FILTER('Sheet1'!A:C;ISNUMBER(SEARCH("foo";'Sheet1'!A)))

which will return:

foo 1   NA
foo 4   z

However, I cannot find a way to filter the results taking into account all columns and sheets:

foo     1       NA
foo     4       z
baz     6       foo
foo     7       bar
foo     10      z

>Solution :

You will certainly need VSTACK() & MMULT() to accomplish the desired output:

=LET(
     _DataFromBothSheets, VSTACK(Sheet1:Sheet2!A2:C7),
     _Include, MMULT(1-ISERR(SEARCH("foo",_DataFromBothSheets)),{1;1;1}),
     FILTER(_DataFromBothSheets,_Include,""))

Using LET() function helps to define variables and makes easier to read.
Using VSTACK() function, we are appending the data from both the sheets into one.
Using ISNUMBER() or 1-ISERR() which does the same to return TRUE for matched values and FALSE for error values because of the SEARCH() function which returns the position of the start of text.
Using MMULT() to return the matrix product of two arrays here one returned using the above and {1;1;1}
Lastly, the above is the include argument of the FILTER() function when placed gives the desired output.

If its only a word you are trying to find and not within text strings, then you could do it in this way:

=LET(
     _DataFromBothSheets, VSTACK(Sheet1:Sheet2!A2:C7),
     _Include, MMULT(N("foo"=_DataFromBothSheets),{1;1;1}),
     FILTER(_DataFromBothSheets,_Include))

Or could use BYROW() a LAMBDA() helper function which applies a custom LAMBDA() calculations to each row in the array and returns one result per row as a single array.

=LET(
     _DataFromBothSheets, VSTACK(Sheet1:Sheet2!A2:C7),
     _Include, BYROW(_DataFromBothSheets,LAMBDA(x, SUM(--(x="foo")))),
     FILTER(_DataFromBothSheets,_Include))

Also, in Modern Excel Versions, there is a new update for Office Insider users, if you happen to enabled it, then the LAMBDA() construct is not required for BYROW() as well, these are called eta-LAMBDA()

=LET(
     _DataFromBothSheets, VSTACK(Sheet1:Sheet2!A2:C7),
     _Include, BYROW(N("foo"=_DataFromBothSheets),SUM),
     FILTER(_DataFromBothSheets,_Include))