IF and AND not working correctly

**silv** · 02-22-2010, 09:58 AM

Afternoon guys,
I work in a hospital and we're trying to relate one spreadsheet to another in order to deduct whether a patient already has a record. To do this im searching for a match of surname and date of birth.

I created a small model to test the IF(AND forumla on before I implemented it into general use, I am having one problem though the AND statement seems to be working more like an OR statement.

If you could help me out i'd appreciate it greatly!

ive attached a screenshot just to show, the values in A1 and G1 are the values being searched for, if both are in the corresponding lists im trying to get HAF to be displayed, otherwise NOT HAF. Currently if one matches then HAF will be shown, but I need both criteia to be fulfilled.

Thanks for your time, I greatly appreciate any efforts to help me out this tight spot!
Cheers Jack

the forumla i currently have is: =IF(AND(ISNA(VLOOKUP(A1,$B$1:$B$20,1,FALSE)),(ISNA(VLOOKUP(G1,$H$1:$H$20,1,FALSE)))),E1,E2)

(All data in the screenshot is fictional)

**NBVC** · 02-22-2010, 10:04 AM

Your formula should work and give the right result...

If you press F9, does it work?

Also, always best to post actual spreadsheet sample, not jpg... so we can diagnose.

**silv** · 02-22-2010, 10:56 AM

Nope F9 didnt seem to have any effect,
Heres the spreadsheet im working on as an example, thanks again for your time
Jack

**NBVC** · 02-22-2010, 10:59 AM

Try:

=IF(AND(ISNUMBER(MATCH(A1,$B$1:$B$20,FALSE)),(ISNUMBER(MATCH(G1,$H$1:$H$20,FALSE)))),E2,E1)

**martindwilson** · 02-22-2010, 11:06 AM

then you should be using OR ie if either is false then give not haf if both true give haf?

**6StringJazzer** · 02-22-2010, 11:49 AM

Originally Posted by martindwilson

then you should be using OR ie if either is false then give not haf if both true give haf?

I agree with this assessment. Your logic is wrong. The function as you wrote it does exactly what it's supposed to do.

However! Setting aside the function for a moment, it seems to me from your description that you want to make sure that there is a match for both surname and date of birth together. That is, if you are looking for Jake born on 4/12/78, then your approach as it is now could find some other Jake born on 5/6/59, and also Fred born on 4/12/78, then conclude that the Jake you are looking for already has a record, when in fact he does not. Because your sample data does not seem to have a one-to-one correspondence between patient names and their birthdays, your data may not be organized in such a way as to be able to do this.