2 Helpful Examples to VLOOKUP by Date in Google Sheets

The VLOOKUP is one of the most useful options in Google Sheets and Excel. This option is useful for searching and extracting information from a large dataset. Several functions like the VLOOKUP, XLOOKUP, or a combination of INDEX and MATCH functions, etc. can perform the VLOOKUP operation. While making datasheets in Google Sheets or Excel, we often use date values. So, a ques arrives, “Can we apply VLOOKUP by Date?”. The answer is “Yes”. Here, in this article, I’ll demonstrate 2 examples to apply VLOOKUP by date in Google Sheets.

Table of Contents

A Sample of Practice Spreadsheet

You can copy our practice spreadsheets by clicking on the following link. The spreadsheets contain an overview of the datasheet and an outline of the described examples to apply VLOOKUP by date.

Apply VLOOKUP by Date

2 Helpful Examples to Apply VLOOKUP by Date in Google Sheets

First, let’s get familiar with our dataset. The dataset contains starting and ending dates of several projects and the estimated budget for these projects. We’ll extract the estimated budget data from the dataset based on date values. Note that the dataset here is sorted.

Example 1: Using VLOOKUP by Date

Here, we’ll do a simple VLOOKUP based on a date value. We have to modify our datasheet like the following and enter any starting date from Column B in Cell C10.

Steps:

First, select Cell C11.
Then, type in the following formula:

=VLOOKUP(C10,B5:D8,3,0)

Finally, press Enter key to get the required result.

Example 2: Applying VLOOKUP Between Two Dates

In the previous example, we used the exact date for looking up. But can we use any random date between two specific values? Yes, we can. Go through the following three examples to know how.

Also, instead of a starting date, we require a random date now. Therefore, modify the datasheet as follows:

Example 2.1: Implementing for Sorted Date Range

The VLOOKUP function will select the nearest match when it can’t find an exact match for the search key in a sorted data range.

Steps:

First, select Cell C11.
Afterward, type in the following formula:

=VLOOKUP(C10,B5:D8,3,1)

Finally, press Enter key to get the required result. As you can see, the Date 12/16/2022 doesn’t exactly match with any of the values in range B5:C8. But it falls between the dates 12/13/2022 and 12/31/2022. Hence, the VLOOKUP function has extracted data from Row 8.

Read More: Google Sheets Vlookup Dynamic Range

Example 2.2: Employing for Unsorted Date Range

The date range was sorted in the previous example. But what happens when the dataset is not sorted? Will the previous method work if the data range is not sorted? The answer is No, it won’t. For example, consider the following modification in the dataset. The date range is now unsorted.

Now, if we apply a formula like the previous method, we see an #N/A error. However, you can still perform a VLOOKUP using a combination of the ARRAYFORMULA and LOOKUP functions.

Steps:

First, select Cell C11.
After that, type in the following formula:

=ARRAYFORMULA(LOOKUP(12,1/($B$5:$B<=C10)/($C$5:$C>=C10),$D$5:$D))

Finally, press Enter key to get the required value.

Formula Breakdown

LOOKUP(12,1/($B$5:$B<=C10)/($C$5:$C>=C10),$D$5:$D)

First, the LOOKUP function searches for a random value of 12 provided as the search key in the search range. The search range here will be 1 or #DIV/0! error. The LOOKUP function will select the value for which it will find the last match. Finally, it returns the value from the second row of the result range D5:D.

ARRAYFORMULA(LOOKUP(12,1/($B$5:$B<=C10)/($C$5:$C>=C10),$D$5:$D))

The ARRAYFORMULA function helps to deal with the arrays in LOOKUP function arguments.

Example 2.3: Combining VLOOKUP and SORT Functions

If you find the previous method a little complex, you can simply combine the SORT function with the VLOOKUP function to virtually sort the date range.

Steps:

First, select Cell C11.
Afterward, type in the following formula:

=VLOOKUP(C10,SORT(B5:D8),3,1)

Finally, press Enter key to get the required result.

Formula Breakdown

SORT(B5:D8)

First, the SORT function sorts the rows of the range B5:D8.

VLOOKUP(C10,SORT(B5:D8),3,1)

Afterward, the VLOOKUP function will return the content of the third column in the sorted range where the closest match is found for the search key in Cell C10.

Similar Readings

What to Do When VLOOKUP Is Not Working with Dates in Google Sheets

There can be various reasons behind the VLOOKUP function not working with date ranges. We have already acknowledged one of the reasons in Example 2.2 where the dataset was not sorted. Example 2.2 and Example 2.3 provided solutions for that problem. Here, we will discuss a different problem.

As we have seen in Example 2.1, the VLOOKUP function selects the nearest value if an exact match is not found in a sorted dataset. But in the following dataset, an #N/A error is returned for that similar formula.

The reason for the error is that the date range is formatted as “Plain Text” instead of “Date”. There can be reasons behind formatting the dates as plain text values. So, can we do VLOOKUP without changing the format? The answer is a resounding Yes! We can join the INDEX, MATCH, and DATEVALUE functions to get the required result. However, if the plain text format isn’t important, then simply changing the data format will work for applying the VLOOKUP function. We will discuss both of these solutions below.

Solution 1: Using DATEVALUE Function

First, select Cell C11.
Then, type in the following formula-

=INDEX(D5:D8,MATCH(C10,DATEVALUE(B5:B8),1))

Finally, press Enter key to get the required result.

Formula Breakdown

DATEVALUE(B5:B8)

First, the DATEVALUE function converts the date strings in the range B5:B8 into date values.

MATCH(C10,DATEVALUE(B5:B8),1)

Afterward, the MATCH function returns the relative position of the cell in range B5:B8 where a match is found for the search key provided in Cell C10.

INDEX(D5:D8,MATCH(C10,DATEVALUE(B5:B8),1))

Finally, the INDEX function returns the content of the subsequent cell in range D5:D8, where it finds a match for range B5:B8.

Solution 2: Applying Date Format

First, select the range B5:C8 and change the data format to “Date”.

Afterward, type in the following formula in Cell C11:

=VLOOKUP(C10,B5:D8,3,1)

Finally, press Enter key to get the required result.

Things to Be Considered

The VLOOKUP function will select the nearest match when it can’t find an exact match for the search key in a sorted data range.
The combination of INDEX and MATCH functions will work only if we provide a sorted date range.
The LOOKUP function will select the value for which it will find the last match.
The VLOOKUP function can’t execute properly when it can’t find an exact match in an unsorted dataset.
Remember to format both the lookup value and range as a date.

Conclusion

This concludes our article to learn how to apply VLOOKUP by date in Google Sheets. I hope the described examples were sufficient for your requirement. Feel free to leave your thought on the article in the comment box. Visit our website OfficeWheel.com for more helpful articles.

2 Helpful Examples to VLOOKUP by Date in Google Sheets

A Sample of Practice Spreadsheet

2 Helpful Examples to Apply VLOOKUP by Date in Google Sheets

Example 1: Using VLOOKUP by Date

Example 2: Applying VLOOKUP Between Two Dates

Example 2.1: Implementing for Sorted Date Range

Example 2.2: Employing for Unsorted Date Range

Example 2.3: Combining VLOOKUP and SORT Functions

What to Do When VLOOKUP Is Not Working with Dates in Google Sheets

Solution 1: Using DATEVALUE Function

Solution 2: Applying Date Format

Things to Be Considered

Conclusion

Related Articles

Seemanto Saha

Leave a reply Cancel reply