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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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