How do I set up this checkbox formula in google sheets? I'm getting #N/A error

by Mr. Victor   Last Updated October 10, 2019 03:03 AM

I have a list of players in my league and next to each name is a checkbox to show if they are active or not. I set up a formula that checks each of the 12 weeks of game play to see if a player participated. If they did participate in any of the 12 weeks, I want the checkbox to be ticked so that their stats get displayed on the stats tab. I'm getting a lot of this and it's displaying #N/A:

ErrorDid not find value 'prduke11' in MATCH evaluation. Invalid: This cell's contents violate its validation rule

When player is active, the checkbox is ticked like it should be. My formula is below (I'm using a nested IF formula but have tried writing it with IFS and got similar results) ($B18 is the player in the list and the formula repeats only changing the Week tab [ex: Week1!, Week2!, etc] it is searching:

=IF(MATCH($B18,Week1!$B$10:$B,0),TRUE,IF(MATCH($B18,Week2!$B$10:$B,0),TRUE,IF(MATCH($B18,Week3!$B$10:$B,0),TRUE,IF(MATCH($B18,Week4!$B$10:$B,0),TRUE,IF(MATCH($B18,Week5!$B$10:$B,0),TRUE,IF(MATCH($B18,Week6!$B$10:$B,0),TRUE,IF(MATCH($B18,Week7!$B$10:$B,0),TRUE,IF(MATCH($B18,Week8!$B$10:$B,0),TRUE,IF(MATCH($B18,Week9!$B$10:$B,0),TRUE,IF(MATCH($B18,Week10!$B$10:$B,0),TRUE,IF(MATCH($B18,Week11!$B$10:$B,0),TRUE,IF(MATCH($B18,Week12!$B$10:$B,0),TRUE,FALSE))))))))))))

Tags : google-sheets


Related Questions


Updated March 03, 2018 02:03 AM

Updated September 20, 2019 15:03 PM

Updated September 19, 2018 13:03 PM

Updated June 10, 2019 17:03 PM

Updated July 26, 2018 21:03 PM