Excel: Date format is throwing up random numbers when using a function

Question

I am using a INDEX, MATCH function in sheet Car Search to pull data from sheet Raw Data. One of the cells is Date Purchased, this will show the date in format dd/mm/yyyy (UK Format).

The problem is, when I enter a date in this format, it doesn't correctly pull over to the Car Search sheet. Instead, it shows up 42350 when I entered 12/12/2015 in the Raw Data sheet. Both cells have been formatted to Date, but it is still showing up as 42350 and not 12/12/2015.

Is it because the function is preventing it from showing date format? Anybody know why Excel does this? I have tried on a new sheet, but same result.

Any help much appreciated :)

Please see images below.

Answer 1

if you're still not getting it after you change the format of the cell, you can surround the index/match formula you've written with =text(b4, "mm/dd/yyyy") which will format the 5 digit number you've gotten as a date.