Problem #1 VLOOKUP Doesn’t Work
One of the more common reasons VLOOKUP fails to perform as expected is when the data we are searching for is interpreted as a number, but the list we are searching through has numbers stored as text.
The numbers look the same to our eyes, but Excel’s “eyes” are more discerning. If the data types don’t coincide, Excel will fail to find a match.
Numbers are sometimes converted to text to exclude the number from certain calculations.
A common method of conversion is to preface the number with an apostrophe, also referred to as a single-quote.
Another common conversion method is to format the cell as text prior to entering the data.
Any value placed in the cell will be perceived by Excel as text.
The biggest challenge is when neither of these techniques have been used but the problem persists.
This is usually the result of a copy/paste action from some external data source.
Demonstrating the Problem
Using the list below; let’s enter a value in cell D2 to search for one of the code numbers in column A of the table.
We will then create a VLOOKUP formula to search for the value in cell D2 and return a description from the table and place it in cell D3.
=VLOOKUP(D3,A2:B11,2,False)
NOTE: If you are unfamiliar with the VLOOKUP function, see the link below this tutorial for a video detailing the operations and use of the VLOOKUP function.
Notice that the result of the VLOOKUP formula is a #N/A error message.
This is because our value in cell D2 is a number while the matching “number” in cell A4 is text. They look the same to our eyes, but not Excel’s.
Solving the Problem
We will perform a bulk conversion of the data in column A of our table.
Step 1: Select all the data in column A (A2 through A11)
Step 2: Select Data (tab) -> Data Tools (group) -> Text to Columns
Step 3: In the Convert Text to Columns Wizard, click Finish
The VLOOKUP formula now performs as expected.
The Text to Columns tool transformed all our numbers stored as text into actual numbers.
Problem #2 Dates aren’t Recognized as Dates
The below table has dates imported from SAP.
The numbers in column B appear to be dates. Although they may not be formatted in the best way, we are still able to understand that the date in cell B4 is August 1, 2018.
A simple way to determine how Excel is interpreting the data is to access the filter dropdown list.
Notice that the data is just listed in a flat list. The data is not being grouped by any year/month/day hierarchy.
Excel is smart when it comes to changing the filter options based on the perceived data type. If the data looks like text, we are presented with Text Filters. If the data looks like dates, we are presented with Date Filters, and if the data looks like numbers, we are presented with Number Filters.
Text Filters:
Date Filters:
Number Filters:
As a side test, if we attempt to extract the year from the date in cell B4, we are returned an error message.
=YEAR(B4)
If we attempt to format the dates in column B, the formatting fails.
Solving the Problem
We will use the same Text to Columns tool to solve this problem.
Step 1: Select the dates (B4 through B19)
Step 2: Select Data (tab) -> Data Tools (group) -> Text to Columns
We need to add an additional step for this solution. We can’t accept all the default settings of the Text to Columns tool and have it perform as previously witnessed.
Step 3: Click the Next button two times to advance to Step 3 of 3 in the Convert Text to Columns Wizard.
Set the Column Date Format option to Date and select the appropriate date format for the data. In our case, we will select the YMD format.
Click Finish to be presented with your newly converted dates.
Testing the Filters
If we select the filter dropdown from the date column, we see that Excel is interpreting the data as dates and we have a year/month/day hierarchy to work with in data selection.
Additional Information
For information regarding the purpose and use of the VLOOKUP function, see the video link below.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
