How to write a formula for searching in a range and displaying values ​​in an adjacent column?

Question

Google Sheets

Greetings, is it possible to display all cells with a match (if the cell contains the word "BP") in one column where the formula fits.
=VLOOKUP("*BP *";A8:A27;1;) - displays only the first match, ignores the rest.

Answer 1

You need to use a formula that expands its own range.
=FILTER(A2:A15;REGEXMATCH(A8:A27;".*БП.*"))
The most profitable option in this case is FILTER
=FILTER(A2:A15;REGEXMATCH(A8:A27;"БП"))