You could make a text copy of the lookup column and use this for the
lookup. If it's a long column you could try this: Insert a column and
format as text, copy the lookup column to the clipboard (by pressing
ctrl+C twice) then paste to the new column using the clipboard icon.


Jones the Scouse wrote:

I am using the Match Function to locate a user-entered date in an array
range that includes numbers, text and dates. This has worked fine for
3 years but I have today encountered a problem where Match erroneously
locates a number in the array that is the same value as the serial date
number of the date that is being searched for i.e. Date being searched
for 3/11/06 (d/mm/yy) but matches number 39024 (the serial date for 3rd
November 2006). The chances of this happening again are rare, but I
would like to eliminate the possibility.

Does anyone know of a way of forcing Excel to only match with dates,
rather than the serial date equivalent? I've tried changing formats of
various cells in the spreadsheets but Excel always seems to read the
date (behind the scenes) as the serial date number.