Common Problems and Errors in LOOKUPS

01 1. Excess leading or trailing spaces

If there is a leading or trailing space in the lookup value search result will return N/A. So if there is excess space, you can use the Trim function with the lookup value to trim the leading and trailing space like this. VLOOKUP(TRIM(G2),A2:F20,2,FALSE) I have seen in some cases even if you use the Trim function extra spaces will not get removed. This usually happens when you got data from outside database. In this case, you can add a helper column and find the length of the lookup value using Len function. If the length is more than the visible length, then you can assume some unseen characters are there. For retrieving the data without those characters, you can use the Left or Right function clubbed with Len function. If the extra space is on the left side on the Vlookup, you can use LEFT(LEN(A2)-1) and if it is on the right you can use RIGHT(LEN(A2)-1).

01 2. Locking of the arrays or forgot putting dollar sign

If you forgot to lock the table arrays in the lookups, the search result would go wrong. So you should always double check the dollar signs are there in the place. Usually, if you are comparing between two files, then lookup will automatically insert a dollar sign. Else you have to manually insert the dollar sign or use the shortcut key F4 to insert.

If you forgot putting a dollar sign in the second parameter, then it will give wrong results. If there is no dollar sign, it will search in the wrong cells.

01 3. Full path to the lookup workbook is not supplied

If you are pulling data from another workbook, you have to include the full path to that file. So you have to enclose the workbook's name (including the extension) in square brackets [], and then specify the sheet's name followed by the exclamation mark. Also, you should have apostrophes around all this in case either a workbook or spreadsheet name contains spaces. This is the structure of the formula. VLOOKUP(lookup_value, '[workbook name]sheet name'!table_array, col_index_num, FALSE) A real formula may look like this. VLOOKUP($A$2,'[Sales report.xls]Sheet1'!$B:$D,2,FALSE) This formula will search for the value of cell A2 in column B of Sheet1 in the "Sales report " workbook, and return a matching value from column C. If any element of the path is missing, your VLOOKUP formula won't work and return the #VALUE error (unless the lookup workbook is currently open).

01 4. Col_index_num argument is less than 1

As you know, the third parameter of the column index cannot be less than one because Vlookup cannot do a left lookup. If you manually enter the Vlookup formula, this will not happen as you know the limitation. But if it is derived from a nested Vlookup or from a helper column derived from a formula then the third parameter may return a negative number. In this case, Vlookup will return #VALUE! error.

06 5. To find Column numbers for larger dataset

If you are comparing a larger data set and the second parameter have so many columns, then it will be difficult to find the column number. In this case, you can use any of these options. Select R1C1 reference style from formulas in Excel options. Once you change, you can see all the column numbers instead of the column names A, B or C. This is another quick way and is my favorite. While you select the cells of the table array ( second parameter), you can see the column number like this in the image.

It will change when you select different columns. The second part denotes the column number. So you can see the column number when you select the table array. Then when you reach the correct column number enter that as the column number in the third parameter. Another way is to put a new row at the top of the existing rows and then use the row function to find out the row you want.

06 6. If you don't put False, it is True

By default, if you don't put false as the fourth parameter in Vlookup then it is true, approximate match. And this will give wrong results.

06 7. Vlookup always find the first match

Vlookup always finds the first match and will ignore the subsequent matches.

06 8. Inserting a column may break existing VLOOKUP formulas

If you delete or add any columns you have specified in table array, Vlookup will give wrong results or error depends upon where you insert the column. This is because the column number you specify as the third parameter in Vlookup will not adjust according to the addition or deletion of columns.

06 9. Vlookup cannot look left

One of the most significant limitations of Vlookup is it cannot look to its left. Your lookup column (first parameter) should always be the left-most column in the table array (second parameter).In Chapter 6 we have addressed this problem.

06 10. Lookup and Table arrays in different format

Sometimes the data you are comparing will be in a different format and you will get the N/A error (data not available) even if there is a visible match. Vlookup treats text and number as two different things. So the text 1 and the number 1 is not the same for Vlookup. For example, in both sheets, you have the value 2017 and when you Vlookup you are getting N/A error. This is because data is in different formats either in the lookup value or table array. In this case, Vlookup will not find the match. You have to convert either of the two to the same format. This happens when you copy down from any website or when you import data from some external database. Another cause is if you have typed an apostrophe before a number to indicate a leading zero. An easy way to check if the value is text format is you will get count instead of sum when you select the range in the status bar. Or you can use the ISNUMBER function to check the value is text or number. Usually the numbers are aligned to the right by default in Excel and Text is aligned to the left. It is not recommended to change the table array to text or number; instead, you should change the lookup value format and is very easy thing to do. We will go through some ways.

06 10.1 First way

Here we will convert the lookup or table arrays first column to Text so you will get the same format if the other is in text format. By default, the text will be left aligned. Example: Open the file Chapter 8.1 - VlookupFormatText.xlsx Here we have the id no. of the employee and the employee name. Now if we enter the ld no. in the F2 cell we should get the name in F3 cell. Please go through the image. [example] First, we have entered 25 in F2 cell and we have written the Vlookup formula to get the name from B column. Even though there is a visible match between the Id no. We are getting the N/A error because both are in different formats. If you click the ld no. in A column you can see the format is in Text and if you click the ID no. in F2, you will see the format is General. So the easiest way is you can convert the lookup value to text and then compare. For that, you can append a blank text string to the lookup value. This is the original formula =VLOOKUP(F2,A2:38,2,0). This is how it will look after the blank text is appended =VLOOKUP(F2&"",A2:B8,2,0). Now the lookup value will be converted automatically to text when the Vlookup formula executes and you will get the correct value. Points to note: It is always easy to covert the lookup value rather than converting the table array.

06 10.2 Second way

In the same example instead of converting the lookup value to text, you can change the values in A column (table array) to number and then do the Vlookup. [example] In the image above A column has a green triangle in the upper-left corner of a cell which indicates a formula error in the cell. If you select the cell, the Trace Error Button image button appears. Click the arrow next to the button and choose "Convert To Number" from the context menu. If you have more cells, select all the cells and convert it to number. Now the lookup value and the table arrays first column are numbers. Then you can easily to do the lookup.

06 10.3 Third way

If there is no error correction option, you can use this method to convert the data in A column to General format. You can convert the data to text also using this method. For that highlight the column whose format you want to change and then from the Data Tab select Text to columns. First one will be the Delimited option don't change anything and click next. Make sure that the delimiter we have checked does not appear (like a comma or semi colon) in your column, and if it is there, this option won't work. Now click next, and in the third step you can select the format General or Text and click Finish. Now the first column data is converted to General format. Now the Vlookup will work.

06 10.4 Fourth way

You can use the Text function clubbed with Vlookup to covert the number to text like this VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE). In the first parameter, you have to use Text function then the lookup value and zero to convert the lookup value to text.

06 11. Converting the text to numbers

If you want you can convert the text to numbers using value function. You can do it either in the lookup value or in the table array. If you want to do it in the lookup value just insert the value function with the first parameter VLOOKUP(VALUE(F1),C1:01,1,0).

06 12. Common mistakes when you send a file

If you are fetching data from another workbook using Vlookup make sure the other workbook does not get deleted. This will break the Vlookup formula and you will get out of reference error.

Also if you are fetching data using Vlookup from a file stored in your computer and that file is sent to any other person he or she will get a security warning, this book is linked to another one. So it is always safe to value paste the data after you do the Vlookup in these cases.

06 13. Lookup is resource sensitive

If you have so many Vlookups spanning to different columns, then it will slow down the Excel to some extent. Mean it will take some more seconds to complete the lookup. Considering the power of new computers, it will be much faster, but on old computers, it will take some time. In this case, it is better you use Index Match function which doesn't use up that much resources. Because it doesn't use the entire data set to perform the lookup calculation, they require less processing power from Excel.

× DATE: Thu, Feb 06, 2020
× TIME: 9:38:44 pm
on Twitter or sign-up for my monthly newsletter: