vlookup or index match with multiple conditions (Date closest match) in pivot table
Hello Excel Experts,
I have a pivot table and i am trying to pull cell values based on multiple criteria, and the data i am trying to pull is from a pivot table.
The matching criteria are:
A Exchange Rate Center - Exact Match Type
Z Exchange Rate Center - Exact Match Type
Effective MAF Date - CLOSEST Match (more explanation below)
The 'Effective MAF Date' is where we had an agreement. So it can be any random date. But the rule is, assuming for a particular Route (Exchange Rate centre from A to Z) has an agreement say in Jan 1, 2010 and in the future, the same Route has an ammendment say in Mar 15, 2013. If a service was required say Aug 8, 2011. The desired result/outcome of MAF and mileage will be taken as per Jan 1, 2010 agreement NOT Mar 15, 2013.
Given the same agreement dates (Jan 1, 2010 and Mar 15, 2013), if the required service date was AT and/or AFTER Mar 15, 2013, then I would need to have the MAF and mileage that was per Mar 15, 2013 agreement. If it's BEFORE Jan 1, 2010, I would want it to be 'UA'.
Re: vlookup or index match with multiple conditions (Date closest match) in pivot table
The lack of response could partly be due to the data setup, that pivottable would be a mess to make formulas from.
I'm just supplying a crude quickfix here involving some helper columns, see if this is something you can work with.
Bookmarks