Complicated array formula referencing other sheets

I use this Google Sheet to track proofreading jobs. I have columns with formulas to do some calculations, and I'm trying to change those to array formulas in the header row so that they automatically apply to new entries without me having to fill them down.

For example, this formula in V1 divides the number of Pages Read in Column U by the total number of pages in Column O to show me the percentage done.

={"% Done"; ARRAYFORMULA(if(ISBLANK($U$2:$U),"",($U$2:$U)/($O$2:$O)))}

Where I'm stuck is converting the complicated formula used to calculate the total cost for the job, which references other sheets. The array formula will go in P1.

Turnaround and Category in Columns B & C are used to find the Rate (Rates Sheet, Column C), but I need it to also look at the Date In (Column F) and select the Rate with the appropriate Start Date and End Date (Rates Sheet, Columns D & E). That way I can change my rates in the future without affecting the Totals for previous jobs.

Upcharge in Column D is used to find any additional cost per page (Upcharges, Column B).

Discount is also a little complicated because there could be a page deduction (Discounts, Column B) or a percentage billed (Discounts, Column C). New Clients get 15 pages off of their first job. A client who provides a Testimonial gets 10% off of a job.

=if(O2>vlookup(E2,Discounts!A:B,2,0),O2-vlookup(E2,Discounts!A:B,2,0),0)*(index(Rates!$C$2:$C$30,match(B2&C2,Rates!$A$2:$A$30&Rates!$B$2:$B$30,0))+vlookup(D2,Upcharges!A:B,2,0))*vlookup(E2,Discounts!A:C,3,0)

PAGES x RATE + UPCHARGE X DISCOUNT

---

PAGES:

if(O2>vlookup(E2,Discounts!A:B,2,0),O2-vlookup(E2,Discounts!A:B,2,0),0)

Compares # of Pages to Pages Off for Discount

• If # of Pages is more than Pages Off, subtracts Pages Off from Pages for calculation
• If # of Pages is less than Pages Off, uses 0 for calculation

RATE:

(index(Rates!$C$2:$C$30,match(B2&C2,Rates!$A$2:$A$30&Rates!$B$2:$B$30,0))

• Looks up Rate using Turnaround and Category
• Needs to also use Date In to look up Rate with appropriate Start Date and End Date

UPCHARGE

vlookup(D2,Upcharges!A:B,2,0))

• Looks up additional Cost Per Page using Upcharge

DISCOUNT

vlookup(E2,Discounts!A:C,3,0)

• Looks up % Billed using Discount

A sandbox version of the Google Sheet is available here. I appreciate any help!