# Adding Blank Cell Logic to Existing Formula

#### meppwc

##### Active Member
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.

=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)))

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Aryatect

##### Active Member
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:

Code:
``=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))))``

Last edited:

#### DanteAmor

##### Well-known Member
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.

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

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

#### DanteAmor

##### Well-known Member
it is correct must be yyyy then

dd/mm/yyyy;;"";@

#### meppwc

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

#### DanteAmor

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

1,102,050
Messages
5,484,411
Members
407,438
Latest member
DKrakken

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...