Trying to finalize consignment calculator with tiered rates

I would do this by making a few simple adjustments to your sheet and using a few helper columns.
Helper columns just break up a complex formula into smaller bits and makes it easier to understand and debug.
Helper columns can be hidden of course

In P9 use formula =MAX(IF($A$9:$A$18<=N9,$A$9:$A$18)) (Note this is an array formula and MUST be entered by pressing Ctrl+Shift+Enter) - This gives you your price bracket
In Q9 use formula =VLOOKUP($P9,$A$9:$G$17,6,0) - This gives you your Payout percentage
In R9 use formula =VLOOKUP($P9,$A$9:$G$17,7,0) - This gives you any adjustments

Copy these down as far as you need and hide the columns

Then in O9 use formula =IF($Q9>1,$Q9,$Q9*$N9+$R9) and drag down.

I'll leave it to you to sum the payout and multiply it by the quantity (if you want that) and put any headings etc. for the adjustment.

Something like the attached