Have you ever found yourself in the situation where you needed to Vlookup a date in a timestamp column in Google Sheets? It can be a bit tricky, but fear not! I’m here to guide you through the process, step by step. So grab your cup of coffee, sit back and relax as I reveal the secrets to Vlooking up dates in timestamp columns.
Converting the Timestamp Column to a Date Column
The first important thing to know is that we need to convert the timestamp column to a date column. Why, you might ask? Well, a timestamp contains both the date and time of the day. And in this case, we want to Vlookup a date, not a timestamp.
There are two options to accomplish this. We can use either the INT function or the toDate function. The INT function is commonly used by Google Sheets pros, while the toDate function is a scalar function in Query.
Using the Converted Column as the Vlookup Range
After converting the timestamp column, the next step is knowing how to use it with the other columns in the table as the Vlookup range. In other words, we need to ensure that the first column in the range is a date column.
Let’s take a look at an example to understand this better.
Example: Vlookup Problem in a Timestamp Column
Assuming we have a table with three columns – Timestamp, ID, and Amount. Our Vlookup search key is a date, so we need to convert the timestamp values to dates and combine them with the other two columns to create the range for Vlookup.
To do this, we can use the following formula:
=query(A1:C6,"Select toDate(A),B,C",1)The formula converts the first column (timestamps) to dates, while keeping the other two columns unchanged. This formula will serve as the range for our Vlookup.
Now, let’s look at the Vlookup syntax:
VLOOKUP(search_key, range, index, [is_sorted])In our case, since the first column is a timestamp column, we want to Vlookup only the dates in that column, not the timestamps. We can use either the toDate function or the INT function to achieve this.
Vlookup Using toDate
If you prefer using the toDate function, here’s how you can do it:
=VLOOKUP(E1,query(A1:C6,"Select toDate(A),B,C",1),2,0)To return the value from the third column instead, you can use this formula:
=VLOOKUP(E1,query(A1:C6,"Select toDate(A),B,C",1),3,0)And if you want to return values from both the second and third columns using a single Vlookup, try this formula:
=ArrayFormula(VLOOKUP(E1,query(A1:C6,"Select toDate(A),B,C",1),{2,3},0))
In the above example, the additional ArrayFormula is necessary as we want to return two values from a non-array Vlookup. By using Query as the range expression, we can effectively solve the problem of Vlooking up a date in a timestamp column.
One important note: if you want to hard code the search key, use the DATE function instead of entering the date directly. For example:
=ArrayFormula(VLOOKUP(date(2020,8,27),query(A1:C6,"Select toDate(A),B,C",1),{2,3},0))Vlookup Using INT
The INT function is another popular option among Google Sheets pros for Vlooking up a date in a timestamp column. Here’s how you can do it:
First, create the virtual range using the INT function:
={int(A2:A6),B2:C6}This formula converts the timestamps to dates using the INT function and combines them with the other two columns as an array using curly brackets.
When using this range with Vlookup, remember to use the ArrayFormula, regardless of whether the Vlookup returns one value or multiple values. For example:
=ArrayFormula(vlookup(E1,{int(A2:A6),B2:C6},2,0))This formula would return “KL604000” as the ID in the second column against the date “27/08/2020”.
Vlookup When the Timestamp Isn’t the First Column
What if the timestamp column isn’t the first column in the table? In this case, we need to adjust our approach.
Let’s say we have a shuffled table like this:
| ID | DateTime | Amount |
|---|---|---|
To create the range for Vlookup, we can use the Query toDate or INT function as follows:
Query toDate range:
query(A1:C6,"Select toDate(B),A,C",1)INT function range:
{int(B2:B6),A2:A6,C2:C6}The Vlookup formulas will be the same as the earlier examples corresponding to these functions.
And that’s all there is to it! You now know how to Vlookup a date in a timestamp column in Google Sheets. I hope this guide has been helpful to you. If you have any questions, feel free to ask. Happy Vlooking!
Learn more about Google Sheets and other useful tips at Crawlan.com.
