How to do like a vlookup between two lists, one with partial strings the other with full strings

[Anarion]

Hi!

I'm trying to do something similar to a vlookup but a little more complicated. I have two lists, one full of addresses and the other has only a partial strings.

What I need to do is see if the addresses in column A (original addresses) contain strings from column b (neighborhood strings) and return a value of true or false in column C.

For example,

Column A has:

(A1) Villa Fontana Park
(A2) Urb Villa Fontana
(A3) Villa Fontana
(A4) Urb Alts de Fontana

Column B has "Villa Fontana" somewhere within a range of 711 rows.

Column C should:

Return (A1) as true
Return (A2) as true
Return (A3) as true
Return (A4) as false

no matter what I do I can't seem to make it work, so far I've tried

=if(isna(match(etc...
=iferror(vlookup(etc...
=find(etc...

and a bunch others, can anyone please enlighten me in this? I've reached a dead end and I'm driving myself crazy x_@ Thanks in advance!

 

[JPM]

Anarion,
your problem is that you are really trying to do 2 things at the same time without exactly idenfifying the problem for Excel.

The way you stated the problem, the first formula should be a vlookup that tells you if anywhere in column B there is a cell with "Villa Fontana". If that is the case, then you want to know if "Villa Fontana" is also found within the corresponding cell in column A.

The following formula (to be placed in C1) would acomplish this:
=IF(ISERROR(VLOOKUP("Villa Fontana",$B$1:$B$711,1,FALSE))=TRUE,"False",IF(ISERROR(FIND("Villa Fontana",A1)=TRUE),"False","True"))

However, I would be surprised if this is actually what you are really looking for. If it is, I am glad, otherwise, post again.