Quick Excel Tips to Boost Productivity Easily – VLOOKUP

Microsoft Excel’s VLOOKUP function allows you to easily identify data within a specific data range in a Microsoft Excel worksheet.

What It Really Does

The VLOOKUP is one of Microsoft Excel’s many reference functions. In a sheet that contains dozens of rows and columns, the VLOOKUP works like a more evolved CTRL + F function. The major difference is that you look up one value and then instruct Microsoft Excel to return another value associated to that value. In a data field containing telecom providers in Africa and their respective internet tariffs, for example, a single VLOOKUP function could be structured to display the internet tariff of one telecoms operator working in Burundi.

The function looks like this:

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, optionally specify TRUE for approximate match or FALSE for an exact match).

• The lookup value is the value you are looking for.
• The range of data within which you expect the lookup value to be. Make sure your lookup value is in the first column of this data range for VLOOKUP to work properly. More on this below.
• You also need the column number that contains the return value. If you are looking for data within D9:W20, then D is column 1 and E is column 2, F is column 3, and so forth.
• The value to be displayed will depend on whether or not you enter TRUE or FALSE at the end of the formular. If you enter TRUE, the formular will return an approximate value. If you enter FALSE, it will return the exact value.

Here’s An Example

For example, say we have a table containing information about different vehicles, their colours, mileage covered, price, and the number of seats as seen in the table below.

From the given table, let us say we would like to know the price of a Tesla vehicle. One way of finding this is to scroll down the list in the table till you find Tesla and then trace the corresponding price. But assuming we have thousands of rows and columns, that could be slow and tedious and could hamper productivity greatly. A VLOOKUP would help us achieve our results with only a simple formular. Here is the information that we will need to build the VLOOKUP formular.

=VLOOKUP(A10,A1:E14,4,FALSE)

=VLOOKUP: This is the formular you need.

A10: Tesla is the lookup value in our formular and is located in cell A10.

A1:E14: This represents the range within which we can find the data we are looking for. Tesla’s cell and its price range must necessarily fall within this range otherwise the formular will return an error.

4: Counting from Vehicles, Prices are in the 4th column of our cell range. Remember that the information you are looking for must always be to the right of the lookup value.

FALSE: By entering FALSE, we are instructing Excel to return the exact value rather than an approximate value. And so entering the above formular into Excel returns a value of 45000

The VLOOKUP function works well with other reference functions like Boolean TRUE, where an approximate value is returned if the exact figure you want is not available. Try this on your PC and let us know how it went for you. If you have any difficulty, feel free to leave a comment below, and we’ll gladly look it up for you.