Hello everyone! Did you know……how flexible and useful the MATCH INDEX special formula is?
book a session
Reason why MATCH INDEX special formula so special
By using Match Index you avoid the limitations of the standard Vertical Lookup in Microsoft Excel such as only being able to look up a table in a left to right process.
What is INDEX?
Index is a function which allows one to return a cell value from a range of cells as being the intersection of a particular row and a particular column.
The syntax for this function is
=INDEX(range of cells we are examining,row position number,column position number)
If the range of cells is just a single row or column then the corresponding column or row can be left out in the function.
What is MATCH?
Match is a function which performs brilliantly inside the Index function.
What does it look like?
The syntax is
=MATCH(what looking up,single column or row range,0 for exact match).
MATCH’s first argument is what you are trying to match or lookup and its second argument is an identifiable list which can only be a single column or row from which you should find a match. Enter 0 for the third argument which means you are looking for an exact match.
What MATCH returns
The result of MATCH is a position number which we can use as one of the arguments in the INDEX function.
MATCH INDEX or INDEX MATCH all together – an example
=INDEX(single column returning from,MATCH(cell looking up,single column lookup data,0))
So long as consistency is maintained in the sequencing of ranges used in the two functions then the position number determined by MATCH can then be used inside the INDEX function to select the exact cell you are looking for based on the matched criteria.
The perfect combination of functions!