Formula (IF) recommendation for mis matched columns

[techspec]

Hi All
need a little help
i am cleaning up some xls'. im not sure if i am on the right path however
could anyone reccomend a fix for the following

i have names from one sheet in coulum
A
i have names from another sheet in column
C
i have their emails in column
D

A and C are from diffferent sheets/data and do not match, so i cant sort them.
i would like to populate B with emails from D IF they are present or match ( A and C)
like i said i am not a hundred percent sure i am on the right track, but i have washed the data so the names in A and C are the same.
i have used the IF formula to work a little however i would like it to search the entire column not just the one row
id like a more expansive if than =IF(a1=c1 thend1) < i think that's how i used it.
i tried something like =IF(A1:A65536 = C1:C65536 then D1:D65536) but it didnt work...
also, could i use a string within the IF statment so if there is a match within a certain parameter it would apply. eg name match of x percent. this isnt as important.
sorry if this doesnt make much sense im not to sure myself

[Robert Tucker]

If you used: =IF(A1:A65536=C1:C65536;D1:D65536;"No Match") as an array formula (entered with Ctrl+Shift+Enter) then you would get an array 65536 rows deep with either an appropriate entry from the D column or the words "No Match".

Or you could just put =IF(A1=C1;D1;"No Match") (as an ordinary function – entered with just the tick sign or Enter) and then fill down.

You may need to clarify the second question or better still open a new thread for it.

[techspec]

tried both IF statements.
no luck i'm afraid, but then again i could be implementing it wrong
as soon as it gets out of synch formula fails.
eg


bob(formula returns email) bob email
tom (formula returns email) tom email
harry (formula does not email) garry email
garry (formula does not email) harry email

even though there is a name mismatch, the names are in both columns, if i can get that formula returning on a match on both columns, it'd be a small victory.

[Robert Tucker]

If I understand you correctly this is going to be quite complicated if possible at all.

First it has to acquire how many (presumably adjacent) letters it is going to need to look at, then it needs to take the first group of this number of characters and see if there is a string match within the cell with which it is comparing the first cell. Then it needs to move the group along one character in the string in the first cell and repeat the process until the last character of the group corresponds with the last character of the string in the cell.

I don't think there are any functions which will facilitate this well – perhaps some array function, but it is a bit beyond me. A macro might be simpler solution.

[TessaES]

Maybe I'm missing something, but it sounds like you need the VLOOKUP-function:
=VLOOKUP(A1;$C$1:$D$65536;2;0)

This finds the value in column C that matches column A and then returns the entry in column D