Forget About Vlookup

Vlookup function is one of the most used operations in Excel. It allows to search in a table and return the result in another table based on a selection criteria. Basically it is the same as filtering something in a table and copying the results from a specific column into another. It is very useful to join information from two different tables, for example, getting the name of a customer or product based on the code or reference number. Stop using it... please.

Start using Index and Match. It will give you more freedom, less errors, more flexibility, faster results and less time calculation.

How to use it:
INDEX ( B:B ; MATCH ( D ; A:A ; 0) ) or
INDEX ( Results ; MATCH ( Reference ; Reference List ; 0 ) )

What you want - B:B the column with values you want to return or get (e.g. customer name)
How to find it - D the selection criteria or reference code (e.g. customer number)
Where to find it - A:A the column with value to select or filter from (e.g. customer numbers)

Example:

What you gain:
1. Stop getting referencing errors when you add, delete or change the order of columns
2. Never need to count the number of columns to identify the column you want to get returned
3. Get faster results and do not to wait every time for recalculating when you change something
4. Easier to review and understand were you are getting information from (easy to read)
5. No specific column order, that is, the search criteria is not required to be on the left

Using Range Names will also improve the flexibility and capacity to understand formulas and the Match formula has a lot of other applications that we may talk about later one (e.g. read data from a matrix or double entry table).

Excel can be made very simple and even easier to use if you use it properly.

Hope this helps. Please share your feedback and questions.