Best practice to VLookup values in Excel

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.

      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).

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).

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.

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).

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).

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.

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.

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.