Compare 2 text fields

[Darkglasses]

Hi All,

I have 2 lists of names seperated into Suranme and First name which I need to compare.I have tried a variety of formulas and googling the problem which I thought I have fixed but discover a whole in my logic that meant the result where not accurate. Bascially I need some help!

First list of names:
A4:A255 - Surname
B4:B255 - First names

Second list of names is broken down into groups
First groups is:
E5:E12 - Surname
F5:F12 - First name

Here is the lastest attempt;

=IF(AND(EXACT(E5,$A$4:$A255),EXACT(F5, $B$4:$B$255)),"Member","Non Member")

I need both field to match and give a member output. Previously I had broked it down so my array compared all fields correctly but returned "member" in error if one persons first name was Mike and someone elses name was Davies.

Basically, how to compare one person (E/F5, E/F6, E/F7, etc) is present on my first list ?

 

[Andrew Poulsom]

Try:

=IF(SUMPRODUCT(--($A$4:$A255=E5),--($B$4:$B$255=F5)),"Member","Non Member")

 

[Darkglasses]

I was coming back to post my progress and saw your post Andrew. Thankyou so much as you have nailed my problem.

I have never seen this formula before and realise just how much I dont know about excel, need to kich this around a bit more. i appreciate you taking the time to respond as I have been battling with this spreadsheet over the past few week.

=IF(EXACT(E5,$A$4:$A$255),"1","0")

I think I was going wrong somewhere here as this formual was working but seems not to be now. I use some test data I knew was in my original list and ensure I used the array with ctrl+shift+enter but no joy. If i figure it out i will post up and help those as I have been.