Why does this formula not work?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DaveMoore - 27 Feb 2007 07:40 GMT

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

Reply to this Message

Max - 27 Feb 2007 08:27 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> ....and how should it be written?
>
[quoted text clipped - 9 lines]
>
> Dave Moore

Reply to this Message

DaveMoore - 27 Feb 2007 09:32 GMT

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

> Try this revision:
> =IF($P30="","Y",IF(AND($W30<>"",ISNUMBER(MATCH($W30,SAGE3,0))),"Y",""))
[quoted text clipped - 19 lines]
>
> - Show quoted text -

Reply to this Message

Max - 27 Feb 2007 10:02 GMT

Glad it helped, Dave !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Reply to this Message

Max - 27 Feb 2007 09:34 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message