Hi everyone! I need help with a Google Sheets formula that suddenly broke. We use it for our local sports team to automatically assign players to age groups based on their birth date and gender.
The setup is pretty simple. We have a Google Form where people enter if they are male or female plus their birthday. Then we have another sheet with all the age categories like this:
Gender Category Start Date End Date
Male/Female U06s 01/09/2014 31/08/2015
Male/Female U07s 01/09/2013 31/08/2014
Male U12s 01/09/2008 31/08/2009
Male U13s 01/09/2007 31/08/2008
Female G13s 01/09/2007 31/08/2009
Female G15s 01/09/2005 31/08/2007
This formula worked great until recently:
=ARRAYFORMULA({"Category";IF(B2:B="",,If(G:G="",,SWITCH(H:H,"Male",VLOOKUP(G:G,SORT({AgeData!C2:C18*({AgeData!A2:A18="Male"}+{AgeData!A2:A18="Male/Female"}),AgeData!B2:B18},1,1),2,1),"Female",VLOOKUP(G:G,SORT({AgeData!C2:C18*({AgeData!A2:A18="Female"}+{AgeData!A2:A18="Male/Female"}),AgeData!B2:B18},1,1),2,1))))})
Now I get an error saying “Switch has mismatched range sizes. Expected row count:1”. Did Google change how SWITCH works with arrays? How can I fix this?
Had this exact problem last month after Google’s array handling update. SWITCH breaks because your VLOOKUP ranges aren’t the same size for male vs female lookups - it needs consistent array dimensions.
Skip restructuring the whole formula. Use MAP with a custom LAMBDA instead: MAP(G:G, H:H, LAMBDA(birthdate, gender, your lookup logic here)). This processes each row separately, so you won’t hit those array conflicts that kill SWITCH.
Way more stable than nested IFs and much easier to debug. Performance is basically the same once you get it set up right.
Indeed, Google has updated how SWITCH functions with arrays, requiring all parameters to match in dimensions. This causes the error in your formula due to mismatched ranges. I had a similar experience recently and opted for nested IF statements instead, which, while longer, resolved the issue. Another approach is to utilize FILTER with OR conditions for separate male and female lookups and then combine the results. Alternatively, consider creating helper columns for each gender, which can simplify the decision-making process and enhance reliability following Google’s update.
i feel ya, google sheets can be super frustrating! nested IFs could be a quick fix, but yeah, give the FILTER function a shot too. sooo much easier at times!
This topic was automatically closed 4 days after the last reply. New replies are no longer allowed.