Best practice to VLookup values in Excel

Posted: September 16, 2010 in Power of Microsoft Office
Tags: ,

Vlookup is a function that helps you search for a value in a range of cells and return the same or a different value from that range on the same row. To make the picture clearer, let’s take a scenario in which Zaid, an accountant working in XYZ Company, has an Excel file of two sheets; Year Revenue, and Year Cost of a list of XYZ products. Zaid is trying to combine the two sheets’ revenue and cost figures to calculate the profit for each product.

It’s obvious that Zaid has two options so that he can calculate each product’s Profit (Revenue minus Cost):

1. search for each product’s revenue in sheet1 and place it in its corresponding cell in sheet2, or

2. search for each product’s cost in sheet2 and place it in its corresponding cell in sheet1

Zaid elected to follow option 1 and added two extra columns in sheet2; Revenue and Profit.

clip_image002 clip_image004

      Figure (1)                                         Figure (2)

It’s worth mentioning that my experience with people who usually get incorrect results when applying the Vlookup function is that they either:

a. forget to ‘fix’ the lookup range (table array, as referred to in the Vlookup formula) using the dollar sign ($), and/or

b. try to lookup values of different data types. E.g. they lookup Invoice number between two sheets in which it is of Number format in one and of text format in the other.

To avoid the first problem, which is the most common, try to name the range of cells in which you need to search for Revenue values. To do this, select the range of cells that covers all data (including headers) in sheet1 and type any name in the Name Box (do not use space in the Name Box). I will name the range RevenueFigures (figure 3).

clip_image006

Figure (3)

Back to our scenario, we’re ready to apply the Vlookup formula in sheet2 to lookup the first Revenue value. I like and do prefer writing formulas than using the Insert Function, so I will write ‘=vl’ in cell C2 and Excel will narrow down my search to ‘VLOOKUP’ function as shown in figure (4). Hitting the TAB keyboard button will insert the function showing the arguments you need to insert to lookup the Revenue value of Product-I from sheet1 (figure 5).

clip_image008 clip_image010

Figure (4)                                                      Figure (5)

First, you need to specify the value in sheet2 that will be looked up from sheet1 which is captioned as lookup_value in the vlookup function. Select this value by moving the cursor to cell A2. This means that you need to search for Product-I in sheet1.

clip_image012

Figure (6)

Obviously, you have to specify the range of cells in which you need Excel to search for the lookup value, which is the second argument captioned table_array. Type a comma to end the first argument then type the first letters of the sheet1 Range name you previously defined until Excel finds it for you. Great Excel! (Figure 7).

clip_image014

Figure (7)

Well, now you’ve got to identify the value in the Range that you need Excel to return to sheet2 if the lookup value has been found in sheet1. I.e. you need to tell Excel to return the Revenue figure of Product-I from sheet1 and place it in cell C2 in sheet2. This is achieved by specifying the col_index_num. This is the column number of the return value reference to the lookup value in the Range. So, we need to return the value in column number 2 (Revenue).

clip_image016 clip_image018

Figure (8)                                                        Figure (9)

The last argument to complete the Vlookup formula is either TRUE or FALSE. Use FALSE to get the exact match (recommended always) and TRUE to get approximate match. Close the function with “)” and hit the return keyboard key to get the result. Then apply the formula to the rest of products by copy/paste.

clip_image020 clip_image022

Figure (10)                                                          Figure (11)

By this Zaid will be able to calculate each product’s profit by deducting the cost from the looked up revenue.

clip_image024 clip_image026

Figure (12)                                                          Figure (13)

After applying the Vlookup function to your data I recommend to always have a couple checks to the figures by taking samples of looked up values and comparing them to those in the searched Range.

Comments
  1. sriraman says:

    ur doing a great job mohammed! im in the learning process of excel… ur tutes are so guidable and i appreciate…

  2. Jamil says:

    Thanks, good job

  3. Sujit says:

    Thanks. very good example.

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