Otis is looking for a date but he can’t find it! Let’s review four solutions that help Otis find a date.
What Is Otis Looking For?
The date that Otis is looking for is 7/13/2016 found in cell B1.
Is It a Syntax Error?
Otis is using this vlookup =VLOOKUP(B1,Table1,2,0)
- lookup_value cell B1 is correct (we are looking for this value)
- table_array is correct (looking in the left most column of ‘Table1’)
- col_index_num is correct (2nd column in ‘Table1’). This is where the answer value comes from
- [range_lookup] is correctly set to false as we want an exact match (not binning or closest match)
So, it is not caused by a vlookup syntax error.
Note that our table_array is all of ‘Table1’ because it only has two columns. Otis is looking for his date in the 1st column and the 2nd column returns the answer.
Let’s Take a Look at the Data
If we scan down column ‘Date‘ we see 7/13/2016 in bold text. The expected answer is to the right.
What’s the Problem?
No Otis, it’s not you. The problem is that the date values are text!
Our lookup value is a proper date. A date in Excel is a serial number. Changing the format of cell B1 to number gives you 42564 (which proves that it’s a date!). It’s the number of days since the beginning of recorded time in Excel (beginning of year 1900).
The values in field ‘Date’ are text values! Underneath, these values are not serial numbers. They are text! They look like date values but they are not! It doesn’t matter what kind of make-up we put on top of text values. The formatting won’t convert text to date. Yes, the dating scene is confusing for poor Otis.
4 Different Solutions!
Otis’s friend and Data Analyst Raj Singh will help us.
Solution 1
=VLOOKUP(TEXT(B1,”m/dd/yyyy”),Table1,2,0)
We wrap the lookup value with Excel’s TEXT function. We use this format “m/dd/yyyy” so that the resulting text value matches the text values in our data. Now we are comparing apples to apples!
Solution 2
Highlight the values in column F and use Excel’s ‘Text to Columns’ tool (found in ‘Data’) to convert text values to date values. Notice above how we selected ‘Date’ and the correct date format (MDY = Month, Day, Year).
Solution 3
=DATEVALUE([@Date])
Our simple vlookup will work just fine if we add a helper column to convert the text values to date values (we just have to change the ‘table_array’ to look in the new helper column to find 7/13/2016)
Solution 4
{=VLOOKUP(B1,Table4*1,2,0)}
The ‘Table_Array’ Table4*1 multiplies each value by 1. This converts each text value to a date! This also turns our vlookup into an array formula so we have to press CONTROL SHIFT ENTER (instead of the normal ENTER). Doing so gives us the special curved array brackets.
Solution 5?
Can you think of a fifth solution? If so, please leave a comment below. Otis thanks you!
Download My Excel file
Download Otis Can’t Find a Date or via my OneDrive (filename ‘Otis Can’t Find a Date‘)
About Me
My name is Kevin Lehrbass. This is my personal blog about Microsoft Excel. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.
Hundreds of millions of people use Excel everyday. Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and my blog posts.
Away from Excel I enjoy playing with my dog Cali (she doesn’t like Excel), learning Spanish, playing Chess and drawing nerds (it’s relaxing and gives me a break from data!)