Why does this formula not work?

[DaveMoore]

....and how should it be written?

=IF($P30="","Y",IF(AND($W30<>"",VLOOKUP($W30,SAGE3,1,FALSE)=
$W30),"Y",""))

If the value of cell W30 is also present in the table SAGE3 there is
no problem. The error occurs if it is not.
I have tried changing FALSE to TRUE but this does not work either.
The table SAGE£ has been sorted by the first field.

Can anyone help?

Dave Moore

 

[Guest]

Try this revision:
=IF($P30="","Y",IF(AND($W30<>"",ISNUMBER(MATCH($W30,SAGE3,0))),"Y",""))

 

[DaveMoore]

Oh Max!
What can I say?
Brilliant!
Thank you so much.
Dave Moore

 

[Guest]

You had a prob earlier because of this part:

VLOOKUP($W30,SAGE3,1,FALSE)=$W30
which returns #N/A if W30 is blank or contained a value not found in the
lookup col

The #N/A is not resolvable by the AND

---

 

[Max]

Glad it helped, Dave !