Lookup & Reference Functions

Lookup & Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets. They perform tasks such as providing information about a range, returning the location of a given address or value, or looking up specific values.

ROW : returns the first row number within a supplied reference or the number of the current row.

Syntax : ROW( [reference] )

            [reference] : optional argument that specifies the cell reference from which row number to be returned.  If omitted it returns the row number of current cell

Examples:

=ROW() returns the row number of current cell

=ROW(G6) returns 6

=ROW(B2:G8)  returns 2 (row number of first cell in the range)

COLUMN() : returns the first column number within a supplied reference or the number of the current column.

Syntax : COLUMN( [reference] )

where [reference] : optional argument that specifies the reference from which the column number to be returned. If omitted returns the number of the current column

Examples:

=COLUMN() returns the column number of current cell

=COLUMN(G6) returns 7

=COLUMN(E2:G8) returns 5 the column number of first cell in the range

ROWS : returns the number of rows that are contained within the range.

Syntax : ROWS(array)

Where array : range from which the number of rows to be returned.

Examples:

=ROWS(B1) returns 1

=ROWS(B1:E5) returns 5

=ROWS(B:B) returns 1048576 (in Excel 2007 or later)

COLUMNS: returns the number of columns that are contained within the range.

Syntax : COLUMNS( array )

Where array : range from which the number of columns to be returned.

Examples:

=COLUMNS(B1) returns 1

=COLUMNS(B1:E5) returns 4

=COLUMNS(1:1) returns 16384 (in Excel 2007 or later)

AREAS : returns the number of areas that make up the reference.

Syntax : AREAS( reference )

Where reference argument can be a reference or list of references or a named range.  Since the syntax of AREAS function accepts only a single argument, put additional bracket and provide list of references separated with comma.

Examples:

=AREAS(C1:E2) returns 1

=AREAS( ( C1:E2, B3:B5 ) ) returns 2

=AREAS( ( B2, C1:E3, D1:E5 ) ) returns 3

=AREAS( TestRange ) returns 3 (while TestRange is a named range referring 3 areas)

ADDRESS: Returns a reference, in text format, for a supplied row and column number.

Syntax: ADDRESS( row_num, column_num, [abs_num], [a1], [sheet_text] )

where

            row_num : row number to be used in the result reference

            column_num : column number to be used in the result reference

            [abs_num] : optional argument to specify result reference type

                        1 – Absolute  (default value if abs_num argument is omitted)

                        2 – Absolute Row & Relative Column

                        3 – Relative row & Absolute column

                        4 – relative

Examples:

=ADDRESS( 1, 1 )  returns $A$1

=ADDRESS( 1, 1, 3 ) returns $A1

=ADDRESS( 1, 1, 1, FALSE ) returns R1C1

=ADDRESS( 1, 1, 4, , “Sheet2” ) returns Sheet2!A1

=ADDRESS( ROW(), COLUMN() ) returns $A$5

INDEX : Returns the value based on the index from the specified range of cells.  This function has two syntaxes.

Syntax1 : INDEX( array/range, row_num, [col_num] )

Where array/range : array or range that is referring

            Row_num : index row value of array or range from which value to be returned

            Col_num : optional index column value which is only useful when you want to refer an range of cells.

Example:

Result above function is 5

Example2:

Result of the above formula is also 5

Syntax2 : INDEX( range, row_num, [col_num], [area_num] )

Example

Above formula returns 45

LOOKUP

Can lookup values vertically or horizontally.  The data to be searched (in the lookup row or column) must be ordered.  If an exact match is not found, the Lookup function will match the closest value below the lookup value.

=LOOKUP(LookupValue,Array)

=LOOKUP(LookupValue,LookupVector,ResultVector)

VLOOKUP

Performs a Vertical lookup.  The user can decide what the function should do if an exact match is not found – either return an error or match the closest value below the lookup value.

=VLOOKUP(LookupValue,Range,ColIndexNo,RangeLookup)

LookupValue – The value to be searched

Range – The range of cells where lookupValue to be found

ColIndexNo – The serial number of column from which result to be returned

RangeLookup – can have values True or False.  False value give exact result while True value gives approximate result.

HLOOKUP

Performs a Horizontal lookup.  The user can decide what the function should do if an exact match is not found – either return an error or match the closest value below the lookup value.

=VLOOKUP(LookupValue,Range,RowIndexNo,RangeLookup)

LookupValue – The value to be searched

Range – The range of cells where lookupValue to be found

RowIndexNo – The serial number of Row from which result to be returned

RangeLookup – can have values True or False.  False value give exact result while True value gives approximate result.