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

Hi,

One of the problems with looking up and matching dates is that Excel storestimesin 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.50Try 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

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-AprAre 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.

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 reactionKind regards

Myburgh

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_arrayand 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 WhiteWith 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.

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

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

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.

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History