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'.
I hope my explanation is clear enough, if not feel free to ask. And thank you in advanced for your help!vlookup or index match with multiple conditions in pivot table.xlsx
Bookmarks