K
K
kosyan4ik12021-02-03 11:56:09
Google Sheets
kosyan4ik1, 2021-02-03 11:56:09

How to write an ARRAYFORMULA + REGEXMATCH formula to search through a column of values ​​from an array?

Help me figure it out, it only works line by line, or if you choose each one separately.
Here is the formula that works

=ARRAYFORMULA(if(A:A="";;
  REGEXMATCH(A:A;"Седьмой")
))


And what I need is that I would not select each separately as described above, but something like this, but with such a formula, the search is only for a complete match of cells ...

=ARRAYFORMULA(if(A:A="";;
  REGEXMATCH(A:A;D:D)
))


601a64ddaf648785467037.png

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander Ivanov, 2021-02-03
@kosyan4ik1

Maybe,

=ARRAYFORMULA(IF(A2:A="";;
  REGEXMATCH(A2:A;"(" & TEXTJOIN("|";1;D2:D) & ")")
))

But I doubt that I understood the task correctly
601a6cd5eabbc816859669.png
. Alternatively, you need to display the values ​​A:A from the search results.
=ARRAYFORMULA(
  IF(
    (A2:A<>"") * REGEXMATCH(A2:A;"(" & TEXTJOIN("|";1;D2:D) & ")");
    A2:A;

))

601a6fd734047639432651.png
If we need to know the criterion that worked during the search, then REGEXMATCH will not work, we need to use a search by string
=ARRAYFORMULA(TRANSPOSE(TRIM(QUERY(
  IF(IFERROR(FIND(B2:B5;TRANSPOSE(A2:A7));"");B2:B5;);
  ;
  9^9
))))

601aacc02e6e8330983786.png

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question