Vlookup Function

Vlookup function is one of the most important functions in excel for me. It helps you fetch a value from a list of values looking at leftmost  column and taking corresponding value from same row of the column you choose.

If you have a transaction code for a sales transaction and you need to find the customer id related to that sales how would you find what you are looking for?

If that is one value you are looking for, you can simply filter your transactions list and get your data. But if you have a bunch of transaction codes that needs to be matched with customers than you will need something more powerful. Here is where vlookup function comes into play.

Here is an explanation with an example:

We are going to use same dataset from pivot table post. It looks like:

We have a transaction id (TR20127973) and we are looking for corresponding customer name inside this list with 1000 rows. We are going to use vlookup function for this task.

Here is how it is done:

Select the cell you want customer name to be shown and write following formula:

=VLOOKUP(B7;Sheet1!$B$4:$H$1003;2;0)          which means

Look for the value of Cell B7 of current sheet; in the range of CellsB4:H1003 in Sheet1; fetch the value in same row of 2nd column; return only exact value.

So excel will

• Look at range B4:H1003 in sheet1.
• Search for TR20127973 (value of cell B7 of current sheet)  in column B (leftmost column in B4:H1003).
• When located TR20127973 in column B, it will fetch the value in same row of 2nd column.
• If it cannot locate TR20127973 in column B, then it will return with #N/A as result.

Now that we have covered the hard way, here is the easy way 🙂

Select the cell you want customer name to be shown and click on the function button located on the left of formula bar. Select vlookup from the menu (look inside lookup functions or all functions). You will be greeted with a formula editor. Fill it like shown below:

You can activate any field than fill it either by writing inside or selecting the cells or ranges. Here is what each field is for:

1-      Lookup value: What do you search for (TR20127973)

2-      Table Array: What is the range of cells that you are searching for TR20127973

3-      Colon index number: Which column in search range do you want your value to be retrieved

4-      Range lookup: TRUE or 1 for best possible result, FALSE or 0 for exact match (returns #N/A if there is no matching values)

For this example, when you hit enter your formula will return with C00001.

Notes:  Vlookup always looks to left side. So if you are looking for a value in a column located at right side of the column that  your lookup value is in, you either need to re-arrange the columns or use another function (will be explained later).

Remember to use $’s in required places in your formula or they will mess up when you copy them to other cells.