Return value from multiple entries, within date range, multiple columns, and conditions

I'm sure this would work better in VBA and / or with using Pivot Tables but do to the way this must be used I need to find a formula solution.

I have a list of repeating products with different dates and anywhere from 0-2 prices (columns) per date. I need to return the most current price within a date range from all entries. Note: MSRP takes precedence over MAP if both exist.

Any thoughts as to best way to go about this?

Attachment 174221