ابدأ بالتواصل مع الأشخاص وتبادل معارفك المهنية

أنشئ حسابًا أو سجّل الدخول للانضمام إلى مجتمعك المهني.

متابعة

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

user-image
تم إضافة السؤال من قبل Evans Otalor , Business Analyst / Facilitator , Marina Business School
تاريخ النشر: 2013/05/08
Evans Otalor
من قبل Evans Otalor , Business Analyst / Facilitator , Marina Business School

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.

مصطفى سيد عبد النبى مصطفى mustafa
من قبل مصطفى سيد عبد النبى مصطفى mustafa , Quality Assurance specialist , شركة اتصال الدوليه

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.

Ansab Khan
من قبل Ansab Khan , AVP / Unit Head - Digital HR Solutions , Meezan Bank Ltd

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.

Andy Osaretin
من قبل Andy Osaretin , Information Systems Database and Business Analyst Consultant , Alpha Consulting

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

Aatif Khan
من قبل Aatif Khan , Manager Compliance & Reconciliation , Kings Real Estate

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

CHARLIE LUGAY CPA
من قبل CHARLIE LUGAY CPA , SR. ACCOUNTING SPECIALIST , Sadara Chemical Company

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

Muhammad Asif Jajja
من قبل Muhammad Asif Jajja , Accountant , Pakistan Veterinary Medical Council

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.

Hany Hassanein
من قبل Hany Hassanein , Microsoft Trainer , Egyptian Banking Institute

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

المزيد من الأسئلة المماثلة