What is your horoscope sign?

Here’s a fun topic! At the same time, this is a good opportunity to practice working with the LOOKUP function. Although, these days, people are talking much more about the XLOOKUP function I don’t see why, in this situation, we wouldn’t use its “older cousin”. Task: determine the zodiac sign of the person whose birthday is on the given date. This is achieved by searching the table that contains the range of dates that determine zodiac signs.

We will start by entering the date in cell B1. Next, enter the formula for determining the zodiac sign in cell B2. First, examine whether the day of birth is greater than the maximum date until which the zodiac sign lasts, for the month of birth. If so, then based on the first column with months, we determine the name of the sign that is taken from column A (A6: A17). If not, the month will be searched in the second column, and the sign name will be found in the same column as in the previous case. The formula should look like this:

=IF(DAY(B1)>LOOKUP(MONTH(B1),E6:E17,D6:D17),
LOOKUP(MONTH(B1),C6:C18,A6:A17),
LOOKUP(MONTH(B1),E6:E17,A6:A17))

This formula assumes that we have the date entered in cell B1. If you pay attention the Capricorn sign is repeated twice. This happens because those born at the beginning, as well as those born at the end of the year, belong to this sign.

I hope you found this example fun, and if you want you can try to determine the zodiac sign with the help of the XLOOKUP function.