VLOOKUP is one of the most useful Excel functions. It is also one of the most used formulas by professionals who want to extract data from flat tables. However, VLOOKUP is also abused and sometimes overvalued.
The formula was designed with some purpose in mind: searching a value in a left column and return the corresponding value in another column. In other words: if a table has the “Product Code” field to the left, that table is only searchable by the “Product Code”.
If you want to work with lists that have multiple and complicated criteria, you should not be wasting your time with it…
Here are the pros of why you should use the VLOOKUP function:
- Searches a value in a backend index column and returns the value of the specified column
- It is easier to write than its counterpart INDEX & MATCH
- Returns the exact or lower approximate match
Here are the cons of why you should not use the VLOOKUP function:
- Searches only the left index column
- Does not retrieve the location of the found value
- Retrieves only the first instance
- Does not search multiple column criteria
I still use VLOOKUP and enjoy the benefits and ease of writing it. I use it when I want to compare tables based on one single column and criteria. Most of the time, I need to move the required column in the backend list to the left.
Ultimately, I use INDEX & MATCH; writing this formula is not as intuitive at the beginning, but once you engage with it, you become familiar. It is worth the effort, especially when you feel the freedom to search any column of the table array and the potential to evaluate multiple and complicated criteria. Highly recommended.