A way to do the same using Excel worksheet functions only:
1. Add a helper column into your table, where all table rows are numbered <e.g. =(ROW()-HeaderRowNo)>;
2. Add a helper column into your table, where all table rows for every supplier are ranked by rate date <using COUNTIFS like =COUNTIFS(SupplierRange, SupplierInRow, RateDate, "<=" & DateInRow)>;
To get the latest rate for any supplier on any date, you have to combine INDEX and SUMIFS functions to return the rank from proper row. The formula will be too complex for me to write it down on fly!

Thank you Arvil.
It does as you say seem to be a lot more complex than I thought it would be but i appreciate you taking the time to look into it for me. If you do find some time and would be able to work it out, it would be fantastic if it could be added to the excel I provided so I could see what is done in a working example.

Rich