Excel Spreadsheet Authors

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

1. It searches a value in the backend left index column and retrieves the specified attribute.
2. It is easy to learn and write.
3. It does not require any complicated DB relational stuff.
4. It updates automatically.
5. 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

1. Only searches a value based on a single condition.
2. Only searches a value in the left index column (you keep moving columns in the backend list to implement the formula successfully).
3. You can not know the location of the returned value (in terms of row or cell reference).
4. It only retrieves the first instance.
5. It is affected by a non sorted ascending index column.
6. 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.

In consequence… 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?

Vlookup articles

• 7 Easy Things to Do Before You Write a VLOOKUP Formula
• Do You Recognize the 3 Early Warning Signs of an Unexpected #N/A Error?
• Stop Wasting your Time Trying to Do Tasks that VLOOKUP Approximate Match doesn’t Support
• How to Build a VLOOKUP Approximate Match that doesn’t Fail
• Revisit VLOOKUP, You may Be Missing some Important Point
• Using VLOOKUP – How to Avoid #N/A by Getting Rid of Spaces in the Backend List
• Using Vlookup – How to Set the Argument “col_index_num” very Easily
• VLOOKUP – How to Delete Duplicates in the Index Column
• INDEX and MATCH Revisited – You may Be Missing some Important Point

Popularity: 7% [?]