...

Search for a specified string across the whole table

 

We have two Excel sheets, and there are same-structure tables in the two sheets.

Sheet1

A

B

C

1

Col1

Col2

Col3

2

foo

1

NA

3

bar

2

y

4

baz

3

bar

5

foo

4

z

6

bar

5

NA

7

baz

6

foo

Sheet2

A

B

C

1

Col1

Col2

Col3

2

foo

7

bar

3

bar

8

bar

4

baz

9

bar

5

foo

10

z

6

bar

11

y

7

baz

12

NA

Task: Search every cell of each table and match their values with a specified string, such as foo. If the matching succeeds, list the whole current row.

E

F

G

1


2

foo

1

NA

3

foo

4

z

4

baz

6

foo

5

foo

7

bar

6

foo

10

z

Enter the following formula in SPL XLL:

=spl("=(?1|?2).select(~.contain(?3))",Sheet1!A2:C7,Sheet2!A2:C7,"foo")

Picture1png

The symbol | is used to concatenate two sequences.

Source:https://stackoverflow.com/questions/78098122/excel-formula-for-searching-string-across-columns-and-filtering-results


 
 •  76 View  •