How to Apply VLOOKUP by Date in Excel

Get FREE Advanced Excel Exercises with Solutions!

We use VLOOKUP by date to find dates in a data table. It is very useful to find the values corresponding to particular dates from a dataset. The value we lookup for in using the VLOOKUP function is called the lookup value. Therefore, the date is the lookup value when we apply VLOOKUP by date. This article shows how to apply VLOOKUP by date in the simplest way. The following picture gives an idea of how VLOOKUP by date works.

Vlookup by Date


Download Practice Workbook

You can download the practice workbook from the download button below.


Apply VLOOKUP by Date in Excel

We are going to use the following dataset to illustrate how to apply VLOOKUP by date in excel. The dataset contains the sales amounts corresponding to different dates.

Assume, you want to enter the date in cell E5 to find the corresponding amount of sales in cell F5.

You can easily do that by following the steps below.

Steps

1. First, enter the following formula in cell F5:

=VLOOKUP(E5,B5:C10,2,FALSE)

2. Don’t worry about the #N/A error.

entering vlookup formula

3. Now, enter a date in cell E5 to get the desired sales amount as follows.

Vlookup by date applied successfully

4. Be cautious about the date format while entering a date. You must enter the date in the same format as in the dataset which is MM/DD/YYYY in this case.

5. If the date you are looking for doesn’t exist in the dataset, it will show a #N/A error. For example, enter 2/4/2022 instead of 2/4/2022 and you will see the following.

How does the Formula Work?

1. The VLOOKUP function consists of the following arguments:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

2. First, it asks for the value to lookup(lookup_value). Then the range of data to look up(table_array). After that, it asks the column number(col_num_index) where the return value exists in that range. Finally, the range_lookup argument asks whether you want to look for an Approximate Match(TRUE) or an Exact Match(FALSE).

3. If we compare this with the formula entered in cell F5 then we get,

  • Lookup_value = E5 which contains the date we look for.
  • Table_array = B5:C10 which contains the dataset.
  • Col_index_num = 2 which is the second column of the dataset containing the sales amounts we wanted to find out.
  • Range_lookup = FALSE, meaning looking for an exact match.

Read More: How to Use VLOOKUP to Find a Value That Falls Between a Range


Perform VLOOKUP to Return Date in Excel

Now, what if we want to find the date corresponding to the maximum sales amount? Let’s follow the steps below for that.

Steps

1. First, enter the following formula in cell E5:

=MAX(C5:C10)

2. Then the MAX function in this formula will return the maximum sales amount from the Sales column.

entering the MAX function

3. After that, enter the following formula in cell F5:

=VLOOKUP(E5,B5:C10,1,FALSE)

4. But this gives the #N/A error.

Vlookup by date shows #N/A error

5. It is because the value we are looking for remains in the second column in the table array.

6. To fix this problem, let’s interchange the Date and the Sales columns.

7. Then change the formula in cell E5 to the following one:

=MAX(B5:B10)

8. The new dataset will look as follows.

9. After that, apply the following formula in cell F5:

=(VLOOKUP(E5,B5:C10,2,FALSE)

10. This will return the date as a number.

11. The simplest way to fix this problem is by changing the date format of that cell from the Home tab.

changing number format in excel

12. An alternative way to do that is by applying the following formula instead of the earlier one in cell F5.

=TEXT(VLOOKUP(E5,B5:C10,2,FALSE),"D-MMM-YY")

13. The TEXT function in this formula will change the value obtained from the VLOOKUP formula to date format.

entering the TEXT function

Read More: Lookup Value in a Range and Return in Excel (5 Easy Ways)


Things to Remember

  • You must enter dates in the same format as the dates are in the dataset.
  • The lookup value must be in a column earlier than the column which contains the return value.
  • Make sure the text format is inside double quotes (“”) in the TEXT function.

Conclusion

Now you know how to apply VLOOKUP by date in excel. Please use the comment section below for further queries or suggestions. You may visit our ExcelDemy blog to learn more about excel.


Related Articles

Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo