#1
|
|||
|
|||
Trying to obtain Value that falls in a Date Range
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. |
#2
|
|||
|
|||
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) |
#3
|
|||
|
|||
Thank you Arvil -I am afraid I dont know VBA at all so would not be able to do this
|
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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 |
Tags |
date range compare rates |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find if Date range falls within another range | Triadragon | Excel | 3 | 05-02-2016 11:48 AM |
Check if a Date falls within a date range | OTPM | Excel | 7 | 02-03-2016 09:11 PM |
Filter by a Month that falls within a date range | MattG1225 | Excel | 2 | 12-03-2015 07:44 AM |
HELP: Return a value with a date that falls between two other dates | hionman | Excel | 5 | 11-12-2014 09:56 AM |
How to obtain maximum value of an excel column with a large range? | sirkay | Excel | 4 | 02-23-2014 08:17 AM |