Table of Contents
VLOOKUP is a powerful function in Microsoft Excel that allows users to quickly search and retrieve data from a specific column or range of cells. One common use of VLOOKUP is to find data by a specific date. This can be achieved by organizing the data in a table with the dates in one column and the corresponding data in another column. The VLOOKUP function can then be used to search for the desired date and return the associated data from the corresponding column. By using VLOOKUP, users can efficiently find and analyze data based on specific dates, making it a valuable tool for data analysis and management in Excel.
Excel: Use VLOOKUP by Date
There are two valid ways to use a VLOOKUP by date in Excel:
Method 1: Use VLOOKUP with Cell Reference
=VLOOKUP(D2,A2:B9,2,FALSE)This formula looks up the date in cell D2 in the range A2:B9 and returns the corresponding value in column 2 of the range.
Method 2: Use VLOOKUP with DATEVALUE
=VLOOKUP(DATEVALUE("1/14/2023"),A2:B9,2,FALSE)This formula looks up the date 1/14/2023 in the range A2:B9 and returns the corresponding value in column 2 of the range.
Note: The FALSE argument tells Excel to look for exact matches instead of approximate matches.
The following examples show how to use each method in practice with the following dataset in Excel:
Example 1: Use VLOOKUP by Date with Cell Reference
Suppose we would like to look up the date 1/14/2023 and return the corresponding value in the sales column.
We can type the following formula into cell E2 to do so:
=VLOOKUP(D2,A2:B9,2,FALSE)The following screenshot shows how to use this formula in practice:
The VLOOKUP formula correctly returns a sales value of 15.
Example 2: Use VLOOKUP by Date with DATEVALUE
Once again suppose we would like to look up the date 1/14/2023 and return the corresponding value in the sales column.
We can type the following formula into cell E2 to do so:
=VLOOKUP(DATEVALUE("1/14/2023"),A2:B9,2,FALSE)The following screenshot shows how to use this formula in practice:
The VLOOKUP formula correctly returns a sales value of 15.
Note that if we attempted to use the quoted date without the DATEVALUE function, we would receive #N/A as a result instead:
The DATEVALUE function is needed to convert a text date into a serial date number that is recognizable by Excel.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use VLOOKUP to find data by date in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-find-data-by-date-in-excel/
stats writer. "How can I use VLOOKUP to find data by date in Excel?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-find-data-by-date-in-excel/.
stats writer. "How can I use VLOOKUP to find data by date in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-find-data-by-date-in-excel/.
stats writer (2024) 'How can I use VLOOKUP to find data by date in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-find-data-by-date-in-excel/.
[1] stats writer, "How can I use VLOOKUP to find data by date in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use VLOOKUP to find data by date in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.