If(and statement - 2 conditions - 3 outcomes

[JoeRooney]

Hi,

I have the below formula and can’t figure out why it returnsa #N/a instead of the “Policy Not Found”

The "Policy found" and “” are working fine the only problem iswith the false value coming up as #N/a instead of "Policy Not Found"



Any help is greatly appreciated.

=IF(AND(A2="",B2=""),"",IF((A2=B2),"PolicyFound","Policy Not Found"))


Thanks
Joe

 

[Fluff]

Does either A2 or B2 contain #N/A

 

[JoeRooney]

Hi,

Yes it does, now I have another problem, I have amended the columnthat it is looking at (code below) so I no longer receive N/A

=IFNA(VLOOKUP(A2,TWG_File!A:AD,1,0),"Not found")

Is it working except it is returning not found for the blankfields, so is it possible to return

The value
Not found
“” for blanks?

Any help is greatly appreciated.

 

[excel_learnerz]

Hi,
I am not sure I understand your question, are you asking that if the vlookip doesnt get a match you want the value to be blank or "Not Found"

 

[JoeRooney]

Hi,

Sorry no so if there is a match I want it to return thevalue

If it doesn’t match I want it to return “Not found”

Or if both cells are blank I want it to return a blank,


Example below, instead of Not found in the 3^rd rowI would like it to return a blank

Hope that makes sense.

[TABLE="width: 124"]
<tbody>[TR]
[TD="width: 84"] policy_id
[/TD]
[TD="width: 90"] Policy Number
[/TD]
[/TR]
[TR]
[TD="width: 84"] 1111111111
[/TD]
[TD="width: 90"] 1111111111
[/TD]
[/TR]
[TR]
[TD="width: 84"] 2222222222
[/TD]
[TD="width: 90"] Not found
[/TD]
[/TR]
[TR]
[TD="width: 84"]
[/TD]
[TD="width: 90"] Not found

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Finalfight40]

Hi,

Yes it does, now I have another problem, I have amended the columnthat it is looking at (code below) so I no longer receive N/A

=IFNA(VLOOKUP(A2,TWG_File!A:AD,1,0),"Not found")

Is it working except it is returning not found for the blankfields, so is it possible to return

The value
Not found
“” for blanks?

Any help is greatly appreciated.

Something like this?

=IFNA(IF(ISBLANK(A2), "", VLOOKUP(A2,TWG_File!A:AD,1,0)),"Not found")

 

[JoeRooney]

Perfect , Thank you.