| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why does this formula not work? | Excel Discussion (Misc queries) | |||
| How does this formula work? | Excel Discussion (Misc queries) | |||
| FORMULA DOESN'T WORK | Excel Discussion (Misc queries) | |||
| A search for $ in a formula use to work now it does not work | Excel Discussion (Misc queries) | |||
| How do I get this formula to work? | Excel Discussion (Misc queries) | |||
Linear Mode
