VLookup is an Excel search function. See the official definition at the Microsoft Office site. It is part of a family of search functions that includes Lookup, VLookup, HLookup and Match. Each one with a slightly different behavior.
The VLookup syntax seems abstract and complex so instead of explaining the syntax let’s focus on a use case:
Translation from key to its value. Such as in the case of translation from id to name.
For example, let’s assume in one place in the Excel worksheet we have a table of Customers that looks something like this:
Now in a different place in the worksheet we have a Pipeline Table with Customer ID and Opportunity details. Something like this:
Note that in the Pipeline Table we have at column B the Customer ID. The big ask is to translate the Customer ID to Customer Name so that we can see who the customer in a more human readable fashion. See the empty column C above.
The VLookup for cell C2 will be as follows:
Or in English:
- First parameter (B2): Take the value of cell B2
- Second parameter (‘Customer list’!A;B): Search for the first row containing this value in the “Customer list” table that has two columns. Column A and column B.
- Third parameter (2): When such row is found, use the second column (column B) of that row to place the value at C2.
- Fourth parameter (TRUE): The last parameter with value TRUE means that the search uses Exact match.
The final result is a new table with the customer name populated:
And that is the simple example of when and how to use VLookup.