I'd create an UDF (user defined function) in VBA, which returns a latest value from Rate column from all records for certain SupplierID/Supplier value.
Then you simply use the formula in cell you want the rate returned. Something like:
Code:
=UdfName(RateSourceRange, RateDateSourceRange, YourDate, SupplierSourceRange, YourSupplier)
I myself can't help with code for this, as I haven't access to MS Office for next 3 weeks!