Trying to obtain Value that falls in a Date Range

[Y021ahe]

I have created a table attached that shows the Energy Supplier rates at different dates - I am trying to pull out the rate for an individual supplier - I have different tabs for each supplier that i want to pass the relevant value into, but i cant get the VLOOKUP or MAX(IF or whatever formula to search through the date ranges and pull back the required rate



So If I look at Rate 1 I am want to input a random date and pull back the value including VAT from Column C comparing it to if its greater than the value in column A .. this may be simple but I cant get it to work

The other complication is that I am on MACOS Excel 2016 so I dont have the MAXIFS or XLOOKUP function to use.

Any help would be appreciated.. I am a novice so am explanation would also be really appreciated.