Microsoft Book: office excel 2003 inside...

I am trying to use the vlookup formula with dates. I want the formula to lookup the date of birth & give me the corresponding star sign from the table that i assign the dates for.I want the formula to only look up the date & month.

How do you have your data setup? It's my understand that your astrological sign can span several weeks.

IE; Libra is September 23 - October 22

MIKE

Hi,

As Vlookup will only find single values, not ranges, create a 366 day table, with the date in the first column and the sign in the second column.An easy way to do this is to put 01-January-1908 in cell C1

Then in cell C2 put the formula =C1+1

Drag the formula from cell C2 down to cell C366 and format the cells in a date format using day and month only (eg use custom format "dd-mmm").Cell C366 should have the following formula =C365+1 and show 31-Dec

I started with 1908 as this was a leap year - use any year that is a leap year but not 1900 as there is an error in Excel.

Now add the signs in column D alongside the appropriate dates.

Enter a date of birth in cell A1, using a format Excel recognizes as a Date, and format A1 as a full date including year.

As star sign ignores year, the formula to use, must take the day and month from the birthdate and then add the year you used in column C.

This new date is the value Vlookup will use.

In Cell B1 put the following formula

=VLOOKUP(DATEVALUE(DAY(A1)&"-"&MONTH(A1)&"-1908"),C1:D366,2)C1:D366 is your table of dates and signs

Datevalue is a function to create a number that Excel uses for storing dates, and which Vlookup can use for the match.

The Day and Month come from cell A1 and the year is hardcoded.

2 is the column offset for the signs (dates in column 1 and signs in column 2)You could use =VLOOKUP(DATEVALUE(DAY(A1)&"-"&MONTH(A1)&"-"&YEAR(C1)),C1:D366,2) with the year being taken from the first date in your table.

The sign will now be shown in Cell B1 for any date entered in A1

Hope that works for you

re: Vlookup will only find single values, not rangesI guess that depends on how you look at the situation.

Please keep in mind that the OP has not told us how his data is laid out, so I'm just going to discuss another way that VLOOKUP can be used to return a value based on a range of values.

As per the Help files regarding VLOOKUP's

range_lookupargument:"If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned."

We don't need VLOOKUP to

returna range, we just need it to find the range that thelookup_valueresides in and return the value associated with that range.Let me explain that in terms of the astrological signs...

The tropical duration of Pisces is February 20 to March 20.

So if I want VLOOKUP to return Pisces, it has find any date in the range 20-Feb through 20-March.

So I set up a table that shows the start date for each sign and the sign itself:

A B 1 01-Jan Capricorn 2 20-Jan Aquarius 3 20-Feb Pisces 4 21-Mar Aries 5 21-Apr Taurus 6 21-May Gemini 7 22-Jun Cancer 8 23-Jul Leo 9 24-Aug Virgo 10 23-Sep Libra 11 24-Oct Scorpio 12 22-Nov Sagittarius 13 23-Dec CapricornNow if I put a date in C1, I can use:

=VLOOKUP(C1,$A$1:$B$13,2,1)

This will return the sign for either the exact date in C1 or, if it's not found, the sign for the range that is between the date in C1 and the start date of the

nextsign.Now, if you look carefully at the table, you will see that Capricorn is listed twice. This is a little liberty I took to cover the dates in early January. It's my data table, so I can set it up any way I want!

Now, the last requirement is that the OP wanted VLOOKUP to only look up the month and day. Well, the data table with the signs will presumably have a year associated with the dates, e.g. 22-Nov-2009

However the date that we are looking up might have a different year, e.g. 22-Nov-2008.

So let's ignore the year of the

lookup_valuein C1 and use the year of the dates in thetable_array:=VLOOKUP(DATE(YEAR($A$1),MONTH(C1),DAY(C1)),$A$1:$B$13,2,1)

That way, it won't matter what year is used in C1 because we're not going to look that one up, instead, we're going to look up the year of the date used in the

table_array.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History