Adding Blank Cell Logic to Existing Formula

[meppwc]

The formula below that is in W247 works correctly. You will notice if the lookup finds a value (the secondary VLOOKUP logis), that the formula returns the value in column 9.
But if the value for column 9 is blank, then I want blank returned.
Currently Excel is populating the value of "1/0/00"
I am struggling with fitting an ISBLANK condition into this formula.
Can someone please assist?


=IF(D247<>"",D247,IF(ISERROR(VLOOKUP(O247,'G-Block CMO-FMO and IP Ranges '!$A$61:$I$400,7,FALSE)),"",VLOOKUP(O247,'G-Block CMO-FMO and IP Ranges '!$A$61:$I$400,9,FALSE)))

 

[Aryatect]

Hi, this is returning value 0 for you and it is in date format m/d/yy.

If you want to change that then without going into too much modification this should work:

=IF(D247<>"",D247,IF(ISERROR(VLOOKUP(O247,'G-Block CMO-FMO and IP Ranges'!$A$61:$I$400,7,FALSE)),"",IF(VLOOKUP(O247,'G-Block CMO-FMO and IP Ranges'!$A$61:$I$400,9,FALSE)=0,"",VLOOKUP(O247,'G-Block CMO-FMO and IP Ranges'!$A$61:$I$400,9,FALSE))))

 

[DanteAmor]

The formula below that is in W247 works correctly. You will notice if the lookup finds a value (the secondary VLOOKUP logis), that the formula returns the value in column 9.
But if the value for column 9 is blank, then I want blank returned.
Currently Excel is populating the value of "1/0/00"
I am struggling with fitting an ISBLANK condition into this formula.
Can someone please assist?

Apparently you have a date format in the cell.
Change the format of the cell to:

dd/mm/aaaa;;"";@

https://support.microsoft.com/en-us...stand-settings-in-the-format-cells-dialog-box

 

[Aryatect]

You can also change the format of Column W to m/d/yy;"";

 

[DanteAmor]

it is correct must be yyyy then

dd/mm/yyyy;;"";@

 

[meppwc]

Thank you all for the assistance.........DanteAmor.........dd/mm/aaaa;;"";@ worked perfectly. It really "cleaned" things up.

 

[DanteAmor]

Thank you all for the assistance.........DanteAmor.........dd/mm/aaaa;;"";@ worked perfectly. It really "cleaned" things up.

I'm glad to help you. Thanks for the feedback.