Search code examples
google-sheets

Conditional Formatting In an Array with Multiple Conditions Evaluated

Folks,

I'm improving a Google Sheet I use for managing a little league team. I duplicate the tab containing the lineup of the previous game to set the lineup of the next game, so the cell reference locations should remain consistent.

I'm trying to do some conditional formatting to visually alert me if I've positioned a pitcher who is ineligible to pitch. I have two tables:

  1. Lineup and Positions Table (A6:J17)
  2. Pitcher Eligibility Table (P6:R17)

Lineup and Pitcher Eligibility Tables

I'm trying to create a conditional formatting rule to fill a cell red if I place a pitcher who is not eligible to pitch.

I currently have a conditional formatting rule with a custom formula of =AND(E6="P",xlookup(B6,P6:P17,R6:R17)="N") applied to E6:J17, however this doesn't seem to update the xlookup's fist value (B6) as it does not shade any cells; I expect F10 and G10 to be shaded. Any suggestions on how I can better accomplish this? Broken Conditional Formatting Rule

Solution

  • You may try with slight modification to your exisitng formula:

    =AND(E6="P",xlookup($B6,$P$6:$P$17,$R$6:$R$17)="N")
    • Here's an article for a bit more info on absolute/relative references