If value found in column A return same row value in column B

[theblade24]

I'm trying to search column A for a term.
If it is found in A41 then return B41, A194 then return B194 so return the same row the term is found in column A is used to get the result in column B if not found return "FALSE"

A B
1 Tom Jones Good Guy
2 Steve Smith Bad Guy
3 Dave Williams OK Guy

Formula should search column A for "Steve Smith", when found in A2 then return same row 2 column B value "Bad Guy" if not found return "FALSE"

Thanks in advance!

[JBeaucaire]

Try these:

=IFERROR(VLOOKUP("Steve Smith", A:B, 2, 0), "False")

=IFERROR(INDEX(B:B, MATCH("Steve Smith", A:A, 0)), "False")

[theblade24]

=IFERROR(INDEX(B:B, MATCH("Steve Smith", A:A, 0)), "False") kinda sorta works for what I'm doing, however

I need the lookup of Steve Smith in column A to be "contains Steve Smith", not an exact match to Steve Smith.

So if A2 were Buy Steve Smith a car Steve Smith is indeed contained in A2 so return B2

[FDibbins]

Add wild cards *steve smith*

[theblade24]

Well it's actually a cell and *A2* won't work. So really in that formula I need to be specifying A2 not necessarily the words "Steve Smith"


Something like =IFERROR(INDEX(B:B, MATCH(C4, A:A, 0)), "False")

So the value in C4 is searched in column A, with whatever row it finds C2 in it returns that row's value in column B

[JBeaucaire]

The wildcards would have to be concatenated in:

=IFERROR(INDEX(B:B, MATCH("*" & C4 & "*", A:A, 0)), "False")