0

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.

(Edit) - link to demo sheet: https://docs.google.com/spreadsheets/d/1weJgCmV_2Hzifb8QoR7I7gMhrAovLs5tXnKqW1ZWuU4/edit?usp=sharing

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), "...")
Improve this question
4
  • 1
    Please read how to and share a test sheet so as you can be easier helped.
    – marikamitsos
    Commented Feb 28, 2020 at 21:33
  • IMHO you should add more details like the spreadsheet and sheet structure.
    – Rubén - Volunteer Moderator - ♦
    Commented Feb 28, 2020 at 21:37
  • Added link to demo sheet.
    – Hedley
    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.
    – marikamitsos
    Commented Mar 6, 2020 at 3:32

1 Answer 1

Reset to default
1

You need to update your Rack Rates data a bit.

Add to your "Rack Rates" technical row which will contain key for further mapping - just adding together your variables (i.e. Low2020FALSE).

Then Use following formula to get required rate

=INDEX('Rack Rates'!$A:$Y,
 MATCH(P4,'Rack Rates'!$A:$A,0),
 MATCH(Q4&R4&T4,'Rack Rates'!$A$1:$Y$1,0))

You can find it here https://docs.google.com/spreadsheets/d/1_yWM2lNNLkETaLPWcLoHd1bJrCFIdwZMU2NNvlX_qSA/edit#gid=1494847140

Improve this answer
1
  • Well, I was hoping that someone would tell me I should be using x method instead of just a formula (pivot tables? lookups? something else?), but perhaps such a thing isn't realistic. I'll except your suggestion for improving my formula, Oleg. Thanks!
    – Hedley
    Commented Mar 14, 2020 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.