Thank you all for the help in advance. If I want to look for the word "Rover" Embeded in a cell and i want to return the word "Dog" but if the cell has the word "Fluffy" I want to return "Cat" I cannot get the nesting syntax on this one to work.. or perhaps im doing it completely wrong.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Nestedm, return a result if a cell hase a certain text embeded in it
- Thread starter DeaninGA
- Start date
Eric W
MrExcel MVP
- Joined
- Aug 18, 2015
- Messages
- 12,980
Try:
If you only have 2 words, then the C2 formula should work. If you have more, I'd suggest making a table and using the B2 formula.
| Book1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Phrase | Word | Code | |||||
| 2 | Run, Rover, Run! | Dog | Dog | Rover | Dog | |||
| 3 | Fluffy likes milk. | Cat | Cat | Fluffy | Cat | |||
| 4 | Mickey is a mouse. | |||||||
Sheet5 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B4 | B2 | =IFERROR(LOOKUP(2,1/SEARCH($E$2:$E$3,A2),$F$2:$F$3),"") |
| C2:C4 | C2 | =IF(ISNUMBER(SEARCH("Rover",A2)),"Dog",IF(ISNUMBER(SEARCH("Fluffy",A2)),"Cat","")) |
If you only have 2 words, then the C2 formula should work. If you have more, I'd suggest making a table and using the B2 formula.
Upvote
0
Similar threads
- Question
