I'm not sure where to go with this but some time ago, we had some assistance setting up a google sheet for our community sports club. An arrayformula was used with switch and up until the end of March all was working. Having done some searches, it appears google may have made changes to Switch and other users reporting similar issues.
In summary, using google forms, players were entered confirming male or female and date of birth. Using a secondary tab, the age group was defined as follows:
Gender Group Date From Date To
Male/Female U06s 01/09/2014 31/08/2015
Male/Female U07s 01/09/2013 31/08/2014
Male/Female U08s 01/09/2012 31/08/2013
Male/Female U09s 01/09/2011 31/08/2012
Male/Female U10s 01/09/2010 31/08/2011
Male/Female U11s 01/09/2009 31/08/2010
Male U12s 01/09/2008 31/08/2009
Male U13s 01/09/2007 31/08/2008
Male U14s 01/09/2006 31/08/2007
Male U15s 01/09/2005 31/08/2006
Male U16s 01/09/2004 31/08/2005
Male U17s 01/09/2003 31/08/2004
Male U18s 01/09/2002 31/08/2003
Female G13s 01/09/2007 31/08/2009
Female G15s 01/09/2005 31/08/2007
Female G18s 01/09/2002 31/08/2005
Male/Female Delete
On the responses page, the below formula was used to define what age group each players was in. Now that no longer works and we get #value with error message "Switch has mismatched range siezes. Expected forw count:1" etc.
Formula is:
=ARRAYFORMULA({"Group";IF(A2:A="",,If(H:H="",,SWITCH(I:I,"Male",VLOOKUP(H:H,SORT({GroupInfo!C2:C18*({GroupInfo!A2:A18="Male"}+{GroupInfo!A2:A18="Male/Female"}),GroupInfo!B2:B18},1,1),2,1),"Female",VLOOKUP(H:H,SORT({GroupInfo!C2:C18*({GroupInfo!A2:A18="Female"}+{GroupInfo!A2:A18="Male/Female"}),GroupInfo!B2:B18},1,1),2,1))))})
Any suggestions or guidance on where I can get assistnace?
Many thanks