vlookup using months and years help please, or is pivot table setup better here?

[gadgetic]

Hi

I have a table of exchange rates sorted by "month-year", here is a small sample:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl66, width: 64, align: center"]Date[/TD]
[TD="class: xl66, width: 64, align: center"]AUD[/TD]
[TD="class: xl66, width: 64, align: center"]EUR[/TD]
[TD="class: xl66, width: 64, align: center"]CDN[/TD]
[TD="class: xl66, width: 64, align: center"]USD[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jan-14[/TD]
[TD="align: right"]0.541947[/TD]
[TD="align: right"]0.839631[/TD]
[TD="align: right"]0.572279[/TD]
[TD="align: right"]0.610054[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Feb-14[/TD]
[TD="align: right"]0.534188[/TD]
[TD="align: right"]0.817929[/TD]
[TD="align: right"]0.545643[/TD]
[TD="align: right"]0.602991[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Mar-14[/TD]
[TD="align: right"]0.540453[/TD]
[TD="align: right"]0.82291[/TD]
[TD="align: right"]0.542123[/TD]
[TD="align: right"]0.598301[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Apr-14[/TD]
[TD="align: right"]0.547375[/TD]
[TD="align: right"]0.83654[/TD]
[TD="align: right"]0.53801[/TD]
[TD="align: right"]0.601395[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]May-14[/TD]
[TD="align: right"]0.553618[/TD]
[TD="align: right"]0.824266[/TD]
[TD="align: right"]0.539869[/TD]
[TD="align: right"]0.596019[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jun-14[/TD]
[TD="align: right"]0.54609[/TD]
[TD="align: right"]0.809585[/TD]
[TD="align: right"]0.541888[/TD]
[TD="align: right"]0.592487[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jul-14[/TD]
[TD="align: right"]0.551268[/TD]
[TD="align: right"]0.801282[/TD]
[TD="align: right"]0.542977[/TD]
[TD="align: right"]0.590388[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Aug-14[/TD]
[TD="align: right"]0.553894[/TD]
[TD="align: right"]0.790014[/TD]
[TD="align: right"]0.546329[/TD]
[TD="align: right"]0.586786[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Sep-14[/TD]
[TD="align: right"]0.559378[/TD]
[TD="align: right"]0.798212[/TD]
[TD="align: right"]0.548727[/TD]
[TD="align: right"]0.600745[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Oct-14[/TD]
[TD="align: right"]0.554232[/TD]
[TD="align: right"]0.79384[/TD]
[TD="align: right"]0.559347[/TD]
[TD="align: right"]0.612557[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Nov-14[/TD]
[TD="align: right"]0.548396[/TD]
[TD="align: right"]0.789079[/TD]
[TD="align: right"]0.555772[/TD]
[TD="align: right"]0.622975[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Dec-14[/TD]
[TD="align: right"]0.521921[/TD]
[TD="align: right"]0.800512[/TD]
[TD="align: right"]0.562936[/TD]
[TD="align: right"]0.638325[/TD]
[/TR]
</tbody>[/TABLE]

In another sheet I have sales entries in different currencies, I would like to pull the correct exchange rate from the above table using month and year from the sales entry.

So for example if I had a sale on 15th July 2014 in CDN 35.00

I need a formula to correctly find the currency exchange rate to use is 0.542977

I have separated the sales entry values into different columns by currency. So the CDN filter is already done. I guess I could make x4 vlookup tables for each currency, but I still have the issue of searching by month-year...

Many Thanks

Gurpreet

 

[Rorylfc]

The formula you need is:

=INDEX($B$2:$E$13,MATCH(A17-DAY(A17)+1,$A$2:$A$13,0),MATCH(B17,$B$1:$E$1,0))

B2:E13 = The Exchange Rate Values
A17 = The Data of the Transaction
A2:A13 = The month Dates
B1:E1 = The Currency Types

Let me know if this needs further clarity

 

[gadgetic]

The formula you need is:

=INDEX($B$2:$E$13,MATCH(A17-DAY(A17)+1,$A$2:$A$13,0),MATCH(B17,$B$1:$E$1,0))

B2:E13 = The Exchange Rate Values
A17 = The Data of the Transaction
A2:A13 = The month Dates
B1:E1 = The Currency Types

Let me know if this needs further clarity

Hi

That worked a treat. My sales transaction date also had time in it, but after truncating for that, worked superb.

Thank You for your help.

First time I used the INDEX function, nice tool to have.

Gurpreet

 

[Rorylfc]

Glad it works as required..