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 d2 . 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 202Example 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 Table1If 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 3If 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 2What 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 2MATCH 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 6MATCH 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 3Example 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 resultExample 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 1Example 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 3Example 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 2In the next article, you will get to know with the advance of index and match functions.
