Trying to output a Chinese Zodiac Animal if the Birth Year matches the corresponding year

Hello,

I'm trying to figure out what formula/function to use to determine a person's chinese zodiac animal if their birth year corresponds with the year for that given animal. I'm still relatively new to excel. I've explored VLOOKUP, MATCH, and INDEX, but none of these seem to work. If you have any ideas I would be most appreciative. I'll attach my excel worksheet to this post of how far I've gotten. All birth years are fictitious.

Thanks in advance,

Rory

Option without the table:

For Chinese Animal Zodiac, put this on D3 and copied down:

=IF(A3="","",INDEX({"Rat","Ox","Tiger","Rabbit","Dragon","Snake","Horse","Goat","Monkey","Rosster","Dog","Pig"},MATCH(MOD((YEAR(A3)-4),12),({0,1,2,3,4,5,6,7,8,9,10,11}),0)))

For Personality, put this on E3 and copied down:

=IFERROR(INDEX({"Quick-witted, resourceful, versatile, kind","Diligent, dependable, strong, determined","Brave, confident, competitive","Quiet, elegant, kind, responsible","Confident, intelligent, enthusiastic","Enigmatic, intelligent, wise","Animated, active, energetic","Calm, gentle, sympathetic","Sharp, smart, curiosity","Observant, hardworking, courageous","Lovely, honest, prudent","Compassionate, generous, diligent"},MATCH(D3,{"Rat","Ox","Tiger","Rabbit","Dragon","Snake","Horse","Goat","Monkey","Rooster","Dog","Pig"},0)),"")

Amazing! My mind is offically blown...

Thanks Olly!

Or..................

[D3] =LOOKUP(1,-FIND(YEAR($A3),'Chinese Zodiac Years'!$B$23:$B$34),'Chinese Zodiac Years'!A$23:A$34)

[E3] =LOOKUP(1,-FIND(YEAR($A3),'Chinese Zodiac Years'!$B$23:$B$34),'Chinese Zodiac Years'!C$23:C$34)

All copied down

Regards
Bosco

Awesome, Thank you Azumi!

I'm looking into this, but can't download the excel file - can anyone help with posting the completed workbook?

I don't think the result will be accurate, I too wanted a formula to return a Chinese Zodiac animal base on a date, but one thing causes a big headache. Chinese calendar is different to a Gregorian calendar, so it will be dicrepancies if someone born between 21st Jan & 20th Feb. They are different every year. Some start as early as 21st January and some as late as 20th February. For example, if someone born 1st Feb 1984, using the above formulas will output "Rat", but year of "rat" starts on 10th Feb 1984 & ends 19th Feb 1985 and therefore the correct result should be "pig". I wonder if this can be hard coded in a formula rather than having to use the table. Thanks!