vlookup or index match with multiple conditions (Date closest match) in pivot table [SOLVED]

[DavidLuhut]

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 criterias are:
A Exchange Rate Center - Exact Match Type
Z Exchange Rate Center - Exact Match Type
Effective MAF Date - CLOSEST Match (more explanation below)

Exchange Rate Center are interexchangeable!!! So if the Pivot Table has i.e. Canada in 'A Exchange Rate Center' and USA in 'Z Exchange Rate Center', it's STILL THE SAME even if the search for 'A Exchange Rate Center' is USA and 'Z Exchange Rate Center' is Canada

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, the attached file will give you a better explanation (I have 3 scenarios).

Thank you in advanced for your help!

 

[John Jairo V]

Hi!

Here is an option (With Helper Column - Column A - and Array Formula).
Please comment! Blessings!

 

[DavidLuhut]

Hi John,

Thanks for the help here!

I forgot to mention 1 more detail and that is with regards to the Exchange Rate Centre.

So the rate centre here is actually interexchangeable...in a sense that 100 MILE HOUSE (A Exchange Rate Centre) to KAMLOOPS (Z Exchange Rate Centre) is the same as 100 MILE HOUSE (Z Exchange Rate Centre) to KAMLOOP (A Exchange Rate Centre).

To be honest, your answer is REALLY SUPPERRR CLOSE to what I'm looking for!!! It's just that I'm so focused on the 'closest match' that I completely forgot about mentioning of the rate centre.

 

[DavidLuhut]

By the way, this formula gives a TRUE or FALSE answer that suggest the 'A' and 'Z' rate exchange centre.

=OR(ISNUMBER(MATCH(1,INDEX((Sheet2!A1:A100=A1)*(Sheet2!B1:B100=B1),0),0)),ISNUMBER(MATCH(1,INDEX((Sheet2!A1:A100=B1)*(Sheet2!B1:B100=A1),0),0)))

 

[John Jairo V]

Ok... Please try this file:

 

[DavidLuhut]

John Jairo V,

You're a GENIUS!!! And it works! Although to be honest, I prefer to be a regular formula (non array), but I'll still take this as the solution.

Thank you, thank you!

Since I'm new here, is there a way to make your reputation go up? As well as to make this thread solved?

 

[DavidLuhut]

Quick question John,

May I know why you have the COUNTA(I$3:I$5)<3 formula? What's the purpose of this?

 

[John Jairo V]

Hi again @DavidLuhut.

Since I'm new here, is there a way to make your reputation go up? As well as to make this thread solved?

I'm new in this forum too. I don't know.

Quick question John,
May I know why you have the COUNTA(I$3:I$5)<3 formula? What's the purpose of this?

The purpose is for give a result if we have all the initial data (in I$3:I$5). If you leave any cell of that range in blank, you can't get the result. Blessings!

 

[NARAYANK991]

Hi David / John ,

This forum does not attach any significance to a person's reputation ; of course , there is a Like button available , which you can click if you like the answer that is presented.

John , you have a reputation at Excelxor.

Narayan