Why does this formula not work?

D

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
 
M

Max

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

Max

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
 
Top