VLOOKUP in Excel explained

 One of the biggest use of spreadsheets, is comparison. There will be situations where we need to compare data in one sheet with that of another.

Finding a matching data may not be easy always. If Tom has a car, and you want to lookup which one it is, we can use vlookup. It is easy in our small list used for the example, but it can be a tedious job when we have a huge list and want to look up the car owned by many people.

This is where vlookup comes in. I have used it to match the employee IDs of my team members to match it with an ever changing list of rotating shift details. So even if the name of the team member changes, I'll have his/her employee ID right next to his/her name.

Let's take the example of car owners for better understanding.

Imagine there are 500 people and each of them own cars (or not). You are given the same list in another order and asked to match their cars to their names. It will be very tedious and even prone to a lot of errors.

Observe here that the list in column E is in a different order, but the cars next to their names is matching to the list in B.

While reading this post, you have to keep in mind that this is useful for a very long list. Our small list might underestimate the power of vlookup.

So, wherever Tom's name is mentioned, his car will be listed next to his name. Let's change all the names to Tom and see what happens.

Let's examine the vlookup formula in detail.


= All formula starts with =

vlookup Vertical lookup

( Open bracket

E2 Read the content in E2 which in this case is the owner's name.

$A$1 This is actually A1. The $ symbol is used to prevent it from changing when the formula is pasted to another cell or dragged to multiple cells

: is used to specify a range

$B$3 Values upto B3

2 Where is the data to be looked up? In column 2 which is B

FALSE is used to get an exact match. If True is used, it will find an approximate match. Eg. If the name is Dic, it'll return BMW. "False" will return BMW only if the name is Dick.

$A$1:$B$3 is a range where the lookup need to be done :

The above cells are denoted by A1:B3. As explained above, if you don't want the values to change when pasting to other cells, use the $ symbol.

A1 This value will increment inside a formula if pasted to another cell
$A1 Column A will not change, but row 1 is allowed to change
A$1 Column A is allowed to change, but row 1 is not
$A$1 Both values are not allowed to change.

In our example, we use $A$1:$B$3 because this is a constant range and should not change. If the range auto-increments to A4, then we don't have data in A4 to lookup and it'll give a REF error.

Lets take a look at what happens if "True" is used instead of "False" in the above formula.

Notice that Dick's and Harry's names are incomplete, but the formula looked up their cars.

If "False" is used, it'll return a N/A indicating that there is no match in A1.

If you have the data in another sheet, then you can reference it using SheetName! as shown below or as explained in How to reference a cell to a new sheet in Excel

Hope you found this post useful. Please share if you liked it.

No comments:

Post a Comment