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 overrated.

The formula was designed with one purpose in mind: to look up a value in one column on the left and return the corresponding value in another column. In other words: if a table has a “Product Code” field on the left, that table is only searchable by “Product Code”.

If you want to work with lists that have multiple complicated criteria, you shouldn’t waste your time with that…

Here are the advantages of why you should use the VLOOKUP function:

  • Looks up a value in a backend index column and returns the value of the specified column
  • It is easier to write than its INDEX & MATCH counterpart.
  • Returns the exact or less fuzzy match

Here are the drawbacks of why you shouldn’t use the VLOOKUP function:

  • Search only in 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 typing it. I use it when I want to compare tables based on a 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 isn’t all that intuitive at first, but once you get involved with it, you get familiar with it. It’s worth the effort, especially when you feel the freedom to search any column in the table array and the potential to evaluate multiple and complicated criteria. Highly recommended.

Leave a Reply

Your email address will not be published. Required fields are marked *