Excel VLookup – Simple Example

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:

Customer TableThe Customer table has pairs of Customer IDs and the associated Customer Names.

Now in a different place in the worksheet we have a Pipeline Table with Customer ID and Opportunity details. Something like this:

Pipeline table with no customer nameNote 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:
=VLOOKUP(B2,'Customer list'!A:B,2,TRUE)

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:

Pipeline table with customer name

And that is the simple example of when and how to use VLookup.

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s