**by A.R. Vital **

#### Vlookup:

As the name it means it lookups (Search). Vlookup is one of the most important and useful function in Excel. It extracts a single record by searching a value which you want in the given dump and extract the data from the concerned column (which you specify). This function has 4 fields. The syntax is

`VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) `

##### Lookup_value:

The value which you want to lookup (search)

For example: Cell reference like A1, B1 (i.e. Cell A1, Cell A2) or Column reference like A:A, B:B (I.e the entire column) or referring to a single cell by making it as constant like $A$1 (i.e. searching the same value/cell in different arrays/dump)

##### Table_Array:

Simply the dump. But it should start with the Lookup_value, i.e. if you are searching for “Apple”, the first column in the dump should contain “Apple” (it may be in any row).

For example: Cell reference like A1:E10/A:E or to another sheet in same workbook (will be in the form – sheetname!{reference} like Address!A:B) or a sheet in another workbook (will be in the form – ‘[File name.xls]Sheetname’!{reference} like ‘[Courier Dispatch Feb-2010.xls]Sheet1′!$A:$B)

##### Col_index_num:

Numbers like 1,2,3…. The column number from where you want to extract the data.

For example: In the above example (Table_Array) from the cell reference A1:E10 (consists A1:A10, B1:B10, C1:C10, D1:D10, E1:E10), if you want to extract the data from the first column i.e. Column A, you have to give the number as 1, likewise if you want to extract the data from the Column C, you have to give the number as 3.

If the Dump (Table_Array) starts from the column Z like Z1:AE10/Z:AE and if you want to extract the data from the first column i.e. Column Z, you have to give the number as 1 only because the given dump starts from Z Column and it is the first column in the dump, likewise if you want to extract the data from the Column AC, you have to give the number as 4.

##### [range_lookup]:

By defining this with 0/False (means exact search i.e. it searches only for the value which you give it in Lookup_value) or 1/True (means search approximately equal to or less than the value i.e. if it is searching for the value like “1.026”, if “1.026” is not available and “1.025” is available, excel considers 1.026=1.025). If you leave this field blank, Excel compiler automatically consider it as 0/False.

So the entire Vlookup function will be in the form

Referring in the same sheet

`=VLOOKUP(A:A,G:I,3,0)`

Referring to other sheet in same workbook

`=VLOOKUP(A:A,Sheet2!A:B,1,0)`

Referring to a sheet in another open workbook

`=VLOOKUP(A1,'[Courier Dispatch Feb-2010.xls]Sheet1'!$A$1:$B$65536,1,0)`

Referring to a sheet in another closed workbook

`=VLOOKUP(A1,'E:\Reports to be sent\[Courier Dispatch Feb-2010.xls]Sheet1'!$A$1:$B$65536,1,0) `