HI,

I'm trying to write a VLOOKUP formula =VLOOKUP(B15,D2:Q12,2,FALSE) where B15 is a date (eg =DATE(2006,2,17)) and it is meant to look for that date in the first row of the array. I'm constantly getting #N/A. Cany anyone help?

Thanks,
TracerB

Your value in B15 is a formula instead of a value.

Try entering your dates column with simply the data as 2006,2,17 and then your VLOOKUP will find it as a text string.

Otherwise, I am using Excel 97 and find your date format as not a vaild format for a date. It correctly sees it as a text string. (Perhaps in later versions of Excel your format is a valid date format?)

If you use a date format such as 2/17/2006 and use that as the lookup value then the function will convert your dates into numeric values and find it by the same value. eg 2006,2,17 as 2/17/2006 converts to 38765.

HTH
Bryan

Thanks Bryan.

The reason I was using =DATE was I had originally used the text string of 2/17/2006 to lookup up the array and match 2/17/2006 but that didn't work either. Still returns #N/A. Any other ideas?

Thanks,
TracerB

If you do not apply any specific format to the cells or column then by default, when you enter a date, Excel will read it as a date. It uses General format.
eg. If I enter "2/17/06" and hit enter it will change to 2/17/2006 because that is how I have Windows set up to display dates.
But it is a date.
Don't enter an equal (=) symbol, just type in the date.

Otherwise, any way that I tried, my VLOOKUP finds what I am looking for on the spreadsheet I made just for the testing of your query.

You column containing the dates are in column D, correct?

Bryan

Thanks Bryan. Seeing as forcing it to General and starting again fixed it. Must have been something a bit screwy with the formatting on the field.

Cheers for the idea. One I should have thought of myself :) Sometimes you always look for the harder problem first.

Thanks,
TracerB