Not sure if I should use multiple vlookup, index or IF functions

Sample of data show below. For each BCN on the date of sale I need to return the Sale Price on that date. For example, if the BCH typed in is 2026002 and the date of sale is 3/5/2003 I would need to return .104 as the price per unit; If the BCN was 2034602 and sale occurred in 7/2007 I would need to return a sale price of .1000. How can I do this?

BCN supplier Sale $ Full $ Effective Date
2026002 GENERIC 500000 0.0959 0.0959 9/2/2004
2026002 GENERIC 500000 0.0959 0.0959 9/1/2004
2026002 GENERIC 500000 0.104 0.104 12/1/2002
2026002 GENERIC 500000 0.1098 0.1098 12/1/2000
2026002 GENERIC 500000 0.0578 0.1098 8/1/1996
2026002 GENERIC 500000 0.1098 0.1098 2/1/1995
2026002 GENERIC 500000 0.0464 0.1098 1/1/1995
2026002 GENERIC 500000 0.033 0.1098 8/1/1994
2026002 GENERIC 500000 0.0246 0.1098 3/1/1994
2034602 VICTORY 0.1287 0.3762 9/27/2009
2034602 VICTORY 0.1087 0.3062 9/26/2009
2034602 VICTORY 0.1000 0.3000 7/21/2005

Assuming that each combination of BCN and date occurs only once for a supplier, you could try the SUMIFS function with the sale price column as the sum column and the BCN number and data columns as criteria with the supplier column to make sure that one one row is returned.

Each bcn and date combination does only occur once but for example 2026002 had a sale price of .0246 effective from 3/1/1994 until the next change occurs which means that price goes only until 7/31/1994 so it would only occur at that price if the date is greater than 3/1/1994 but less than 8/1/1994 and I'm not sure how to account for that