IF a cell contains a word
PremiumIn B2, show "Pro" if the product name in A2 contains "pro" anywhere, and "Standard" if it does not. IF has no wildcards, so test with ISNUMBER(SEARCH("pro",A2)).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Line | ||||
| 2 | Laptop PRO 14 | |||||
| 3 | Wireless mouse | |||||
| 4 | Monitor Pro 27 | |||||
| 5 | Keyboard | |||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock IF and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel assessment (Indeed format)
Why this works
SEARCH("pro",A2) returns 8, where "PRO" starts in "Laptop PRO 14", and #VALUE! when the word is not there. ISNUMBER turns that into TRUE or FALSE, which is exactly what IF needs as its test. Filled down, the mouse and the keyboard show Standard.
Common mistake: Writing IF(A2="*pro*",…). IF compares the whole cell with those exact characters, so it never matches.
The answer
- B2
=IF(1ISNUMBER(SEARCH("pro",A2)),2"Pro",3"Standard")
In plain words: 1If the product name in A2 contains pro, 2show Pro. 3Otherwise show Standard.