Excel Formulas and Tutorial

VLOOKUP

One of the most useful Excel Formulas. The Excel VLOOKUP function is used to look for specified data (lookup_value) in the range of cells called table_array. If value is found VLOOKUP will display data from the same row of array for the column you specify (col_index_num).

The syntax for VLOOKUP is as follows:

=vlookup(lookup_value,table_array,col_index_num,range_lookup)

lookup_value - is what we want to find. It can be a text (e.g. "Jan") or a reference to a cell (e.g. A1)

table_array - is where we want to find. Here we should choose a range of cells either by typing or by selecting with a mouse.
Make sure the leftmost column of your range is the search column for lookup_value.

col_index_num - is the array column number, from which we want the data to be retrieved.

range_lookup - is the optional argument which can be "TRUE", "FALSE" or omitted. If you want to find the exact match just go with "FALSE.

Let's practice with VLOOKUP. Please create a sample table in your Excel:
Type in any cell =VLOOKUP(A2,$A$1:$B$6,2,FALSE) and press "Enter"

The formula will return you the following result - "120".

It means that we wanted to find "A2" which is in our case "Jan" in the array defined as "from A1 to B6" and we want to retrieve the data from column number "2" (first column is the "search" column and further on).

If an exact match does not exist in our range we get the #N/A! error.

Once you get familiar with VLOOKUP you can manage different tasks with it. For instance you can easily create a quotation from a thousand-line price list.

==

Suppose you have a request from your customer that appears to be a list of items that you need to quote:

Item code

101
201
303
402
512
601
701
084

And you have your price list or stock data or catalogue etc. that you need to grab data from. It can have different columns such as item code, description, weight, lead time etc. and in most cases presented as an Excel file.

Price list (Sheet2) All you need to do is to open Excel Workbook where you will have one page (Sheet1) as a Quotation and another page (Sheet2) as your stock or price list data.

Quotation (Sheet1): Enter requested items into the first column (A) and insert VLOOKUP formula into another columns (B,C etc.) with appropriate argument col_index_num. It will fill up cells with your price list data from Sheet2. Prolong the formula downwards and the cells will be filled accordingly.

You can manage to break the Excel limit of 65536 rows if you use a combination of VLookup function, IF function, and ISNA function. Get to know how