At a glance
Question
Help! My VLOOKUP doesn’t work – what should I do?
Answer
VLOOKUP is the function I receive most queries about. While it is easy to use, it is also easy to break.
VLOOKUP stands for vertical lookup. This function works well with a standard table layout that has a code, or value, in the left column of the table. The other columns in the table can be accessed by “looking up” the code or value in the left column.
Most issues relate to the data table, especially when it is imported from other systems. The exact match type of VLOOKUP is the one usually affected. You specify an exact match by including FALSE or a zero at the end of the VLOOKUP function.
Examples from cell B2 in Figure 1:
=VLOOKUP(A2,D1:E5,2,0)
=VLOOKUP(A2,D1:E5,2,FALSE)
When using an exact match, the code or value must be in the left column of the table, otherwise the #N/A error is displayed.
Common problems
Spaces
The most common problem is leading and trailing spaces in the left column of the table.
Systems still pad entries with spaces when data is exported into Excel. Padding means filling the field with spaces. If a field has a maximum of 10 characters but only six characters are used for the code, then four spaces are added to the beginning or end of the code to fill up the field.
Padding with leading spaces is obvious, as the code is not left aligned. Padding with trailing spaces is harder to notice, as text entries are left aligned. You need to use the right align format on the column to identify any trailing spaces. While this is a frustrating problem, it is easily solved with the TRIM function.
The TRIM function removes all leading and trailing spaces. Spaces within the code are unaffected. The formula to trim an entry in cell A1 is:
=TRIM(A1)
Typically, you use a blank column to the right of the table to enter a range of TRIM functions referencing the left column of the table. You then copy the trimmed range and use Paste Special Values to paste the trimmed values on top of the original values. The table is then ready to use.
If you have no spaces within your codes, you could also use the Find and Replace option (Ctrl + h) on the left column to remove all spaces. Select the left column of the table and press Ctrl + h; type a single space into the Find box and then click the Replace All button. This replaces all the spaces with nothing.
Note: always select a range before using Find and Replace, otherwise you may be affecting the whole sheet.
Numbers vs Numbers as Text
This problem only applies to numeric codes. Sometimes when you import data into Excel, the numbers are formatted as text.
You can spot this because the numbers will be left aligned, rather than right aligned. Unfortunately, if you look up a real number and the table has text numbers, Excel will not find a match. You have two options: convert the real number you are looking up to text and then look it up, or convert the text numbers in the left column of the table to real numbers.
Figure 1 will be used to demonstrate the remaining techniques.
Look up a text number
To look up a text number, you have two choices. The first is to insert an apostrophe in front of the number you are looking up. See Figure 2.
The apostrophe doesn’t display, but you see it if you edit the cell as per the centre image in Figure 2. The second option is to amend the VLOOKUP function to convert the real number into a text number. The following formula amends the formula in B2 and converts the number to a text number:
=VLOOKUP(""&A2,D1:E5,2,0)
Placing ""& in front of A2 instructs Excel to treat A2 as text. The & symbol joins text together and using it automatically converts numbers to text. The "" means a blank and adding it to the start of the A2 does not change the value. See Figure 3.
Fix numbers formatted as text
If you want to fix the data in the table and convert the text numbers into real numbers, Excel has a built-in technique to fix the problem. Excel’s built-in error checking displays a tiny green triangle in the top left of a cell to warn you that a number is formatted as text. See Figure 4
If a column of numbers is formatted as text, you can select the range involved and another small icon with an exclamation mark will display. Pointing to it will display the error involved. See Figure 5.
Clicking the small drop down displays a menu that offers the solution. See Figure 6. Clicking the Convert to Number option will fix the selected range. A formula that converts the text number in D2 into a real number is:
=D2*1
You could add a column to the right of the table using the above formula and use copy and Paste Special Values to overwrite the text numbers with real numbers. These are a few of the common issues that can impact the VLOOKUP function. If you have others that trouble you, send your queries to the email address below. The companion video and an Excel file may assist your understanding.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to a range of organisations. Questions can be sent to [email protected]
