## Vlookup Function

Stands for vertically lookup from the leftmost column of the table, its look for the data in the same row from a column you specify.

**Syntax for the Vlookup function:**

**=Vlookup(lookup value, table array, column index number, [range lookup])**

**Lookup value** is the value we need to look on for more information. Lookup value is in the first column of the table array.

**Table array** is the two or more columns dataset, where your return value exists.

**Column index number** is the column number from which you want to pull back your data from. The first column is 1 and so on.

**[Range lookup]** is optional. We need to enter False or True. False is to find and exact match and true is to find an approximate match.

In place of False you can write 0 and True to 1. If you leave this parameter vlookup will return approximate match value.

In the above screen shot in cell **“F2”** Vlookup function would return **$ 41000** for **EmpID 11004**(cell **c2**).

## Lookup Function

The LOOKUP function looks for the value in the lookup_range and returns the value from the same position from the result_range.

Note: lookup_function cannot find an exact match, it select the largest value in the lookup range.

**Syntax:**

**=Lookup(lookup_value,lookup_vector,[result_vector])**

## Match Function:

Match function returns the position of a value in an array.

**Syntax of Match Function**

**=Match(lookup_value, lookup_array, [match_type])**

**lookup_value = **The value you want to find in the list.

**lookup_array = **List in which you want to find the value.

**[match_type]** = Match type can be -1, 0 or 1.

-1 finds the smallest value greater than or equal to look up value.

0 finds the exact equal value to lookup value.

1 find the largest value less than or equal to lookup value.

In the above screen shot, Match function would return 3 in the Cell C4. 3rd is the position of Gurgaon (lookup value) in the list B7:B13.

## Index Function:

Index function returns the value from the specific position in a table or range.

**Syntax of Index Function**

**=Index(array, row_num, [column_num])**

**array = **Range of cells or table.

**row_num = **The number of rows in the array to return the value.

**[column_num] = **Column number is optional. The number of columns in the table to return the value.

In the above screen shot, Index Function in Cell F2 would return

**=Index(B4:F4,3,3)** – “Anshul Gupta”(Would return the cell D6 value)

## Offset Function

Offset function returns the reference to a range that is a given number of rows and columns from the given reference. Offset have five parameters. It also helps us to create dynamic ranges with the help of 4^{th} and 5^{th} parameter.

**The syntax of Offset function**

**=Offset(**Reference, Rows, Columns,[ Height], [Width]**)**

**Reference – **Reference is any starting cell from where the offset will be applied.

**Rows – **The number of rows move down/up from the reference cell in the range.

**Columns – **The number of columns move Right/left from the reference cell in the range.

**[Height] – **The number of rows in the range. This is optional; if we leave this parameter then height will be a single cell.** **

**[Width] – **The number of columns in the range. This is optional; if we leave this parameter then height will be a single cell.

Based on the above example, offset function in cell F2 would return

=offset(B4,3,2) – “Yash Pal”(Would return the cell D7 value)

## 0 responses on "Lookup and Reference Functions - That Helped Me Keep My Job"