2

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:

enter image description here

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?

0

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

9


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"))

  • Thank you for your time. The formula looks good but there's some glitch. If you mind look at the link: docs.google.com/spreadsheets/d/… If there's a two similar codes in database like 2240 and D2240 there's only one that can be find. I have multiple codes that short like 2240 and another one like 342240001. Any ideas how to fix it? – Wojtek Jan 9 at 9:50

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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