What are the limitations and dangers of using VLOOKUP in Excel?

Vlookup is a great function but have too many limitations as seen from all your comments.
Its great to know these limitations and have alternative functions at your disposal when faced with lookup problems where the Vlookup function will not suffice.

It will not be suitable if we use a lot of tables, changeable tables .

It can only handle data that has a unique value in each cell.
For example, if you have a column with two identical matches it will only select one and ignore the other.

one constraint which i have confronted many times, is unique names, not even space.
vlookup matches only unique names if there is additional space then formula won't work.
on other hand, it can be used for one range at a time.
Another, matches with left most column / row.
you can't apply formula if you want to match data from second column, you will have to alter the range.
alternates are index or / and match.

VLOOKUP is limited to a single table.
It cannot be used to search for a value in a different table or sheet.

It cannot lookup data above a certain limit.
Only an exact match or below.

First of all there are two ways to use Vlookup, Either with exact match or range match.

 

Range match is best used with the example of grading eg A=91-100%, B=81 -90% etc. If used properly there is not much issue.

 

The major issue arises in exact Match. Here the spellings have to be exactly correct. For example If one cell says " John" and the other says "John", it will not consider them the same. This is ideal in cases of database extracted sheets but not where data has been hand punched. The data has to be properly screened before applying Vlookup.

 

In fact all formulas have their limitiations. This is where experience comes in. With time one can learn to how combine three or four formulae to compensate for any limitations

Vlookup is one of the most important and a powerful tool in excel which we often use because it saves us a lot of time especially you are analyzing huge volume of transactions.
I believe, as for its intended use, I could not see a limitation.
Limitation is mostly on the user's knowledge of excel.
The excel user must be aware of the Vlookup parameters so their report will not turn into a disaster.
First is that you don't go straight ahead and use Vlookup.
You need to analyze first the data in your table for consistency and accuracy.
One dangerous thing is that Vlookup can still give you a result even if it cannot find an exact match of the value you are looking up.
But you can overcome this by adding the word FALSE in your vlookup formula so you can be sure that result you get is the exact result you want.

The greatest formula in my Quantity Surveying Professional life time.
The ONLY major issue is "HOW TO USE IT?".
If you are familiar about vlookup, if statement and $ formula, you can play like any thing as of my experience.
if anybody wants to know more about vlookup, please contact me at

The limitations of the "Vertical LOOKUP" are as under
1- It cannot lookup data above a certain limit.
Only an exact match or below.
2- It is limited to a single table.
It cannot be used to search for a value in a different table or sheet.
3- It matches only unique names if there is additional space then formula won't work.

VLOOKUP function is a great function can i use to search, but the limitation of VLOOKUP is that it will only search from left to right, another thing if your lookup value duplicates, another thing you must count the columns in your table.
BUT you can slove these issues by using LOOKUP or/and MATCH or/and INDEX with VLOOKUP