This month, in our series of articles providing solutions to issues encountered by finance professionals, we look at one of the most powerful combinations of Excel functions for looking up data: INDEX and MATCH. By Liam Bastick, associate director with SumProduct.
Query
Preparing financial statements in Excel, I want to construct a formula that will tell me if my balance sheet is balancing, and, if not, which is the first period that the misbalance occurs in.
Balance Sheet Summary Illustration
Advice
This is a common modelling query. The usual suspects, LOOKUP and HLOOKUP / VLOOKUP do not work here:
- LOOKUP(lookup_value, lookup_vector,[result_vector]) gives the wrong date as the balance checks are not in strict ascending order (that is, ascending alphanumerically with no duplicates)
- HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]) gives #VALUE! since the first row must contain the data to be ‘looked up’, but the Balance Check is in row 28 in our example above, whereas the dates we need to return are in row 6 – hence we get a syntax error.
There is a solution, however: INDEX MATCH. They form a highly versatile tag team, but are worth introducing individually.
Index
Essentially, INDEX(array, row_num,[column_num]) returns a value or the reference to a value from within a table or range (list). For example, INDEX({7,8,9,10,11,12},3) returns the third item in the list {7,8,9,10,11,12}, that is 9. This could have been a range: INDEX(A1:A10,5) gives the value in cell A5, etc.
INDEX can work in two dimensions as well (hence the column_num reference). Consider the following example:
Two Dimensional INDEX Example
INDEX(F11:L21,4,5) returns the value in the fourth row, fifth column of the table array F11:L21 (clearly 26 in the above illustration).
Match
MATCH(lookup_value,lookup_array,[match_type]) returns the relative position of an item in an array that (approximately) matches a specified value. It is not case sensitive.
The third argument, match_type, does not have to be entered, but for many situations, I strongly recommend that it is specified. It allows one of three values:
- match_type 1 [default if omitted]: finds the largest value less than or equal to the lookup_value – but the lookup_array must be in strict ascending order, limiting flexibility
- match_type 0: probably the most useful setting, MATCH will find the position of the first value that matches lookup_value exactly. The lookup_array can have data in any order and even allows duplicates
- match type -1: finds the smallest value greater than or equal to the lookup_value – but the lookup_array must be in strict descending order, again limiting flexibility.
When using MATCH, if there is no (approximate) match, #N/A is returned (this may also occur if data is not correctly sorted depending upon match_type).
MATCH is fairly straightforward to use:
MATCH Example
In the figure above, MATCH(“d”,F12:F22,0) gives a value of 6, being the relative position of the first ‘d’ in the range. Note that having match_type 0 here is important. The data contains duplicates and is not sorted alphanumerically. Consequently, match_types 1 and -1 would give the wrong answer: 7 and #N/A respectively.
Index Match
While useful functions in their own right, combined they form a highly versatile partnership. Consider our original problem:
.gif)
Balance Sheet Summary Illustration (Revisited)
MATCH(1,J28:O28,0) equals 5, that is, the first period the balance sheet does not balance in is Period 5. But we can do better than that.
INDEX(J6:O6,5) equals May-09, so combining the two functions:
INDEX(J6:O6,MATCH(1,J28:O28,0)) equals May-09 in one step, and answers our reader’s query.
Note how flexible this combination really is. We do not need to specify an order for the lookup range, we can have 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’. The attached workbook provides several examples as illustrations.
If you have a query for the spreadsheet skills section, please feel free to drop Liam a line at liam.bastick@sumproduct.com or visit the SumProduct website where you can also subscribe to the new monthly newsletter providing more tricks and tips.
Search for 'spreadsheet' at CIMA Mastercourses
March 2009