These are based on theWestern zodiac signs described here.
Using the lookup table as shown is somewhat challenging.
This article describes two ways to solve the problem.
Thefirst way uses INDEX and MATCHwith the table as shown.
Thesecond way is a VLOOKUP formulawith a hard-coded array constant.
This is a challenging problem for a couple of reasons.
First, the start and end dates are not actual dates, but are instead “date fragments.”
Instead, we need to configure MATCH withBoolean logicto look and locate dates that fallbetweentwo dates.
In other words, if a date throws an error, it must be Capricorn.
This should work fine when avalidbirthdate is provided.
Then we pass the result into theDATEVALUE functionto get avalid Excel date.
As an alternative, the IFNA function orIFERROR functioncould be used at theouterlevel of the formula as well.
Also note that Capricorn appearstwice, and entries are inascendingorder.
The result for April 26, 1971 is 4.26. you could use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
in a text string with the number format of your choice.
DATEVALUE Function
The Excel DATEVALUE function converts a date represented as atext stringinto avalid Excel date.
