# VLOOKUP date without #N/A & 00/01/1900

#### Gwill1983

##### Board Regular
Hi all,

Really struggling with this one.

I am carrying out a vlookup against cells containing a date. I have established a way to do this to avoid returning #N/A for errors, but this then returns 00/01/1900 for cells containing no data. If I adjust the formula to get rid of the 00/01/1900 then the #N/A appear again!!

The formula I have for removing the #N/A is =IFERROR(VLOOKUP([@[PO Number]],Prev_Report,9,0),"")
The formula I have for removing the 00/01/1900 is =IF(VLOOKUP([@[PO Number]],Prev_Report,9,0)=0,"",VLOOKUP([@[PO Number]],Prev_Report,9,0))

Is there a way of combining these in some way to avoid both?

I only want it to return a result if there is a date in the resulting cell

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Sufiyan97

##### Well-known Member
Is VLOOKUP result in Date Format?

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=IFERROR(1/(1/VLOOKUP([@[PO Number]],Prev_Report,9,0)),"")``

#### Gwill1983

##### Board Regular
Is VLOOKUP result in Date Format?
Yep. I idiot checked that just before I posted

#### Gwill1983

##### Board Regular

Excel Formula:
``=IFERROR(1/(1/VLOOKUP([@[PO Number]],Prev_Report,9,0)),"")``
That doesn't return anything now! It did fix the N#A's and the stupid date though!!

#### Fluff

##### MrExcel MVP, Moderator
Are you sure the values the vlookup returns are numbers & not text?

#### Gwill1983

##### Board Regular

Are you sure the values the vlookup returns are numbers & not text?
100% positive. Both the column containing the VLOOKUP and the column with the returns (column 9 within the prev_report table) are set to short date

#### Fluff

##### MrExcel MVP, Moderator
If you format col 9 of the prev_report table what do you see?

#### Gwill1983

##### Board Regular
If you format col 9 of the prev_report table what do you see?

#### Fluff

##### MrExcel MVP, Moderator
Oops should have said
If you format col 9 of the prev_report table as General what do you see?

Replies
3
Views
576
Replies
3
Views
122
Replies
15
Views
507
Replies
3
Views
118
Replies
5
Views
195

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,586
Messages
5,765,298
Members
425,271
Latest member
kristyfinn

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back