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 |
CodePudding user response：
use:
=ARRAYFORMULA(IFNA(REGEXEXTRACT(B1:B, TEXTJOIN("|", 1, A1:A))))
CodePudding user response：
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.
