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:

Date	AUD	EUR	CDN	USD
Jan-14	0.541947	0.839631	0.572279	0.610054
Feb-14	0.534188	0.817929	0.545643	0.602991
Mar-14	0.540453	0.82291	0.542123	0.598301
Apr-14	0.547375	0.83654	0.53801	0.601395
May-14	0.553618	0.824266	0.539869	0.596019
Jun-14	0.54609	0.809585	0.541888	0.592487
Jul-14	0.551268	0.801282	0.542977	0.590388
Aug-14	0.553894	0.790014	0.546329	0.586786
Sep-14	0.559378	0.798212	0.548727	0.600745
Oct-14	0.554232	0.79384	0.559347	0.612557
Nov-14	0.548396	0.789079	0.555772	0.622975
Dec-14	0.521921	0.800512	0.562936	0.638325

<tbody>
</tbody>

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..