How to use VLOOKUP function in Excel

VLOOKUP Formula in Excel

I have been teaching Excel for many years. Since the first batch till now, one formula has always been the most sought after and difficult to learn. VLOOKUP, a function that is very powerful in extracting value by matching data but also complicated for beginners. So, let’s understand this formula in simple steps.

Summary

VLOOKUP is an Excel formula that helps to retrieve a value from a table by specifying the value of the first (left-most) column of data. Fetch its corresponding value from the same row by the column number you specify. This formula comes under the category of “Lookup & References” where VLOOKUP means vertical lookup. This means that VLOOKUP can be used with a vertical data set.

Syntax

=VLOOKUP(lookup_value,table_array,column_index_number,[range_lookup])

Arguments/Parameters of Function

Lookup Value: This is the value to be checked in the first (left-most) a column of the given data.

Table Array: Data range which contains column having lookup value and also the column having a return value

Column Index Number: Table Array must be a data range having rows and columns. In this case, the column number is used for its return value.

Range Lookup: This is an optional parameter with two options

i.                     True (approximate match): This is the default type, if no option is used, it will automatically choose TRUE. True means approximate match which means, if the lookup value is exactly not available in the list, the formula will return the least matching value automatically, rather than returning an error.

ii.                   False (exact match): FALSE (exact match) means, it looks up for the exact match from the lookup value available in the list, and return back value from the same row from the column you specify.

Some Practical Examples

Example 1: Find value from table array (Range Lookup: FALSE)

In the above example, the Employee code entered (A113) is being searched from a left-most column of a table array and range lookup being “false” finds the exactly matching value and returns back value (4%) from the same row from the specified column index number (3).

Example 2: Find value from table array (Range Lookup: TRUE / Not mentioned)

In the above example, Basic salary 12800 is being searched from a left-most column of a table array and range lookup being “true” find the approximate matching value and return back value (4%) from the same row from the specified column index number (3). The similar output will be returned if the range lookup is not mentioned (as default range lookup is TRUE).

I hope this small elaboration will help you in understanding VLOOKUP formula better. In case you have specified criteria which you are still unable to resolve with the VLOOKUP function, or you want some more examples or other formulas to be elaborated, feel free to send me a comment for the same.

Do provide me a review for this blog as well, your reviews will encourage me to do better and it will give me the power to work harder. Till then, happy excelling, and have a happy day.