Hi everyone! I need help with a Google Sheets formula that broke recently. Our youth football club has been using this setup since last year but it stopped working around April.
We collect player info through Google Forms including gender (male/female) and birth date. Then we have a lookup table on another sheet that shows age categories:
Gender Category Start Date End Date
Male/Female U06 01/09/2014 31/08/2015
Male/Female U07 01/09/2013 31/08/2014
Male/Female U08 01/09/2012 31/08/2013
Male U12 01/09/2008 31/08/2009
Male U13 01/09/2007 31/08/2008
Female G13 01/09/2007 31/08/2009
Female G15 01/09/2005 31/08/2007
Our formula used to automatically assign players to the right age group but now throws a #VALUE error saying “Switch has mismatched range sizes. Expected row count:1”.
The formula we’re using is:
=ARRAYFORMULA({"Category";IF(A2:A="",,IF(H:H="",,SWITCH(I:I,"Male",VLOOKUP(H:H,SORT({AgeGroups!C2:C18*({AgeGroups!A2:A18="Male"}+{AgeGroups!A2:A18="Male/Female"}),AgeGroups!B2:B18},1,1),2,1),"Female",VLOOKUP(H:H,SORT({AgeGroups!C2:C18*({AgeGroups!A2:A18="Female"}+{AgeGroups!A2:A18="Male/Female"}),AgeGroups!B2:B18},1,1),2,1))))})
Has anyone else run into this issue? I think Google might have changed how SWITCH works with arrays. Any ideas how to fix this would be amazing!
Had the exact same problem with our company tracking system around that time. Google made SWITCH stricter about array dimensions when handling ranges. Your formula’s passing entire columns (H:H, I:I) which creates mismatched array sizes. Quickest fix: replace those full column references with bounded ranges. Use H2:H1000 and I2:I1000 instead (adjust for your data size). This stops SWITCH from processing infinite arrays. Or try a helper column approach - separate columns for male/female lookups, then combine with a simple IF statement. Less elegant but way more reliable since Google keeps changing how arrays work. I’ve found bounded ranges work best long-term for youth sports databases.
Had the same SWITCH problem with arrays earlier this year. Google definitely broke how SWITCH handles array inputs - it’s been a nightmare for formulas that used to work fine. That error usually pops up when SWITCH gets arrays of different sizes. Ditch SWITCH and go with nested IF statements instead. Try this: =ARRAYFORMULA({"Category";IF(A2:A="",,IF(H:H="",,IF(I:I="Male",VLOOKUP(H:H,SORT({AgeGroups!C2:C18*({AgeGroups!A2:A18="Male"}+{AgeGroups!A2:A18="Male/Female"}),AgeGroups!B2:B18},1,1),2,1),IF(I:I="Female",VLOOKUP(H:H,SORT({AgeGroups!C2:C18*({AgeGroups!A2:A18="Female"}+{AgeGroups!A2:A18="Male/Female"}),AgeGroups!B2:B18},1,1),2,1),""))))}) - should work way better. You could also try MAP or LAMBDA functions for a cleaner approach, but nested IF will fix your youth club system without rebuilding everything.
yeah, google broke switch around april/may. use ifs with arrayformula instead - it’s way more reliable. try =ARRAYFORMULA(IFS(I2:I="Male",your_male_vlookup,I2:I="Female",your_female_vlookup)). also check that all your ranges match in size or you’ll keep hitting that error.