Find cell that contains some phrase

Question

I'm looking for replacement of Excel formula:

=if.error(lookup(1000;search($B$1:$B$2;A1);$B$1:$B$2); "NONE")

The sheet looks like this:

So in column A, I have a long product index, in column B, I have a database of indexes. In column C, I want to receive an index from a database if a cell from column A contains it.

Could anyone help me with that?

Answer 1

=ARRAYFORMULA(IFERROR(LOOKUP(1000, SEARCH($B$1:$B$4, A1), 
                                          $B$1:$B$4), "NONE"))

---

to make it more intuitive (this way you don't need to edit B range when your DB grows):

=ARRAYFORMULA(IFERROR(LOOKUP(1000, SEARCH(INDIRECT("$B$1:$B$"&COUNTA(B$1:B)), A1), 
                                          INDIRECT("$B$1:$B$"&COUNTA(B$1:B))), "NONE"))