Date matching using VLOOKUP

February 27, 2006 at 13:39:56
Specs: Win XP SP2, Intel P4 3.0/1 GB

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?


See More: Date matching using VLOOKUP

Report •

February 27, 2006 at 16:37:06
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.


Report •

February 27, 2006 at 22:40:41
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?


Report •

February 28, 2006 at 03:49:28
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?


Report •

Related Solutions

February 28, 2006 at 16:51:46
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.


Report •

Ask Question