0

This is my sample data

A ...
1 This doesn't match ...
2 This one has Foo ...
3 In this, Foo exists too ...
4 Here we have Bar this time ...

I have in a different sheet the values to search

A
1 Foo
2 Bar

This is the result I need

A B ...
1 This doesn't match < nothing > ...
2 This one has Foo Foo ...
3 In this, Foo exists too Foo ...
4 Here we have Bar this time Bar ...

For this example the next formula would work

=IFS(regexmatch(A1, "Foo"), "Foo", regexmatch(A1, "Bar"), "Bar")

but in reality I have to search for values in a range, so can't list every term manually.

How can I solve this?

1

1 Answer 1

0

Use map() and filter(), like this:

=map(tocol(A1:A, 1), lambda(t, 
  textjoin(", ", true, 
    ifna(filter(Sheet2!A1:A, search(Sheet2!A1:A, t))) 
  ) 
))

See map(), tocol(), textjoin(), filter() and search().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.