# Request for alternative method suggestions to this Google Sheets formula

I wrote this ridiculously long formula (60,000+ characters, shown below) in Google Sheets, and it works perfectly. However, now I want to simplify it, as I'm certain that it's inefficient and could be written - at minimum - more tightly.

Essentially, it's looking at a list of hotel reservations and displaying the rack rate of each individual reservation. It uses several criteria for this:

• Season (there are four seasons: Holiday, High, Mid, and Low).
• Room Type (there are currently 12 different room classes).
• Year (each business year has different date ranges that define its seasons).
• Day of Week (the rooms have different rates for weekdays and weekends).

I'm not looking for anyone to voluntarily re-write the formula (or do anything else) for me, but my guess is that I could probably achieve the same (or even better) result if I used some method other than an IFS-AND formula.

My hope is that someone will point me to the better-best method of doing this (pivot tables? lookups? something else?) so that I can go and read the docs for it and then build it on my own. A point in the right direction might help me avoid some unnecessary dead ends.

Thanks!

H.

``````=IFS(
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$4,S4=2020,U4=True), 'Rack Rates'!\$C\$8,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$5,S4=2020,U4=True), 'Rack Rates'!\$C\$9,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$6,S4=2020,U4=True), 'Rack Rates'!\$C\$10,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$7,S4=2020,U4=True), 'Rack Rates'!\$C\$11,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$8,S4=2020,U4=True), 'Rack Rates'!\$C\$12,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$9,S4=2020,U4=True), 'Rack Rates'!\$C\$13,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$10,S4=2020,U4=True), 'Rack Rates'!\$C\$14,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$11,S4=2020,U4=True), 'Rack Rates'!\$C\$15,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$13,S4=2020,U4=True), 'Rack Rates'!\$C\$17,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$14,S4=2020,U4=True), 'Rack Rates'!\$C\$18,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$15,S4=2020,U4=True), 'Rack Rates'!\$C\$19,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$16,S4=2020,U4=True), 'Rack Rates'!\$C\$20,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$4,S4=2020,U4=True), 'Rack Rates'!\$F\$8,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$5,S4=2020,U4=True), 'Rack Rates'!\$F\$9,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$6,S4=2020,U4=True), 'Rack Rates'!\$F\$10,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$7,S4=2020,U4=True), 'Rack Rates'!\$F\$11,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$8,S4=2020,U4=True), 'Rack Rates'!\$F\$12,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$9,S4=2020,U4=True), 'Rack Rates'!\$F\$13,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$10,S4=2020,U4=True), 'Rack Rates'!\$F\$14,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$11,S4=2020,U4=True), 'Rack Rates'!\$F\$15,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$13,S4=2020,U4=True), 'Rack Rates'!\$F\$17,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$14,S4=2020,U4=True), 'Rack Rates'!\$F\$18,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$15,S4=2020,U4=True), 'Rack Rates'!\$F\$19,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$16,S4=2020,U4=True), 'Rack Rates'!\$F\$20,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$4,S4=2020,U4=True), 'Rack Rates'!\$I\$8,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$5,S4=2020,U4=True), 'Rack Rates'!\$I\$9,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$6,S4=2020,U4=True), 'Rack Rates'!\$I\$10,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$7,S4=2020,U4=True), 'Rack Rates'!\$I\$11,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$8,S4=2020,U4=True), 'Rack Rates'!\$I\$12,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$9,S4=2020,U4=True), 'Rack Rates'!\$I\$13,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$10,S4=2020,U4=True), 'Rack Rates'!\$I\$14,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$11,S4=2020,U4=True), 'Rack Rates'!\$I\$15,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$13,S4=2020,U4=True), 'Rack Rates'!\$I\$17,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$14,S4=2020,U4=True), 'Rack Rates'!\$I\$18,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$15,S4=2020,U4=True), 'Rack Rates'!\$I\$19,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$16,S4=2020,U4=True), 'Rack Rates'!\$I\$20,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$4,S4=2020,U4=True), 'Rack Rates'!\$L\$8,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$5,S4=2020,U4=True), 'Rack Rates'!\$L\$9,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$6,S4=2020,U4=True), 'Rack Rates'!\$L\$10,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$7,S4=2020,U4=True), 'Rack Rates'!\$L\$11,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$8,S4=2020,U4=True), 'Rack Rates'!\$L\$12,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$9,S4=2020,U4=True), 'Rack Rates'!\$L\$13,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$10,S4=2020,U4=True), 'Rack Rates'!\$L\$14,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$11,S4=2020,U4=True), 'Rack Rates'!\$L\$15,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$13,S4=2020,U4=True), 'Rack Rates'!\$L\$17,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$14,S4=2020,U4=True), 'Rack Rates'!\$L\$18,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$15,S4=2020,U4=True), 'Rack Rates'!\$L\$19,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$16,S4=2020,U4=True), 'Rack Rates'!\$L\$20,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$4,S4=2020,U4=False), 'Rack Rates'!\$B\$8,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$5,S4=2020,U4=False), 'Rack Rates'!\$B\$9,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$6,S4=2020,U4=False), 'Rack Rates'!\$B\$10,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$7,S4=2020,U4=False), 'Rack Rates'!\$B\$11,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$8,S4=2020,U4=False), 'Rack Rates'!\$B\$12,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$9,S4=2020,U4=False), 'Rack Rates'!\$B\$13,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$10,S4=2020,U4=False), 'Rack Rates'!\$B\$14,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$11,S4=2020,U4=False), 'Rack Rates'!\$B\$15,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$13,S4=2020,U4=False), 'Rack Rates'!\$B\$17,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$14,S4=2020,U4=False), 'Rack Rates'!\$B\$18,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$15,S4=2020,U4=False), 'Rack Rates'!\$B\$19,
AND(R4='Rack Rates'!\$B\$2,Q4=Data!\$A\$16,S4=2020,U4=False), 'Rack Rates'!\$B\$20,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$4,S4=2020,U4=False), 'Rack Rates'!\$E\$8,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$5,S4=2020,U4=False), 'Rack Rates'!\$E\$9,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$6,S4=2020,U4=False), 'Rack Rates'!\$E\$10,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$7,S4=2020,U4=False), 'Rack Rates'!\$E\$11,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$8,S4=2020,U4=False), 'Rack Rates'!\$E\$12,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$9,S4=2020,U4=False), 'Rack Rates'!\$E\$13,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$10,S4=2020,U4=False), 'Rack Rates'!\$E\$14,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$11,S4=2020,U4=False), 'Rack Rates'!\$E\$15,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$13,S4=2020,U4=False), 'Rack Rates'!\$E\$17,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$14,S4=2020,U4=False), 'Rack Rates'!\$E\$18,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$15,S4=2020,U4=False), 'Rack Rates'!\$E\$19,
AND(R4='Rack Rates'!\$E\$2,Q4=Data!\$A\$16,S4=2020,U4=False), 'Rack Rates'!\$E\$20,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$4,S4=2020,U4=False), 'Rack Rates'!\$H\$8,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$5,S4=2020,U4=False), 'Rack Rates'!\$H\$9,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$6,S4=2020,U4=False), 'Rack Rates'!\$H\$10,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$7,S4=2020,U4=False), 'Rack Rates'!\$H\$11,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$8,S4=2020,U4=False), 'Rack Rates'!\$H\$12,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$9,S4=2020,U4=False), 'Rack Rates'!\$H\$13,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$10,S4=2020,U4=False), 'Rack Rates'!\$H\$14,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$11,S4=2020,U4=False), 'Rack Rates'!\$H\$15,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$13,S4=2020,U4=False), 'Rack Rates'!\$H\$17,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$14,S4=2020,U4=False), 'Rack Rates'!\$H\$18,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$15,S4=2020,U4=False), 'Rack Rates'!\$H\$19,
AND(R4='Rack Rates'!\$H\$2,Q4=Data!\$A\$16,S4=2020,U4=False), 'Rack Rates'!\$H\$20,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$4,S4=2020,U4=False), 'Rack Rates'!\$K\$8,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$5,S4=2020,U4=False), 'Rack Rates'!\$K\$9,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$6,S4=2020,U4=False), 'Rack Rates'!\$K\$10,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$7,S4=2020,U4=False), 'Rack Rates'!\$K\$11,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$8,S4=2020,U4=False), 'Rack Rates'!\$K\$12,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$9,S4=2020,U4=False), 'Rack Rates'!\$K\$13,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$10,S4=2020,U4=False), 'Rack Rates'!\$K\$14,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$11,S4=2020,U4=False), 'Rack Rates'!\$K\$15,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$13,S4=2020,U4=False), 'Rack Rates'!\$K\$17,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$14,S4=2020,U4=False), 'Rack Rates'!\$K\$18,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$15,S4=2020,U4=False), 'Rack Rates'!\$K\$19,
AND(R4='Rack Rates'!\$K\$2,Q4=Data!\$A\$16,S4=2020,U4=False), 'Rack Rates'!\$K\$20,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$4,S4=2021,U4=True), 'Rack Rates'!\$P\$8,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$5,S4=2021,U4=True), 'Rack Rates'!\$P\$9,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$6,S4=2021,U4=True), 'Rack Rates'!\$P\$10,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$7,S4=2021,U4=True), 'Rack Rates'!\$P\$11,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$8,S4=2021,U4=True), 'Rack Rates'!\$P\$12,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$9,S4=2021,U4=True), 'Rack Rates'!\$P\$13,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$10,S4=2021,U4=True), 'Rack Rates'!\$P\$14,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$11,S4=2021,U4=True), 'Rack Rates'!\$P\$15,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$13,S4=2021,U4=True), 'Rack Rates'!\$P\$17,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$14,S4=2021,U4=True), 'Rack Rates'!\$P\$18,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$15,S4=2021,U4=True), 'Rack Rates'!\$P\$19,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$16,S4=2021,U4=True), 'Rack Rates'!\$P\$20,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$4,S4=2021,U4=True), 'Rack Rates'!\$S\$8,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$5,S4=2021,U4=True), 'Rack Rates'!\$S\$9,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$6,S4=2021,U4=True), 'Rack Rates'!\$S\$10,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$7,S4=2021,U4=True), 'Rack Rates'!\$S\$11,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$8,S4=2021,U4=True), 'Rack Rates'!\$S\$12,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$9,S4=2021,U4=True), 'Rack Rates'!\$S\$13,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$10,S4=2021,U4=True), 'Rack Rates'!\$S\$14,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$11,S4=2021,U4=True), 'Rack Rates'!\$S\$15,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$13,S4=2021,U4=True), 'Rack Rates'!\$S\$17,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$14,S4=2021,U4=True), 'Rack Rates'!\$S\$18,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$15,S4=2021,U4=True), 'Rack Rates'!\$S\$19,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$16,S4=2021,U4=True), 'Rack Rates'!\$S\$20,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$4,S4=2021,U4=True), 'Rack Rates'!\$V\$8,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$5,S4=2021,U4=True), 'Rack Rates'!\$V\$9,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$6,S4=2021,U4=True), 'Rack Rates'!\$V\$10,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$7,S4=2021,U4=True), 'Rack Rates'!\$V\$11,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$8,S4=2021,U4=True), 'Rack Rates'!\$V\$12,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$9,S4=2021,U4=True), 'Rack Rates'!\$V\$13,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$10,S4=2021,U4=True), 'Rack Rates'!\$V\$14,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$11,S4=2021,U4=True), 'Rack Rates'!\$V\$15,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$13,S4=2021,U4=True), 'Rack Rates'!\$V\$17,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$14,S4=2021,U4=True), 'Rack Rates'!\$V\$18,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$15,S4=2021,U4=True), 'Rack Rates'!\$V\$19,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$16,S4=2021,U4=True), 'Rack Rates'!\$V\$20,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$4,S4=2021,U4=True), 'Rack Rates'!\$Y\$8,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$5,S4=2021,U4=True), 'Rack Rates'!\$Y\$9,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$6,S4=2021,U4=True), 'Rack Rates'!\$Y\$10,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$7,S4=2021,U4=True), 'Rack Rates'!\$Y\$11,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$8,S4=2021,U4=True), 'Rack Rates'!\$Y\$12,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$9,S4=2021,U4=True), 'Rack Rates'!\$Y\$13,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$10,S4=2021,U4=True), 'Rack Rates'!\$Y\$14,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$11,S4=2021,U4=True), 'Rack Rates'!\$Y\$15,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$13,S4=2021,U4=True), 'Rack Rates'!\$Y\$17,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$14,S4=2021,U4=True), 'Rack Rates'!\$Y\$18,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$15,S4=2021,U4=True), 'Rack Rates'!\$Y\$19,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$16,S4=2021,U4=True), 'Rack Rates'!\$Y\$20,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$4,S4=2021,U4=False), 'Rack Rates'!\$O\$8,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$5,S4=2021,U4=False), 'Rack Rates'!\$O\$9,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$6,S4=2021,U4=False), 'Rack Rates'!\$O\$10,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$7,S4=2021,U4=False), 'Rack Rates'!\$O\$11,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$8,S4=2021,U4=False), 'Rack Rates'!\$O\$12,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$9,S4=2021,U4=False), 'Rack Rates'!\$O\$13,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$10,S4=2021,U4=False), 'Rack Rates'!\$O\$14,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$11,S4=2021,U4=False), 'Rack Rates'!\$O\$15,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$13,S4=2021,U4=False), 'Rack Rates'!\$O\$17,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$14,S4=2021,U4=False), 'Rack Rates'!\$O\$18,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$15,S4=2021,U4=False), 'Rack Rates'!\$O\$19,
AND(R4='Rack Rates'!\$O\$2,Q4=Data!\$A\$16,S4=2021,U4=False), 'Rack Rates'!\$O\$20,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$4,S4=2021,U4=False), 'Rack Rates'!\$R\$8,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$5,S4=2021,U4=False), 'Rack Rates'!\$R\$9,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$6,S4=2021,U4=False), 'Rack Rates'!\$R\$10,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$7,S4=2021,U4=False), 'Rack Rates'!\$R\$11,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$8,S4=2021,U4=False), 'Rack Rates'!\$R\$12,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$9,S4=2021,U4=False), 'Rack Rates'!\$R\$13,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$10,S4=2021,U4=False), 'Rack Rates'!\$R\$14,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$11,S4=2021,U4=False), 'Rack Rates'!\$R\$15,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$13,S4=2021,U4=False), 'Rack Rates'!\$R\$17,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$14,S4=2021,U4=False), 'Rack Rates'!\$R\$18,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$15,S4=2021,U4=False), 'Rack Rates'!\$R\$19,
AND(R4='Rack Rates'!\$R\$2,Q4=Data!\$A\$16,S4=2021,U4=False), 'Rack Rates'!\$R\$20,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$4,S4=2021,U4=False), 'Rack Rates'!\$U\$8,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$5,S4=2021,U4=False), 'Rack Rates'!\$U\$9,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$6,S4=2021,U4=False), 'Rack Rates'!\$U\$10,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$7,S4=2021,U4=False), 'Rack Rates'!\$U\$11,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$8,S4=2021,U4=False), 'Rack Rates'!\$U\$12,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$9,S4=2021,U4=False), 'Rack Rates'!\$U\$13,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$10,S4=2021,U4=False), 'Rack Rates'!\$U\$14,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$11,S4=2021,U4=False), 'Rack Rates'!\$U\$15,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$13,S4=2021,U4=False), 'Rack Rates'!\$U\$17,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$14,S4=2021,U4=False), 'Rack Rates'!\$U\$18,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$15,S4=2021,U4=False), 'Rack Rates'!\$U\$19,
AND(R4='Rack Rates'!\$U\$2,Q4=Data!\$A\$16,S4=2021,U4=False), 'Rack Rates'!\$U\$20,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$4,S4=2021,U4=False), 'Rack Rates'!\$X\$8,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$5,S4=2021,U4=False), 'Rack Rates'!\$X\$9,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$6,S4=2021,U4=False), 'Rack Rates'!\$X\$10,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$7,S4=2021,U4=False), 'Rack Rates'!\$X\$11,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$8,S4=2021,U4=False), 'Rack Rates'!\$X\$12,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$9,S4=2021,U4=False), 'Rack Rates'!\$X\$13,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$10,S4=2021,U4=False), 'Rack Rates'!\$X\$14,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$11,S4=2021,U4=False), 'Rack Rates'!\$X\$15,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$13,S4=2021,U4=False), 'Rack Rates'!\$X\$17,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$14,S4=2021,U4=False), 'Rack Rates'!\$X\$18,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$15,S4=2021,U4=False), 'Rack Rates'!\$X\$19,
AND(R4='Rack Rates'!\$X\$2,Q4=Data!\$A\$16,S4=2021,U4=False), 'Rack Rates'!\$X\$20,
AND(R4<>99999), "...")
``````
• Please read how to and share a test sheet so as you can be easier helped. Commented Feb 28, 2020 at 21:33
• IMHO you should add more details like the spreadsheet and sheet structure. Commented Feb 28, 2020 at 21:37
• Added link to demo sheet. Commented Feb 28, 2020 at 22:34
• Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Commented Mar 6, 2020 at 3:32

``````=INDEX('Rack Rates'!\$A:\$Y,