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.

Attached Files

Test Compare Dates to get Value.xlsx (13.7 KB, 4 views)

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:
I myself can't help with code for this, as I haven't access to MS Office for next 3 weeks!

Thank you Arvil -I am afraid I dont know VBA at all so would not be able to do this

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