# 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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### 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
4
Views
265
Replies
3
Views
2K
Replies
16
Views
232
Replies
14
Views
709
Replies
7
Views
159

1,195,651
Messages
6,010,932
Members
441,574
Latest member
Prescience

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