Vlookuphobia1

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

Vlookuphobia2

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.

Vlookuphobia3

  • 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!

Vlookuphobia4

 

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

Vlookuphobia5

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

 

Download The Ultimate VLOOKUP Guide

The Ulimate Vlookup Guide 1

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

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI