If reference cell contains "x", return "y", otherwise return "z" I'm trying to build what seems like a dang simple formula, but for some reason it's stumping me. Here's the idea:

IF(//B4 contains "apple"//),"$100","$200")


so that it returns "$100" if B4 contains "apple", or "$200" if it doesn't.

I can't figure out the function to use in the //B4 contains "apple"// part. I tried this, but it didn't work:

IF(LOOKUP("apple",B4),"$100","$200")


What am I missing?
Try changing your LOOKUP function to EXACT, so that your formula would read

IF(EXACT("apple",B4),"$100","$200")


or

IF(B4="apple", 100, 200)


for a case insensitive solution, or 

IF(TRIM(B4)="apple",100,200)


for a solution which will work even when there's whitespace around the word in the cell (leading/trailing spaces, etc.).



My Numbers is set to use ; instead of , as delimiters, so my test sample reads IF(EXACT("Apple";B4); 100; 200)