search within certain columns, but output all columns - excel

Question

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?

Mayukh Bhattacharya · Accepted Answer · 2024-03-16 14:04:18Z

2

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

edited Mar 16, 2024 at 14:04

answered Mar 16, 2024 at 13:37

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

search within certain columns, but output all columns - excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related