Varied Criteria for IF Function in Conditional Formatting

Don't become another member of the 'IF' club, there are many other, often better / simpler ways to do things.

Take your formula in B7 for example, =IF($G$3=1,B13,IF($G$3=2,C12,IF($G$3=3,D12,IF($G$3=4,E12,IF($G$3=5,F12)))))

You have an IF for each week (and what appears to be an error, B13 should be B12?), when you don't really need any of them, you could simply use =INDEX(B12:F12,G3)

Setting your sheet up more efficiently also helps considerably.

For your criteria table below, have 1 column per week, lets say column I for week 2, and lets use Row 14 for the week numbers, so week 2 in I14, J14 for week 3, K14 for week 4, L14 for week 5.

Now beneath the week numbers enter the minimum score for the red condition, so I15, J15, K15 and L15 should all contain 0 (number only, don't add any text such as 'pts' to it).

In the next row enter the minimum score for the yellow conditions, so 8 in I16, 13 in J16, 17 in K16 and 26 in L16.

Repeat the pattern in the next 2 rows for the minimum scores for the green and gold conditions.

Then you can refer to that table in conditional formatting with some simple formulas.

For the red condition, you could use

=MATCH($B$7,INDEX($I$15:$L$18,0,MIN($G$3,5)))=1

For the other conditions, you just need to change the last number, =2 for yellow, =3 for green and = 4 for gold.

The bit in bold text caps the week number at 5, so the criteria in column L will be used for weeks 5 to 12 (or longer).

Have a go at setting it up yourself, you know where to find us if you get stuck.

im stuck....I reattached where I am in the process. I built the conditions as specified, but the cell isn't reflecting the correct color for the week. What am I doing wrong here?
Thank you for the INDEX tip. I just got used to IF formulas, so now I need to learn Index better.

Sorry, that was my bad!

I forgot to adjust the formula to allow for the fact that week 2 is the first week, so everything needs to be reduced by 1.

=MATCH($B$7,INDEX($I$15:$L$18,0,MIN($G$3,5)-1))=1

Or you could use

=MATCH($B$7,INDEX($I$15:$L$18,0,MATCH($G$3,$I$14:$L$14)))=1

as an alternative, which looks for the week number in row 15 that is equal to, or before the number in G3, rather than assuming that they are consecutive (which the first formula does).

Thank you Thank you!!!!! I am about to log a new post regarding a Vlookup with multiple criteria, which I guess is probably going to be another INDEX/MATCH type solution. I did try on my own, but still stuck.