In this tutorial, I will teach you how to use INDEX and MATCH functions.
INDEX and MATCH functions are advance level functions in Excel 2013 which are used to perform lookups in a matrix. This is also called two-way lookup. INDEX and MATCH is a complex formula but if you will understand all the components of this, you will be able to easily use it.
In Excel 2013, this formula is in the formula tab, it is under Lookup & Reference section.
Before using the combination of INDEX and MATCH function, there is key condition that it only work if your data table is in matrix format.
If we will have sales data for 4 regions and 5 years this is called a matrix data and we can apply the formula on it.
The INDEX formula
The INDEX formula as you to specify a reference and returns a value. Following is the syntax:
= INDEX ( array , row_number , column_number )
With index we are providing both vertical and horizontal references.
For example if we want to check what is the value at row number 4 and column number 3.
The formula used here is “=INDEX(A1:G5,C10,D10)”
INDEX formula always takes the vertical reference first.
The MATCH formula
Match formula will ask you to specify a value and return as a reference. Match formula is opposite to the INDEX formula.
It works by providing a value to give its position in the range.
Following is the syntax for MATCH value:
= MATCH ( lookup_value , lookup_array , 0 )
Here the last argument specifies the following.
0 = Exact match.
1= less than the value.
-1 = Greater than the value.
Searching for the location of “South” as per the below image will return “3”.
While using the combination of Index and match function, we would use the following syntax:
= INDEX ( array , MATCH ( lookup_value1 , lookup_array1 , 0 ) , MATCH ( lookup_value2 , lookup_array2, 0 ) )
If we will explain the syntax, it would be easier to implement.
array means complete matrix.
Lookup_value1 means vertical lookup value.
lookup_array1 means entire left hand lookup column.
lookup_value2 means horizontal lookup value.
lookup_array2 means entire top header row.
As per our example we will implement the index and match combination.
If we want to find out sales for South region for 2012, we will use the following formula:
Excel 2013 evaluates the two MATCH formulas that are embedded in the INDEX formula.
The INDEX and MATCH function would be best to use when you have the matrix data and you have to get 2 kinds of lookups together i-e vertical and horizontal.