Basic of Index Match

What is INDEX function in Excel?

It Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Example 1:

How many ways are to use the INDEX function?

we can use two ways to use index function:

1 . Array form

Returns the value of an element in a table or an array, selected by the row and column number indexes.

Use the array form if the first argument to INDEX is an array constant.

=INDEX({"a","b";"c","d"},2,2) ----->>>>answer is  d

2 . Reference form

Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of non-adjacent selections, you can pick the selection to look in.

=INDEX((A2:E5, A7:E10),2,3,2)----->>>>answer is  202

Example 2:

What happens If you set row_num or column_num to zero in INDEX function?

If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula.

=INDEX(Table1,0,0)----->>>>answer is Table1

If you set row_num to 0 (zero), INDEX returns the array of values for the entire column, respectively.

=INDEX(Table1,0,3)----->>>>answer is Column 3

If you set column_num to 0 (zero), INDEX returns the array of values for the entire row, respectively.

=INDEX(Table1,2,0)----->>>>answer is Row 2

What is MATCH Function in Excel?

It returns the relative position of an item in an array that matches a specified value in a specified order.

Example 1:

Match type in match function

When match type is set to 1,MATCH finds the first value equal to lookup value. The lookup array does not need to be sorted.

=MATCH(28,B2:B10,0)----->>>>answer is  2

MATCH finds the largest value less than or equal to lookup value. The lookup array must be sorted in ascending order.

=MATCH(28,B2:B10,1)----->>>>answer is  6

MATCH finds the smallest value greater than or equal to lookup value. The lookup array must be sorted in descending order.

=MATCH(28,B2:B10,-1)----->>>>answer is  3

Example 2:

Default Setting of Match type in match function

Default Setting of Match type is 1. If you need an exact match, make sure you set the match type to zero. Otherwise, you will get the wrong result.

=MATCH(28,B2:B10)----->>>>answer is  wrong result

Example 3:

ٍExact Match in match function

ٍExact Match in match function means the match type is set to zero.

=MATCH("Oliver",A2:A10,0)----->>>>answer is 1

Example 4:

ٍApproximate Match in match function

ٍApproximate Match in match function means the match type is set to 1(ascending) or -1(descending).

=MATCH(28,B2:B10,1)----->>>>answer is  6

=MATCH(28,B2:B10,-1)----->>>>answer is  3

Example 5:

ٍWildcard Match in match function

When match type is set to zero (0), MATCH can perform a match using wildcards.

=MATCH("Eli*",A2:A10,0)----->>>>answer is  2

In the next article, you will get to know with the advance of index and match functions.