DATE column should not shows blank with VLOOKUP formula

Naga

On Sheet1, VLOOKUP returns a blank value if there's no date. When that is converted to a dd-mm-yy date by the TEXT function it seems to convert the blank to 0 which, in Excel date numbering, is equivalent to one less than the start of that numbering scheme, 01 January 1990 (value 1) . That is then displayed as 00-01-90 following your dd-mm-yy formatting.

To prevent that, I suggest you ignore the TEXT function and instead set the formatting of your cells to dd-mm-yy (or "dd/mm/yy") and use an IF function and ISBLANK to check if the VLOOKUP returns a blank (and if so show the null string "") or if not, show the non-blank result of the VLOOKUP:

=IF(ISBLANK(VLOOKUP(D1,A4:C6,3,FALSE)),"",VLOOKUP(D1,A4:C6,3,FALSE))

I've done that in the revised file attached,.

On your sheet 2 and your VLOOKUP on short number, your range should NOT include the headings in row 3 but the main issue is that your column C has a formula:

=Right(B3,3)

 which produces a string. Cell F1 however has General formatting so if you enter 456 say, Excel assumes it is an number and VLOOKUP can't find it since it's comparing a number with a string (even though there's a string 456 which looks the same to us!). It then displays an error code #N/A.

There are two easy solutions however. Either format the search cell F1 as Text  (then entering 456 will produce a string which can be found in column C) or leave F1 with General formatting and convert the cells in column C to numbers by wrapping the Value function around the existing formula, e.g. for C3.: 

=VALUE(RIGHT(B3,3))

In the revised file attached, I've used the latter method.

Hope this helps

Please check the file.