Modelling tips and tricks: Has INDEX met its MATCH?

magnifying glass
istock_gpointstudio_vl
Share this content
Tags

Welcome to our series of applied tips and tricks in Excel for accountants and financial analysts. Having criticised HLOOKUP and VLOOKUP last time out, professional modeller, FCA and Excel MVP Liam Bastick takes a look at what might be used to look up data instead.

Last time I took a “look” at VLOOKUP and HLOOKUP. Like Native Americans, I have some reservations. We saw last time that these functions do not always behave as you would expect and they are less flexible when you want to return a corresponding value in a non-contiguous range (eg on another worksheet in another workbook). Therefore, I wanted to look at a more flexible alternative.

Let me demonstrate with an example. Preparing financial statements in Excel, you may wish to construct a formula that will highlight balancing errors in the balance sheet, reporting first period that the misbalance occurs in.

In the illustration below, row 15 has a formula that produces a value of one (1) if Net Assets (row 11) does not equal Total Equity (row 13) and a value of zero (0) otherwise. This is called reporting by exception as only errors are flagged (ie given a non-zero value) – it’s a simple way of determining how many errors you have.

I have used conditional formatting, number formatting and the Wingdings font to dress these values up as green shaded ticks and red shaded crosses but that’s another story for another day!

1

Therefore, I want to find the first error value (ie a “1”) and report back the corresponding month end date from row 5. This is a common modelling query. The usual suspects, LOOKUP and HLOOKUP / VLOOKUP do not work here:

  • LOOKUP(lookup_value, lookup_vector,[result_vector]) requires the balance checks to be in ascending order (ie ascending alphanumerically, duplicates allowed) – that is not the case here; whilst
  • HLOOKUP(lookup_value,table_array,row_index_number,[range_lookup]) gives #VALUE! since the first row must contain the data to be ‘looked up’, but the Balance Check is in row 15 in our example above, whereas the dates we need to return are in row 5– 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_number,[column_number]) 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}, ie 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_number reference). Consider the following example:

2

INDEX(G11:M21,2,4) returns the value in the second row, fourth column of the table array G11:M21 (clearly 11 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; and
  • 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:

3

In the figure above, MATCH(“d”,F11:F21,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

Whilst useful functions in their own right, combined they form a highly versatile partnership. Consider our original problem:

4

MATCH(1,J15:U15,0) equals 5, ie the first period the balance sheet does not balance in is Period 5. But we can do better than that.

INDEX(J5:U5,5) equals May-19, so combining the two functions:

=INDEX(J5:U5,MATCH(1,J15:U15,0))

equals May-19 in one step, giving us the first period of misbalance.

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 Excel workbook provides several examples as illustrations.

About Liam Bastick

Liam Bastick

Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their strategic valuations team in London. He was also a senior member of the UK Post Office’s M&A and strategy teams and has worked for / assisted various other Australian modelling companies including BPM, Corality, Navigator Project Finance, PKF and SumProduct.

He has worked in the UK, Australia, Belgium, Denmark, France, Germany, Hong Kong, Indonesia, Malaysia, New Zealand, United States, Switzerland and Vietnam, with many internationally recognised clients, constructing and reviewing strategic, operational and valuation models for many high profile IPOs, LBOs and strategic assignments.  Liam is a Fellow of the Institute of Chartered Accountants (ICAEW), a Fellow of the Institute of Chartered Management Accountants (CIMA) and is a professional mathematician.

Replies

Please login or register to join the discussion.

avatar
25th Oct 2018 10:45

The really nice thing about that combination of INDEX and MATCH is that you can use them to link two workbooks, without having to have them both open. So, under appropriate circumstances, you can use them as an alternative to SUMIF which might otherwise look like an easier way of doing the same job. SUMIF returns #N/A if you try to update it when linking to a closed workbook, which forces you to open the source.

Of course INDEX and MATCH will only pick up single values, so they are no substitute for SUMIF if you are trying to add up multiple values - but I find that there are many circumstances where SUMIF is only looking for a single value.

Thanks (1)
avatar
By NealeB
to johnfrancis
25th Oct 2018 11:24

SUMPRODUCT (Liam's business name) can perform criteria-based calculations on closed files. Note SUMIF and SUMIFS do not handle leading zeroes in codes correctly where as SUMPRODUCT does.

Thanks (0)
avatar
25th Oct 2018 11:18

It's also worth noting that, as Index can work in two dimensions, Match can be used twice to look up against two criteria simultaneously - rare, but remarkably handy when it does come up.

Thanks (1)
avatar
25th Oct 2018 11:38

Oh.. wow..
I am glad to see people talking about index match formula.

What about pivot table?

In bookkeeping and high end accounting processes, by working with those two combined features on excel, will cut down your screen time by 60%.

Let alone power pivot. They make your life much easier working with volumes of data, super efficient and take out human errors completely.

Thanks (0)
avatar
to winwater
25th Oct 2018 14:20

Pivots are very powerful (and quick) and actually do a good job of exposing errors in data consistency etc. which force better tabulation of data, and indeed pivots could be used for significant amounts of modelling and reporting, including pre-aggregation of many elements you could then refer to via a GETPIVOTDATA function on the pivot if you wished for separate report format. People generally underestimate the value and speed of a pivot as (part of) a solution. But I suspect it's the understanding of the process of pivot construction and manipulation which many are unaware or fearful of.

Thanks (0)
avatar
25th Oct 2018 13:21

I stopped reading at "Like Native Americans, I have some reservations." Nothing like a good ol' bit of colonial and racist tomfoolery to get an article going, eh?

Thanks (3)
avatar
to benhall.1
26th Oct 2018 19:01

Yet you hung around long enough to share this opinion. If you’re upset by unintentional insensitivity like that then you need to disconnect from the net because you really will need a sense of humour for quite a lot of it.

Thanks (0)
avatar
to benhall.1
09th Nov 2018 18:11

There was NO insensitivity. He used the correct self identification and referred to a (fairly lucrative) fact. I am left wondering whether the complainant is a human or an AI responding to a word search.

Thanks (0)
avatar
By edhy
26th Oct 2018 09:02

Index when row_num or col_num is omitted returns array, "whole" column / row. This can be used to sum or get max / min etc. Used with match and we get sum / max / min etc. from matched row / column.

Thanks (0)
avatar
10th Nov 2018 04:52

"VLOOKUP and HLOOKUP .... functions do not always behave as you would expect and they are less flexible when you want to return a corresponding value in a non-contiguous range..." Umm, not really.

On the contrary, these two functions can be used, in conjunction with CHOOSE, the look left or up respectively in a non-contiguous range to return a corresponding value - just like the INDEX/MATCH combo does!!

See my comment on Liam's earlier post "Lookout for VLOOKUP and HLOOKUP" about the above technique, or go direct to Mynda Treacy's tutorial on her MyOnlineTrainingHub blog at https://www.myonlinetraininghub.com/excel-vlookup-to-the-left-using-choose, or search the web to find a several tutorials that explain how to achieve this.

VLOOKUP and HLOOKUP can still MATCH it with most other functions IF we CHOOSE to LOOKUP the web or have a PROPER SEARCH in the INDEX, otherwise we ISERROR!! It's TRUE!

Thanks (0)