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
In D3:
Formula:
=INDEX('Chinese Zodiac Years'!$A$23:$A$34,MATCH("*"&YEAR($A3)&"*",'Chinese Zodiac Years'!$B$23:$B$34,0))
In E3:
Formula:=INDEX('Chinese Zodiac Years'!$C$23:$C$34,MATCH("*"&YEAR($A3)&"*",'Chinese Zodiac Years'!$B$23:$B$34,0))
Copy down.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
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)),"")
Awesome, Thank you Azumi!
I'm looking into this, but can't download the excel file - can anyone help with posting the completed workbook?
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
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!
Administrative Note:
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks