This post titled Lookup Dates and Return Currency Rates is about GoogleFinance array use in Google Sheets.

I have a column with a few dates.

In the next column, I want to find out the currency exchange rates on that dates. It is what I’m explaining here.

With the help of Vlookup and GoogleFinance, we can Lookup dates and return currency rates in an Array.

The formula is a little complex involving three functions – GoogleFinance, Vlookup, and Query.

But there is no need to worry. You can use the formula out of the box.

You are going to learn here the use of the GoogleFinance function in an Array.

How to Lookup Dates and Return Currency Rates in an Array (GoogleFinance Array Use)

In the below example screenshot, you can see there are a few random dates in column A.

In column B, you can see the currency exchange rates on that particular dates.

There is one GoogleFinance formula in an array form in cell B2.

It automatically fetches the currency exchange rates for all the dates in column A.

Here is that custom formula.

=ArrayFormula(
     if(
        len(A2:A),
        VLOOKUP(
           A2:A,
           query(
              GOOGLEFINANCE(
                 "CURRENCY:USDinr", "price", DATE(2017,12,1), DATE(2017,12,31), "DAILY"
              ),
              "Select todate(Col1), Col2 label todate(Col1) '', Col2 ''"
           ),2
        ),
     )
)

Please note that there is a limitation in entering the dates in column A.

Here in this example, you can enter the dates that fall in December 2017.

If you want to Lookup the currency exchange rates of any other period, you should change the dates in the formula first.

Because in the above formula, the start date is 1-12-2017, and the end date is 31/12/2017.

You may change the period in the formula. Accordingly, you can use the dates in column A to fetch the exchange rates.

Formula Explanation

As I told you above there are three major formulas in use here.

Let me explain the role of each of those formulas here.

1. Google Finance to Import Historical Currency Exchange Rates

=GOOGLEFINANCE("CURRENCY:USDinr", "price", DATE(2017,12,1), DATE(2017,12,31), "DAILY")

This formula returns the currency exchange rates from USD to INR for December 2017.

That’s why I have told you above, in column A, the dates should be between 01/12/2017 to 31/12/2017.

If you want dates that fall in any other month and year, change the relevant parts in this Google Finance formula.

If you want to learn how to use the Google Finance function, please check my Google Sheets Functions Guide.

Apply this formula in any cell in a blank column to understand what it returns.

You would get a table like this.

I’ve only taken the screenshot of the top part of the data as it’s a bit lengthy.

I am going to use this data in a Vlookup as the range.

2. Vlookup to Lookup Dates and Return Currency Rates in an Array

Here I want to use the above data in Vlookup.

Syntax: VLOOKUP(search_key, range, index, [is_sorted])

We already have the lookup search_keys in column A.

The above data returned by GoogleFinance is our range in Vlookup.

You May Like: Learn Vlookup in Array Use in Google Sheets

If you carefully analyze the historical data returned by the GoogleFinance formula above, you can understand one thing.

What’s that?

We can’t use the above data in Vlookup as the range because the dates returned by the GoogleFinance formula are not clean ones.

The dates are timestamps!

But our search key dates in column A are clean ones without time components.

So we should first clean the historical data returned by GoogleFinance in Google Sheets.

Here comes the Query handy.

There are different methods to remove time from the timestamp in Google Sheets.

Earlier I’ve posted a dedicated tutorial on this. There I’ve used INT, TO_DATE functions. But I am not following that tutorial here.

Must Check: How to Extract Date From Time Stamp in Google Sheets.

Here I am using the toDate scalar function in Google Sheets Query.

3. Query To Remove Time form Time Stamp and Clean the Date

The below formula part in our master formula deals the date cleaning.

query(GOOGLEFINANCE("CURRENCY:USDinr", "price", DATE(2017,12,1), DATE(2017,12,31), "DAILY"),"Select todate(Col1), Col2 label todate(Col1) '', Col2 ''")

This Query formula removes time from the timestamp in Google Sheets. Further, it removes the labels too.

We have a clean data range to use. That is one column containing dates and the other column containing the exchange rates.

We have completed our major part.

Now let’s see how to lookup dates and return currency Rates in an array.

In Vlookup, I’ve used the range A2:A as the search_key, then the above Query and GoogleFinance combo formula is the range, and column 2 is the index number that contains the exchange rates.

The above is the core part of the formula.

See the master formula again. There you can see that I’ve used the ArrayFormula function.

It’s because we have to search multiple dates in column A, not a single search key. So ArrayFormula is a must.

The use of IF and LEN is to speed up the execution of the formula by limiting the lookup only to the rows containing the search keys. 

That’s all.

Conclusion

I know this is tutorial is not for newbies.

But if you first individually learn/understand the functions used in the formula, you can quickly grasp how to lookup dates and return currency rates in an array in Google Sheets.

I hope you’ve enjoyed it. See you again with another spreadsheet tutorial. Thanks for the stay!