EN VI

search within certain columns, but output all columns - excel?

How to search within certain columns, but output all columns - excel

I'm trying to search within certain columns but then output all columns. For example, with an excel file containing two sheets:

Sheet1:

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

Sheet2:

A       B       C
Col1    Col2    Col3
1       7       foo
bar     foo     bar
baz     9       bar
foo     foo     z
bar     11      y
baz     foo     NA

Currently I have this formula which displays all columns based on a string match in any of the columns:

=LET(w,WRAPROWS(TOCOL('Sheet1:Sheet2'!A:C,1),3), FILTER(w,BYROW(ISNUMBER(SEARCH("foo",w)), LAMBDA(b,OR(b)))))

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

However, I would like to restrict the search to only columns Col1 and Col2, but still display all columns:

Col1    Col2    Col3
foo     foo     NA
baz     foo     bar
foo     bar     z
bar     foo     bar
foo     foo     z
baz     foo     NA

How should I change the formula to achieve this result?

Solution:

Try something along the lines of using TAKE() function , with reference to my last answer:

=LET(
     w,WRAPROWS(TOCOL(Sheet1:Sheet2!A:C,1),3), 
     FILTER(w,BYROW(ISNUMBER(SEARCH("foo",TAKE(w,,2))), LAMBDA(b,OR(b)))))

Or bit shorter:

=LET(
     _Data, VSTACK(Sheet1:Sheet2!A2:C7), 
     FILTER(_Data,BYROW(1-ISERR(SEARCH("foo",TAKE(_Data,,2))),OR),""))

Or, using MMULT()

=LET(
     _Data, WRAPROWS(TOCOL(Sheet1:Sheet2!A:C,1),3),
     FILTER(_Data, MMULT(1-ISERR(SEARCH("foo",TAKE(_Data,,2))),{1;1})))