Zodiac sign lookup
To look up one of the 12 Western Zodiac signs based on a birthdate, you can use a formula based on INDEX and MATCH. The formula in H7 is:
where dob (H5), sign (B5:B16), symbol (D5:D16), start (E5:E15), and end (F5:F16) are named ranges. Using the lookup table as shown is somewhat challenging. See below for an alternative self-contained formula.
The goal of this example is to look up the correct astrological or zodiac sign for a given birthdate, using the table shown in B5:F15. These are based on the Western zodiac signs described here. Using the lookup table as shown is somewhat challenging. This article describes an INDEX and MATCH formula that uses the table as shown, and alternative formula based on VLOOKUP that uses a hard-coded array constant.
For this example, I was determined to use a formula to look up a zodiac sign in an existing table (as shown) that lists each Zodiac sign along with a symbol, start, and end dates. This is a challenging problem for a couple reasons. First, the start and end dates are not actual dates, but are instead "date fragments". Second, because the zodiac signs start in March and cross over a year boundary, they are not listed in chronological order. If they were real dates in chronological order, we could use use a standard INDEX and MATCH formula set up for an approximate match on the start date only. Instead, we need configure MATCH with Boolean logic to look to locate dates that fall between two dates. In addition, we need to assemble the date fragments into actual dates, using the year from the given birthdate.
This approach works for all zodiac signs except one, Capricorn, which crosses the new year boundary, and means the "between" logic will fail and never return TRUE. We could easily solve this problem by splitting the Capricorn entry into two rows with separate date ranges – one from December 22 to December 31, one for January 1 to January 19. But I didn't want to do that, since it would mess up the clean, human-readable lookup table.
I tried a number of tricky options to work around this problem, including shifting all dates back 3 months so they fit into one year. But the complexity of these approaches bothered me (and made the formula more opaque), so in the end, I cheated by catching the #N/A error returned by MATCH in the Capricorn date range, and returning row 10, hardcoded. In other words, if a date throws an error, it must be Capricorn.
This should work fine when a valid birthdate is provided. But it also means that any invalid date will also return Capricorn – if your birthday is "apple", you are Capricorn :) See below for a sane alternative that doesn't use the table as shown.
INDEX and MATCH with table as shown
The goal is to look up the correct zodiac sign for a given birthdate, using the existing table as shown (B5:B16). The worksheet includes several named ranges for convenience: dob (H5), sign (B5:B16), symbol (D5:D16), start (E5:E15), and end (F5:F16). The formula in H7 is:
The tricky part is in constructing the array, which is done with this expression:
On the left, we check for a dob (date of birth) greater than or equal to the start:
On the right, we check for a dob less than or equal to end:
The two expressions are joined with the multiplication operator (*), since multiplication corresponds with AND logic in Boolean algebra.
Inside each expression, we concatenate a date fragment from the table with the year from the birthdate, extracted with the YEAR function. Then we pass the result into the DATEVALUE function to get a valid Excel date. Because the named ranges "start" and "end" contain multiple values, the expressions above generate multiple results. With April 26, 1971 as a the date of birth in H5, we get:
We can now simply the original formula to:
The MATCH function then returns 2, and, since 2 is not an error, we have:
=INDEX(sign,2) // returns Taurus
And the INDEX function returns the second item in sign, "Taurus", as a final result. In the event that the birthdate falls between December 22 and January 19, the "between two dates" logic will fail and MATCH will throw an #N/A error. The IFNA function will catch this error and return 10, and INDEX will return "Capricorn"", the tenth item in sign (B5:B16).
As an alternative, the IFNA function or IFERROR function could be used at the outer level of the formula as well. However, I wanted the two formulas (see symbol lookup below) to be the identical except for the lookup array passed into INDEX, and I also wanted to retrieve the actual values in the table for Capricorn.
Retrieving the symbol
The formula in H8 to retrieve the symbol from the named range symbol (D5:D16), is almost identical to the original formula. The only difference is the array provided to INDEX, which is symbol instead of sign:
The symbols in the table are created with the UNICHAR function. They start at 9800, and end at 9811:
You can generate all symbols at once using SEQUENCE like this:
VLOOKUP with embedded table
The INDEX and MATCH formula above is based on a requirement to use the lookup table in B5:B16 as shown. If you don't need or want to show the lookup table, or if you don't mind a lookup table with two entries for Capricorn, you can use a formula based on the VLOOKUP function:
At the core, this is a straightforward VLOOKUP formula:
where the table_array argument is supplied as a hard-coded array constant. If we put these values into cells, we have a table like this:
Note that the values in the first column are actual numbers. Also note that Capricorn appears twice, and all entries are in ascending order.
To lookup a birthdate, we need a similar number, and for that we use the TEXT function like this:
--TEXT(dob,"m.dd") // returns 4.26
Text pulls the month number and day number out of the date as text. The double negative (--) then coerces the text to a number in the same format as the lookup table above. The result for April 26, 1971 is 4.26.
Note: I ran into this approach in a post on the MrExcel board, by the always amazing Aladin Akyurek.