A thousand nested if/and/or

Question

I have a huge "sheet application", in one row, and one of the fields, called "price" literally has 433 "IF" statements in the formula bar. Basically, there's a lot of options, and based on them, I have to change the price in the field (if user picks option 1, set price 1, and so on). I was just wondering if there was a more sane way of writing that, because

1. it's seriously tiring and incomprehensible
2. it usually doesn't even work because I have to throw a IF(AND()) and IF(OR()) from time to time which breaks my workflow, and makes debugging impossible.

Any help greatly appreciated.

Answer 1

instead of AND you can use multiplication * and instead of OR use sum + - that way you can easily use ARRAYFORMULA if needed. also have a look here: https://webapps.stackexchange.com/q/123729/186471 for alternatives

---

update

try this instead of your pstebin formula:

=ARRAYFORMULA(IFERROR(IF((D24<>"")*(E24=""), 
 VLOOKUP(D24, {data!A:B; data!F1:G23}, 2, 0), 
 VLOOKUP(D24&E24, {{data!F24:F37&data!F50; data!F28&data!F49}, 
 {data!I23; data!I23; data!I23; data!I23:I29; 
  data!I29; data!I29; data!I29; data!I30; data!G28}}, 2, 0))))

and data sheet: