Index Match Function
The Index Match function is the bigger more sophisticated brother of the lookup function allowing for both vertical and horizontal analysis of the data.
The index match function uses two separate queries to pinpoint specific data in the data array.
Combining the Index Match function with drop down boxes produces a sophisticated search function for the end user.
Break down of the Index Match function
In order to understand the Index Match function it is important that we first understand the two combined functions.
The Index function returns the data in specified cell based on the row number and column number of the data range specified.
Although this function is great if you know exactly where the data you wish to see is located, there are not many instances when you will know the location of the data.
=INDEX(DATA RANGE, ROW, COLUMN)
In the example above we are looking at the data in range B3:O107.
We have stated that we want data from the 10th row and in the 7th column.
The match function returns the relative position of data and can be used for both columns and rows.
=MATCH(CRITERIA, DATA RANGE, TRUE/FALSE)
Criteria – This is the value that you wish to be found
Data range – the range of data from where the criteria is to be found
True / False – True means that an approximate match is acceptable, false means that an exact match is required.
Combining the Index and match functions
As we can see above both the Index and match functions have limited functionality in isolation, however when combined they become a very powerful analysis tool.
By utilising the Match criteria we can locate the relative position of data and then feed this information into the index function to return the data in these cells.
Structure of the formula
When compared to the original INDEX formula below you can see above that the column and row items “10” and “7” have been replaced by two MATCH functions.
The Match functions act as the co-ordinates for the INDEX functions to locate the data required.
The best way to understand INDEX MATCH is to use examples.
In order to best illustrate how to use the Index Match function I have created a data set that includes an additional column (B) and row (A). The additional column and row have been added to easily identify the row and column number of the dataset.
The INDEX function
Using the Index function on the above data set as per the data below: –
The function is looking for the data in the 10th row of the data set and the 7th column. The 10th Row is the “Australia” data and the 7th column contains sales data for “September”. These have been highlighted in the screen shot above and it can be seen that the answer returned of 383 is as expected.
Using the MATCH function in isolation: –
The initial MATCH function, Match 1, is looking for the column number in the data array that has the value of “February”. In the above data set it can be seen that this is in column 12 in the data series from C3 to O3.
The second MATCH function, Match 2, is looking for the country “Benin” from the data in the range from C4 to C107. In this instance “Benin” is the 20th value in this data series therefore the value of 20 is returned.
Combining the two functions: –
By subsitituing the two MATCH functions into the INDEX formula we are using the MATCH return to power the co-ordinates required for the INDEX function to locate the data value to be returned.
Simplifying this further: –
C4:C107 – Look in this data array
MATCH(S16,C4:C107,0) – Return the row value of the variable in S16
Let us call this coordinate A
MATCH(S15,C3:O3,0)) – Return the column value of the variable in S15
Let us call this coordinate B
So our formula below can be constructed for our understanding as: –
=Return data value from (this data array where the following co-ordinates intersect, co-ordinate A, Co-ordinate B))
To reitterate the value to be returne is 4.00.
Benin is the 20th row of the data set and February is in the 7th Column of the data set. If we follow the data from the column and row to where they intersect then the value is 4.00
Summary of Index Match
The index match function can make your head hurt, it can take a while to figure out the logic but when the lightbulb moment occurs you will be thrilled at the possibilities.
The way I first understood the Index function was to imagine that I was finding co-ordinates on a map and plotting to a single point based on these co-ordinates. The Match function is simply obtaining these co-ordinates based on a pseudo lookup.