SOLVED

Multiple dates in Birthday List

%3CLINGO-SUB%20id%3D%22lingo-sub-2540013%22%20slang%3D%22en-US%22%3EMultiple%20dates%20in%20Birthday%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2540013%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20birthdays%20for%20family%20%26amp%3B%20friends%20and%2C%20of%20course%2C%20some%20have%20the%20same%20date.%20I%20use%20this%20formula%20(%20%3DXLOOKUP(E1%2CF9%3AF110%2CB9%3AB110%2C%22NONE%22%2C2)%26amp%3B%22%20%22%26amp%3BXLOOKUP(E1%2CF9%3AF110%2CA9%3AA110%2C%22%20%22%2C2)%20)%26nbsp%3B%20to%20locate%20a%20name%20that%20matches%20TODAY()%20(E1)%20AND%20the%20year%20they%20were%20born%20in%20(to%20display%20their%20age).%20My%20question%20is%20how%20do%20I%20modify%20the%20formula%20to%20find%20multiple%2C%20or%20all%2C%20dates%20the%20meet%20the%20criteria%3F%20Thanks%20in%20advance!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2540013%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2540343%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20dates%20in%20Birthday%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2540343%22%20slang%3D%22en-US%22%3EWhy%20do%20you%20use%20XLOOKUP%20and%20not%20FILTER%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2540429%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20dates%20in%20Birthday%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2540429%22%20slang%3D%22en-US%22%3EXLOOKUP%20just%20seemed%20the%20easier%20way%20to%20go...never%20even%20considered%20FILTER%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2540463%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20dates%20in%20Birthday%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2540463%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684000%22%20target%3D%22_blank%22%3E%40dlcartin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20FILTER(%3CFONT%20color%3D%22%230000FF%22%3EIF(%7B0%2C1%7D%2CName%2C%20Age)%3C%2FFONT%3E%2C%20Birthday%3Dtoday%2C%20%22None%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%20blue%20combines%20the%20two%20columns%20into%20a%202D%20range%2C%20reversing%20their%20order.%26nbsp%3B%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%20then%20picks%20out%20the%20matching%20birthdays.%26nbsp%3B%20You%20could%20also%20add%20a%20%3CSTRONG%3ESORT%3C%2FSTRONG%3E%20to%20list%20the%20individuals%20in%20age%20order.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2540689%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20dates%20in%20Birthday%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2540689%22%20slang%3D%22en-US%22%3EThanks%2C%20but%20it%20keeps%20giving%20me%20a%20SPILL%20error.%3C%2FLINGO-BODY%3E
Contributor

I have a list of birthdays for family & friends and, of course, some have the same date. I use this formula ( =XLOOKUP(E1,F9:F110,B9:B110,"NONE",2)&" "&XLOOKUP(E1,F9:F110,A9:A110," ",2) )  to locate a name that matches TODAY() (E1) AND the year they were born in (to display their age). My question is how do I modify the formula to find multiple, or all, dates the meet the criteria? Thanks in advance!!!

5 Replies
Why do you use XLOOKUP and not FILTER?
XLOOKUP just seemed the easier way to go...never even considered FILTER
best response confirmed by dlcartin (Contributor)
Solution

@dlcartin 

= FILTER(IF({0,1},Name, Age), Birthday=today, "None")

The blue combines the two columns into a 2D range, reversing their order.  FILTER then picks out the matching birthdays.  You could also add a SORT to list the individuals in age order.

Thanks, but it keeps giving me a SPILL error.
UPDATE: I got the SPLL error worked out. It's working fine....THANK YOU!!!