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
- You can easily get to know by spotting a green error triangle on top left corner of the cell.
- Simply select all the cells with numbers as text and click on the notification icon (appears as you select the cells)
- 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
Download The Ultimate VLOOKUP Guide
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
