Problem: None of my VLOOKUP formulas are working. I can clearly see that there is a match in the lookup table, but Excel cannot see it.
- None of the VLOOKUP functions work.
Strategy: A common problem is that either the item in column A or Column L has trailing spaces. This can happen if you downloaded the data from another system.
To fix this problem, you select cell A2 and press the F2 key to put the cell in Edit mode. A flashing insertion cursor will appear at the end of the cell. Check to see if the insertion cursor appears immediately after the last character or a few spaces away.
Edit cell L2 to see if there are trailing spaces. You will likely find that either column has trailing spaces. Below, you can see that there are a couple trailing spaces after the Item in column A. These trailing spaces cause the VLOOKUP to not classify the cells as a match. Although you can tell that 'BG33-8 ' is the same as 'BG33-9', Excel cannot.
- Column A has trailing spaces.
You can use the TRIM function to remove leading and trailing spaces from a value. If there are spaces between words, it will change consecutive spaces to a single space. For example, =TRIM(' Bill Jelen ') would change the cell contents to 'Bill Jelen'.
Additional Details: If the trailing spaces appear in your lookup value, use TRIM around that one value. Change =VLOOKUP(A2,$L$3:$M$30,2,FALSE) to =VLOOKUP(TRIM(A2),$L$3:$M$30,2,FALSE).
If the trailing spaces appear in the lookup table, then you can actually TRIM the entire table with one bizarre modification. Change the formula above to =VLOOKUP(A2,TRIM($L$3:$M$30),2,FALSE). But, don't press Enter after making the edit. Instead, hold down Ctrl and Shift and then press Enter.
Gotcha: That formula where you TRIM the entire lookup table is going to be insanely slow. It is fine for impressing your friends who use Excel, but in real life, it would be better to add a temporary column to TRIM each individual cell in column L. Then, copy that column and paste as values over column L.
Alternate Strategy: The other common problem of VLOOKUPs failing is numbers stored as text being used to look up a table with numeric values. Select column A and do Alt+DEF. Repeat with column L. Alt+DEF does a text to columns and converts text numbers to real numbers.
Method 2: Remove Leading and Trailing spaces with Excel VBA macro. If you want to quickly remove leading and trailing spaces, you can write an Excel VBA macro code, just do it following: 1# click on “Visual Basic” command under DEVELOPER Tab. 2# then the “Visual Basic Editor” window will appear. This way, we can deal with breaking spaces in Excel Cell. If the TRIM function is not able to give to proper results, then use this technique. #3 – Remove Double Spaces Using Excel FIND & REPLACE Method. We can remove double spaces in cells by using Excel find and replace method as well. Assume below is the data we have in excel. If your data set contains superfluous spaces, the Excel TRIM function can help you delete them all in one go - leading, trailing and multiple in-between spaces, except for a single space character between words. A regular TRIM formula is as simple as this: =TRIM (A2) Where A2 is the cell you want to delete spaces from. These trailing spaces cause the VLOOKUP to not classify the cells as a match. Although you can tell that ' BG33-8 ' is the same as ' BG33-9', Excel cannot. Column A has trailing spaces. You can use the TRIM function to remove leading and trailing spaces from a value. Excel contains a special text function called TRIM that is designed to fix this problem. TRIM takes one argument: the text you want to process. In this case, we just need to add a reference to the titles in column C, and then copy the formula down. The result is a set of cleaned up titles without extra spaces. Notice that TRIM replaces multiple.