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