Vlookup date problems

January 19, 2010 at 11:07:07
Specs: Windows XP

I have really struggled to find a solution on the net but to no avial.
I have a date column [spreadsheet B]with the date linked to another spreadsheet A. The date format in both are dd/mm/yyy.
In the spreadsheet B i have two column column S with for excample 01-Jan, 01-Feb, 01-Mar, 01-Apr and next to each a text value I want to lookup using the date column of the spreadsheet B
I have tried al the problem solving answers on the Net but always get a #N/A or #value!
Please help

See More: Vlookup date problems

Report •


#1
January 19, 2010 at 11:29:26

Hi,
One of the problems with looking up and matching dates is that Excel stores times in the same variable as the date.

If some of your date values also contain times, there will not be a match.

Two cells formatted as dd/mm/yy may show 10/Jan/10
but one of them is midnight and the other is noon - you can't tell by looking at the date formatted cell.
10/Jan/2010 at midnight is 40198.00
10/Jan/2010 at noon is 40198.50

Try formating your dates as numbers and see if there are any decimal values 'hidden' in the cells.

If the values in the cells are not around 40000 (assuming recent dates), but still show as a date, then Excel hasn't recognized the cell as a date. So some cells are text that is in a date style, while others are numbers recognized by Excel as dates and showing as a date due to formatting. So again - no match.

One way to handle the time issue is to compare the integer parts of the numbers only.

Create a new column to the right of your dates, before the text and use a formula =int(A1), to ensure that you get only the integer (whole number) part. Do your lookup on this new column using =VLOOKUP(INT(A9),B!$A$2:$B$20,2,FALSE), so you compare the integer part of the date in cell A9 with the integer values in your new column.

Regards


Report •

#2
January 19, 2010 at 11:33:03

I'm a bit confused by the description of your layout...

re: "I have a date column [spreadsheet B] with the date linked to another spreadsheet A."

What do you mean by "with the date linked to another spreadsheet A."? How does that enter into what you are trying to do?

re: In the spreadsheet B i have two column column S with for excample 01-Jan, 01-Feb, 01-Mar, 01-Apr

Are these values actual dates with years associated with them?

The reason I ask is that the date format in the cells shouldn't matter to VLOOKUP.

e.g. if A1 is formatted to display 1/1/2010 and S1 is formatted to display 1-Jan but actually contains 1/1/2010, VLOOKUP will find it since all dates are stored as numerical values within Excel itself.


Report •

#3
January 19, 2010 at 11:53:50

Thanks a lot for rerplying so fast.
Spreadsheet A - column D [example]
In this spreadsheet are the birthdates of learners formatted as dd/mm/yyy [and a lot of other information]
In spreadsheet B which is linked to spreadsheet A the date of the learner also appears in column D and only tehir names and surnames. In this spreadsheet I want to use the birthdate and wrote [for example] next to their name the team they belong to. I want to use a lookup or similar function to lookup the team they belong to, using their birthdate, from the following two columns
Date Team
Al those in January Blue team
All those in February White team
All those in March Blue team
All those in April White team etc.

As I have mentioned I have tried a lot of the problem solving answers but still get error messages

Ho[e this explain my problem better?
Thanks again for your speedy reaction

Kind regards

Myburgh


Report •

Related Solutions

#4
January 19, 2010 at 13:14:34

There are numerous ways to do what you are asking, everything from an IF to a VLOOKUP.

For example, based on your example, with a Birthday in D1, you could use something like this:

=IF(MOD(MONTH(D1),2)<>0,"Blue","White")

All the ODD months would return Blue, all the EVEN months would return White.

To use VLOOKUP on just the Month of a date, you could add a column before the dates in the lookup_array and look that up.

For example:


      C            D          E          F            G
1   Month        Date       Name       Surname       Team
2  =MONTH(D2)   1/2/2010    Xxx          Yyy         Blue
3  =MONTH(D3)   2/5/2010    Aaa          Bbb         White

With a date in A1, use this to return a Team color:

=VLOOKUP(MONTH(A1),$C$2:$G$3,5,0)

You could Hide column C if you don't want to see the Month Column.

I hope that helps.


Report •

#5
January 19, 2010 at 21:58:26

To DerbyDad03

Thank you for your reply

The If function works 100% for two teams - but I forgot to mentioned in my example that there could be 3 or more teams.
For example how will this function change if for example month 1,4, 7, 10 is the Blue team, months 2, 5, 8, 11 is the White team and months 3, 6, 8 ,12 is the Gold team.

The vlookup function still gives a N/A error. I just got a problem with this becauese where does one make the lookup table so that the function can lookup the month to the corresponding team name?

I appreciate your input and help with this and hope I do not have to bother you anymore

Kind regards


Report •

#6
January 19, 2010 at 23:46:46

To DerbyDad03 and Hunar

Thank you for your input

Did not know why but this morning everthing seems to go 100%.

Used the =MONTH(D30 function to deetermine the month from the birthdate and then used =vlookup(month, lookupArray) and it is working perfectly. Do not know wgat happen ysterday because the same function did not work

Anyway besides this Thanks for your time and effort

Kind regards


Report •

#7
January 20, 2010 at 06:09:16

Just an FYI...

If there will always be a date in D30, the IF function would be:

=IF(OR(MONTH(D30)=1,MONTH(D30)=4,MONTH(D30)=7,MONTH(D30)=10),"Blue",IF(OR(MONTH(D30)=2,MONTH(D30)=5,MONTH(D30)=8,MONTH(D30)=11),"White","Gold"))

This function checks for the Months associated with Blue and White and defaults to Gold if one of them isn't found.


Report •


Ask Question