'Search for word in a phrase and return match
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 |
Solution 1:[1]
use:
=ARRAYFORMULA(IFNA(REGEXEXTRACT(B1:B, TEXTJOIN("|", 1, A1:A))))
Solution 2:[2]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | player0 |
| Solution 2 |