INDEX and MATCH are “nested” functions – two functions used together.
The first argument of INDEX is to give it an array. This array (range) should include your answer. You then need to specify how many rows to go down and how many columns to move to find the correct value. You cannot move outside the INDEX range.
The syntax of INDEX is:
- The range where the return value resides.
- Number of rows to move down (the row index)
- Number of columns to move to the right (the column Index). This argument is optional. If you only have one column, you can leave the column argument empty, otherwise, you need to specify the number of columns to move over, in the range.
Here is an example of INDEX without MATCH:
As shown above, INDEX alone is not useful for identifying the number of rows or columns to move. This is where MATCH comes into play. It helps the INDEX function to locate the answer.
The MATCH function was designed to return the position or address of the lookup value to the INDEX function. This is what differentiates MATCH with other functions. It does not return the VALUE in the cell but returns the POSITION of the cell within a specified range.
The MATCH function’s syntax is as follows:
- Like VLOOKUP, Match needs a lookup (target) value. The user can reference a cell or directly type the value into the formula.
- Lookup_array: The “list” (range) where the return values are located.
- Match_type: 0 for an “exact”
In the below example, the MATCH function returns the value of 4. 200 is 4 rows down from the specified range.
The important point to note on the MATCH function is that it’s a one-way street – you can only have one row or one column selected.
Tip: If your MATCH contains multiple criteria then you need to turn the multiple criteria into a one-way street. You can do this by wrapping the MATCH function inside another INDEX or by converting your formula to an array – CSE – function. To watch this in action, refer to the INDEX & MATCH Lecture in my Advanced Excel Training XelPlus.com/AdvancedExcelCourse