I was introduced to Excel by accident. I started a job and got a PC with Excel on it. The first tasks involved data entry and lists maintenance.
As my assignments evolved, I needed to do some basic queries…
That was the time when the VLOOKUP function made me feel the power of Excel.
Here are the pros why you should implement a VLOOKUP function
- It searches a value in the backend left index column and retrieves the specified attribute.
- It is easy to learn and write.
- It does not require any complicated DB relational stuff.
- It updates automatically.
- It can use wild cards.
After the initial feel, I found some shortcomings worth talking about…
Here are the cons why you should keep away of the VLOOKUP function
- Only searches a value based on a single condition.
- Only searches a value in the left index column (you keep moving columns in the backend list to implement the formula successfully).
- You can not know the location of the returned value (in terms of row or cell reference).
- It only retrieves the first instance.
- It is affected by a non sorted ascending index column.
- It does not allow you to search a value based on multiple criteria.
I still use the VLOOKUP formula because it is very easy to write; however, I cannot cope with the limitation of not being able to search multiple criteria, and the limitation to search only the leftmost column. I feel bored by moving columns to the left and trying to find out the solution to a multiple criteria lookup.
I now use the INDEX and MATCH formula. It appears complex at first, but once you get it, you can overcome those well-known VLOOKUP limitations and even get more versatility.
What do you think of the VLOOKUP gem?