0

I need to build a formula that:

If in column X, has the values ​​of column B (regardless of position), returns the values ​​of A

Column A
Amoxicilina
Azitromicina
Cetoconazol
Column B
Amoxicilina
Esomeprazol + Claritromicina + Amoxicilina
Amoxicilina + Clavulanato de potássio
Azitromicina
Cetoconazol
Betametasona + Sulfato de Neomicina + Cetoconazol
Cetoconazol + Betametasona

the result I want is like this:

Column B Column A
Amoxicilina Amoxicilina
Esomeprazol + Claritromicina + Amoxicilina Amoxicilina
Amoxicilina + Clavulanato de potássio Amoxicilina
Azitromicina Azitromicina
Cetoconazol Cetoconazol
Betametasona + Sulfato de Neomicina + Cetoconazol Cetoconazol
Cetoconazol + Betametasona Cetoconazol
player0
  • 124,011
  • 12
  • 67
  • 124
Melo
  • 49
  • 6

2 Answers2

2

use:

=ARRAYFORMULA(IFNA(REGEXEXTRACT(B1:B, TEXTJOIN("|", 1, A1:A))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
1

I used this method (though I am a little unclear of your exact requirement - is it the corresponding column A, or if the thing is contained, return it if it's in column A as well?):

=IF(REGEXMATCH(C2,"(?i)"&B2),A2,"No Match")

The "?i" in the regular expression is to make it case insensitive when searching as shown in the last line.

example