Can some one please explain what does Index function do and also what does
Match function do?

Which scenarios can i use these?

Dear Harsha

Please refer the below link which explains the difference in detail. Post
back if you have any more queries ...
http://www.ion.icaew.com/itcounts/14565

If this post helps click Yes
---------------
Jacob Skaria

Hi,

Returns the relative position of an item in an array that matches a
specified value in a specified order. Use MATCH instead of one of the LOOKUP
functions when you need the position of an item in a range instead of the
item itself.

Syntax

MATCH(lookup_value,lookup_array,match_type)

Lookup_value is the value you use to find the value you want in a table.

Lookup_value is the value you want to match in lookup_array. For example,
when you look up someone's number in a telephone book, you are using the
person's name as the lookup value, but the telephone number is the value you
want.

Lookup_value can be a value (number, text, or logical value) or a cell
reference to a number, text, or logical value.

Lookup_array is a contiguous range of cells containing possible lookup
values. Lookup_array must be an array or an array reference.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft
Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal
to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or
equal to lookup_value. Lookup_array must be placed in descending order: TRUE,
FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

If match_type is omitted, it is assumed to be 1.

Remarks

MATCH returns the position of the matched value within lookup_array, not the
value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative
position of "b" within the array {"a","b","c"}.
MATCH does not distinguish between uppercase and lowercase letters when
matching text values.
If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
If match_type is 0 and lookup_value is text, lookup_value can contain the
wildcard characters asterisk (*) and question mark (?). An asterisk matches
any sequence of characters; a question mark matches any single character.
Example

The example may be easier to understand if you copy it to a blank worksheet.

How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column
headers.


Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return
the results, press CTRL+` (grave accent), or on the Tools menu, point to
Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
5
A B
Product Count
Bananas 25
Oranges 38
Apples 40
Pears 41
Formula Description (Result)
=MATCH(39,B2:B5,1) Because there is not an exact match, the position of the
next lowest value (38) in the range B2:B5 is returned. (2)
=MATCH(41,B2:B5,0) The position of 41 in the range B2:B5. (4)
=MATCH(40,B2:B5,-1) Returns an error because the range B2:B5 is not in
descending order. (#N/A)

===================

Returns a reference to a range that is a specified number of rows and
columns from a cell or range of cells. The reference that is returned can be
a single cell or a range of cells. You can specify the number of rows and the
number of columns to be returned.

Syntax

OFFSET(reference,rows,cols,height,width)

Reference is the reference from which you want to base the offset.
Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET
returns the #VALUE! error value.

Rows is the number of rows, up or down, that you want the upper-left cell
to refer to. Using 5 as the rows argument specifies that the upper-left cell
in the reference is five rows below reference. Rows can be positive (which
means below the starting reference) or negative (which means above the
starting reference).

Cols is the number of columns, to the left or right, that you want the
upper-left cell of the result to refer to. Using 5 as the cols argument
specifies that the upper-left cell in the reference is five columns to the
right of reference. Cols can be positive (which means to the right of the
starting reference) or negative (which means to the left of the starting
reference).

Height is the height, in number of rows, that you want the returned
reference to be. Height must be a positive number.

Width is the width, in number of columns, that you want the returned
reference to be. Width must be a positive number.

Remarks

If rows and cols offset reference over the edge of the worksheet, OFFSET
returns the #REF! error value.

If height or width is omitted, it is assumed to be the same height or width
as reference.

OFFSET doesn't actually move any cells or change the selection; it just
returns a reference. OFFSET can be used with any function expecting a
reference argument. For example, the formula SUM(OFFSET(C2,1,2,3,1))
calculates the total value of a 3-row by 1-column range that is 1 row below
and 2 columns to the right of cell C2.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column
headers.


Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return
the results, press CTRL+` (grave accent), or on the Tools menu, point to
Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
A B
Formula Description (Result)
=OFFSET(C3,2,3,1,1) Displays the value in cell F5 (0)
=SUM(OFFSET(C3:E5,-1,0,3,3)) Sums the range C2:E4 (0)
=OFFSET(C3:E5,0,-3,3,3) Returns an error, because the reference is not on
the worksheet (#REF!)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Sri Harsha" wrote:

Can some one please explain what does Index function do and also what does
Match function do?

Which scenarios can i use these?

The F1 key is one of a number of ways into Excel help.
Index and Match are standard Excel functions, and are explained in help.
--
David Biddulph

Sri Harsha wrote:
Can some one please explain what does Index function do and also what
does Match function do?

Which scenarios can i use these?