#### Oh God .. please make this VLOOKUPÂ work !! I promise to visit you every week đĄ

Do you pray that yourÂ VLOOKUP formula should work ? đŻ Let me rescue you from the top 3 common mistakes while writing VLOOKUPÂ function and save our prayers for more crucialÂ things in life!! đ

### 1. Trailing (Ghost) Spaces in your data or lookupÂ values

I have encounteredÂ this a lot Â so lets settle this once and for all. Â With no fault of yoursÂ sometime the data (or the look up value) on which you are working has some extra spaces inÂ the end. Take a look here

This simple problem has an equally simple solution. Just use the TRIM function (to remove any extra spaces) on your data and then apply VLOOKUP on the new data.

• Note that TRIM removes extra spaces before, after and in the middle of the text but will keep once space between two words by default.
• For Example =TRIM(” Â  James Â  Â  Bond”) will result in James Bond (removed extra spaces from the beginning and from the middle, keeping one space between ‘James’ and ‘Bond’)

### 2.Â Numbers Formatted as Text

Sometimes the numbers are formatted as Text and the Vlookup does’nt seem to work

1. You can easily get to know by spotting a green error triangle on top left corner of the cell.
2. Simply select all the cells with numbers as text and click on the notification icon (appears as you select the cells)
3. And change theÂ text values to numbers. Bingo!

### 3. NotÂ freezingÂ the Lookup Range

Often in my training sessions some folks say that I have never had the need to freeze (absolute referencing) the look up rangeÂ while applying a VLOOKUP… well there is a catch to this

• Your look upÂ range automatically picks up absolute referencingÂ when you apply VLOOKUP on a different workbook (i.e. a different excel file)
• But when yourÂ VLOOKUP is picking up data from the same excel file it does not freeze the range and you need to do that by using the F4 Key

Don’t know cell referencing ? Read aÂ detailed post on Cell referencing and Tricks

The guide contains tricks and tips beyond the conventional VLOOKUP and it’s free – DOWNLOAD THE ULTIMATE VLOOKUP GUIDE (pdf)

### Want more on VLOOKUP ?

What errorÂ do you often face while writing VLOOKUP ? Please share using comments