In Excel, in one cell, how can I check to see if a specific character string is in an adjacent column?

Question

For example, if I have a column like this:
.......A.............B..............C
1 Apple
2 Banana
3 Orange

What formula can I write in cell B1 that will return a "1" if "Banana" is in column A, and a "2" if it is not.

Many thanks.

Answer 1

=IF(ISERROR (VLOOKUP("Banana", A:A,1,FALSE)),2,1)

If the VLOOKUP function can't find the exact text "Banana" (no spaces or extra words or characters) in a cell in column A, the VLOOKUP function will report and error. This will make the ISERROR function report TRUE, so the IF function will use the first value: 2 in the cell. If the VLOOKUP function does find "Banana" then it will not report an error, so ISERROR will report FALSE. In that case, the IF function will use the second value: 1 in the cell.

Answer 2

Here's the formula with "Banana" inside the formula

=if (ISERROR (FIND ("Banana", A1)), "", IF(FIND ("Banana", A1)>0, 1, ""))

Don't worry about embedded spaces in above. When you paste in the formula Excel will give you an error and recommend a fix. Just accept the fix and you'll be fine.

Otherwise, rather than put a specific word into the formula, you can replace "Banana" with a reference to a cell. Use the $ (dollar sign) to make the cell not-relative if you want to reference a single cell.

=IF (ISERROR (FIND ($C$1, A1)), "", IF (FIND ($C$1, A1)>0, 1, ""))

This way you can enter a single word in a cell and then all the rows below will change to "1" if it matches.

Hope this helps,
Kind Regards,
QwertKPH @ Yahoo!

Answer 3

check out the find() function