Nesting 'And' 'or' within an If

[SSharrock]

Good Afternoon,

On the attached sheet in column X, I am trying to input the Allocation percentage dependent on a number of variables:

Type (Bar, Blank, Coil, Sheet, Special)
Sub Fam Code (2A02, BC02-05, BC99, BZ99)
Sup Category (Arcelor, Independent, Tata/ Corus)

The allocation percentage is based on the below; anything outside of this should result in "Other".
Sheet (BC02/03/04) A=40%, T=40% & I=20%
Coil (BC02 & BC03) A=40%, T=40% & I=20%
Coil (BC04) A=30%, T=50% & I=20%

Example 1:
Type - Sheet & Coil
Sup Category - Independent
Result 20%

Example 2:
Type - Coil
Sup Category - Tata/ Corus
Sub Fam Code - BC04
Result 50%

Example 3:
Type - Coil
Sup Category - Arcelor
Sub Fam Code - BC04
Result 30%

I hope I have made it clear as to what I intend to change.

[ben_hensel]

I think trying to nest all that decision-making into IF statements is too complicted. Changing the parameters will mean re-working the logic every single time, it would just be a mess.

I advise pumping through lookup tables using INDEX(MATCH) combos. That way the formulas can stay relatively stable, all you need to do is update the range-size references maybe (not even that if you go to named ranges, you'll just have to update the name refernce), and it will be much clearer what combos are supposed to output what percentage just looking at it, so you will have an easier time trouble-shooting.

See column R in attached for the lookup and the new sheet "tables" for the tables.