Discover how to use INDEX & MATCH functions in a formula.
When to use
Whilst useful functions in their own right, combined they form a highly versatile partnership.
- MATCH(1,F13:Q13,0) equals 5, i.e. the first period the balance sheet does not balance in is Period 5.
- INDEX(F4:Q4,5) equals May-17, so combining the two functions: INDEX F4:Q4,MATCH(1,F13:Q13,0)) equals May-17 in one step. This process of stepping out two calculations and then inserting one into another is often referred to as “staggered development”.
- Do note how flexible this combination really is. It is not necessary to specify an order for the lookup range, there can be duplicates and the value to be returned does not have to be in a row / column below / to the right of the lookup range (indeed, it can be in another workbook never mind another worksheet!).
- With a little practice, the above technique can be extended to match items on a case sensitive basis, use multiple criteria and even ‘grade’.
Lesson notes are only available for subscribers.