How to calculate zodiac sign in google spreadsheet?

Question

Google Sheets

It is necessary to solve the following problem: there are many employees and branches in the organization, there is only one accounting department. There is a list with dates of birth, and I wanted the signs of the zodiac to be determined from the date in it, because. based on this, an event with general congratulations will be organized at the end of the mark period.

Answer 1

Try VLOOKUP
For example, you have a directory of employees and zodiac signs. In the latter, you need to make indexes so that the search takes place in a given range. The index is an integer that is assembled from one and the start of the sign in the calendar. For example, Leo begins on July 23, which means that its index will be written as a number consisting of the digits 1, 06, 23, i.e. 10623.
To find out the index for an employee, you need to call the formula VALUE(TEXT(A1;"1MMDD"), where is the A1date of birth of the employee.
Because there will be a lot of employees, then it is necessary that the formula is stretched independently. For example, a fairly reliable option

=ARRAYFORMULA(IF(B2:B;IFERROR(
  VLOOKUP(
    VALUE(TEXT(B2:B;"1MMDD"));
    SORT({'Знаки. Спаравочник'!J:J\'Знаки. Спаравочник'!A:L};1;1);
    2;
    1
  )
);""))

We check that there is B2:Ba value in, otherwise we output an empty string, then in the composite range

{'Знаки. Спаравочник'!J:J\'Знаки. Спаравочник'!A:L}

we are looking for an index on the column J:J, i.e. "Western astrology (variant II)".
To also display elements with planets, you can add a list of columns for output

=ARRAYFORMULA(IF(B2:B;IFERROR(
  VLOOKUP(
    VALUE(TEXT(B2:B;"1MMDD"));
    SORT({'Знаки. Спаравочник'!J:J\'Знаки. Спаравочник'!A:L};1;1);
    {2\3\4\5\6};
    1
  )
);""))

• Example in Spreadsheet https://docs.google.com/spreadsheets/d/1nsFTjp96IR...