VLOOKUP Not Working?

Convert Text to Date?

(Excel Text to Columns)

When working with imported data in Excel, it is not uncommon to encounter issues with numbers and dates not being recognized properly.

Importing data via Power Query provides us with a plethora of tools to manipulate and interpret our data.  Importing data from Excel add-ins that pull content from external sources such as Oracle or SAP will often not perform content analysis.  The add-ins simply pull content and dump it into Excel.  Manually copying and pasting data from a list or database can also prove problematic when it comes to searches and comparisons.

From a human perspective a number looks like a number and a date looks like a date.  From an Excel perspective, numbers are sometimes interpreted as text and dates are often interpreted as numbers.

Because of this confusion, popular functions, like VLOOKUP, or formatting instructions fail to return the expected results.

Let’s explore these situations and learn a very fast and easy way to have Excel see our data the way WE see the data.

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.

Excel Download Practice file

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

LEARN MORE

Leave A Comment

Share This