Match function is used to get the relative location of a text or value within a specified array.

Syntax:

= Match (Lookup_value, Lookup_array, Lookup_type)

Lookup_value: This value in the function specifies the value or the text (an item) that is to be looked up in the array (or a range of cells)

Indirect()

This function is used to return the reference to text reference provided. This function converts the text reference to actual reference and fetches the value available in the address referenced. (It will be much clear when we will take a look at an example)

Syntax:

= Indirect (Text_reference, Reference_format)

Index()


Index function can be used in 2 forms – Array form and the Reference form. I will take each of these here.


Array form: In the first type, it is used to fetch a value from a table / array by making an intersection point using the row number and the column number. In this form, you can specify only a single array from which the values are to be fetched.

Today I am starting to write about Lookup functions, that include VLookUp(), HLookUp(), InDirect(), etc. Lookup functions are one of the most useful and in-demand functions.

These functions are used in lot of applications, their demand in today’s market is very high, one of which is financial industry. And not just financial research, these functions are very famous in other industries also, at all those places where there is high use of Excel spreadsheets.

If-Then-Else – one of the functions that Excel has taken from the programming languages. And it is one of those things that one learns as basics of programming.

If-Then-Else (or simply IF function) is used to make a choice based on condition(s) provided by the user. For example:

Row()

This function is used to determine the row number of a given reference (the cell referred to in the function).

Syntax:

= Row (Reference_cell)

Reference_cell: This is the cell for which the row number is to be determined

Examples:

The function simply returns the row number for the cell which is passed on as reference with the function. For example:

• Row($A$3) – result will be: 3
• Row($C$35) – result will be: 35
• Row($AA$45) – result will be: 45
• Row($BB$125) – result will be: 125

You can also use this function to determine the row in which the function itself lies. For example, If this function is written in cell $F$5, then the function should return this cell’s row number. For this the function can be simply written without the Reference_cell. It will be:

=Row()

…and the function will return the row number of the cell in which it is contained.

Application:

Though this function is very simple to use, but is a very useful functions. This functions is mostly applied with other advanced lookup functions, such as vlookup(), hlookup(), address(), etc. This functions works similar to Column() function.

Column()

This function is used to determine the column number of a given reference (the cell referred to in the function).

Syntax:

= Column (Reference_cell)
Reference_cell: This is the cell for which the column number is to be determined

Examples:

The function simply returns the column number for the cell which is passed on as reference with the function. For example:

• Column($A$3) – result will be: 1 {as column A is the first column}
• Column($C$35) – result will be: 3
• Column($AA$3) – result will be: 27
• Column($BB$3) – result will be: 54

You can also use this function to determine the column in which the function itself lies. For example, If this function is written in cell $F$5, then the function should return this cell’s column number. For this the function can be simply written without the Reference_cell. It will be:

=Column()

…and the function will return the column number of the cell in which it is contained.

Application:

Though this function is very simple to use, but is a very useful functions. This functions is mostly applied with other advanced lookup functions, such as vlookup(), hlookup(), address(), etc. Take a look at this query which I received from one of the readers of the Excel Matic. I have made use of this function. (Note that the example uses a few functions which are not yet described on Excel Matic, but will soon be).

Address()


This function is used to custom build a address range in Excel. It converts a text into actual address based on given parameters. The function can return address for a single cell and a range of cell as well.




Syntax:


= Address (Row_num, Column_num, Reference_type, Address_type, Sheet_name)


Row_num: This value determines the row number that the address should refer to
Column_num: This value determines the column number that should be referred by the address
Reference_type: This single-digit parameter determines which type of reference should the address have. The reference codes are – 1=absolute reference, 2=absolute row/relative column reference, 3=relative row/absolute column reference and 4=relative reference. The default reference is ‘Absolute’ reference
Address_type: This value determines the style of address that should be returned. There is no effect of this parameter on its functioning. The address type codes are – 1 or TRUE = A1 type reference, 0 or FALSE = R1C1 style reference. In case this parameter is not available, the function adopts the A1 style reference, even if R1C1 is the default style in the sheet
Sheet_name: As the name suggests, this parameter provides the sheet name which should be included in the address as its part. This is used when the address should refer to a range which is external to the given sheet


Note: In the above syntax, Reference type, Address Type and Sheet name are optional parameters. Any of these can be mentioned in the function independent of each other

an>




Examples:


Here are a few examples of using Address() function

• Address(1,2) – result will be: $B$1
• Address(2,1) – result will be: $A$2
• Address(2,1,1) – result will be: $A$2
• Address(2,1,2) – result will be: A$2 (note the reference style, the reference to column ‘B’ is not absolute)
• Address(2,1,4) – result will be: A2
• Address(2,1,1,1) – result will be: $A$2
• Address(2,1,1,TRUE) – result will be: $A$2
• Address(2,1,1,0) – result will be: R2C1
• Address(2,1,1,FALSE) – result will be: R2C1
• Address(2,1,1,1,Sheet1) – result will be: Sheet!$A$2 (note that the address now contains sheet name also)
• Address(2,1,1,1,Sheet_new) – result will be: Sheet_new!$A$2
• Address(2,1,,1) – result will be: $A$2 (note that the third parameter has been ignore, but its place holder remains there as a blank indicated by 2 commas)

Application: