I need help creating a formula to pull back data and pricing based on tiers

Hi,

So I am trying to create a formula to pull back information in my lookup sheet based on the ship-to number entered in yellow. I need to pull information for brand, size, description, pattern ply, cmpd, tra, 78 name, and price based on the ship-to's pricing tier sheet. I have special pricing for a certain date range for one ship-to on some of his items numbers (#) is there a way to have it override the tier pricing when the special price is cheaper and an applicable date is entered in yellow. I have attached a sample file that I have been working on.Thank you in advanced for any help I have been trying several different formulas and had no luck.

Can a moderator delete this post I way over complicated this.