+91-8826547882

Lookup and Reference Functions – That Helped Me Keep My Job

Lookup-function

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.

Vlookup-function

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])
Lookup function

 

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.

match function in excel - lookup function

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.

Index-Function-in-Excel-2007

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 4th and 5th 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.

Offset-function-in-Excel-2007

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

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

September 4, 2013

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

Leave a Message

Your email address will not be published. Required fields are marked *

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com

Your Name (required)

Your Email (required)

Your Phone no. (required)

Your Location (required)

Your Message for us...

captcha