Why does this formula not work?
 

....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

Why does this formula not work?
 

Try this revision:
=IF($P30="","Y",IF(AND($W30<"",ISNUMBER(MATCH($W3 0,SAGE3,0))),"Y",""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DaveMoore" wrote:
....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

Why does this formula not work?
 

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

On 27 Feb, 08:27, Max wrote:
Try this revision:
=IF($P30="","Y",IF(AND($W30<"",ISNUMBER(MATCH($W3 0,SAGE3,0))),"Y",""))
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---



"DaveMoore" wrote:
....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- Hide quoted text -

- Show quoted text -

Why does this formula not work?
 

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Why does this formula not work?
 

Glad it helped, Dave !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Feb 27, 5:32 pm, "DaveMoore" wrote:
Oh Max!
What can I say?
Brilliant!
Thank you so much.
Dave Moore