excel Vlookup

Microsoft Book: office excel 2003 inside...
August 24, 2009 at 15:03:58
Specs: Windows Vista
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.

See More: excel Vlookup

Report •

August 24, 2009 at 16:28:00
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



Report •

September 8, 2009 at 15:24:57
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

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

Report •

September 8, 2009 at 19:06:01
re: Vlookup will only find single values, not ranges

I 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_lookup argument:

"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 return a range, we just need it to find the range that the lookup_value resides 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     Capricorn

Now if I put a date in C1, I can use:


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 next sign.

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_value in C1 and use the year of the dates in the table_array:


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.

Report •

Related Solutions

Ask Question