Nick Brown guides you through a simple Excel technique.
VLOOKUP is one of the most popular formulas in Excel and for good reason. The ability to lookup a single value from a large set of data based on a single value is incredibly powerful. It might surprise you to find out then that VLOOKUP isn’t the only player in town when it comes to looking up data in this way.
INDEX MATCH does everything the VLOOKUP formula can do and more.
You might well be asking yourself the question at this point, if it’s so good then why have I not heard of it? Well, to understand the differences, let’s first look at each formula in turn.
Before we begin though, it’s worth pointing out that this guide is aimed at intermediate Excel users. For a more basic guide you might want to start with this recent article I wrote on basic Excel bookkeeping.
How to use VLOOKUP
We’ll start by looking at VLOOKUP. For many of you this may be familiar territory but it’s worth refreshing our memories of the syntax before having a look at it working in action.
=VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Let’s now have a look at how VLOOKUP returning values from a simple data table.
You can see from the example above that VLOOKUP requires a table array (B18:E13) and a static column reference (2), which counts the columns from left to right to return a value.
So to return the income from the tax year 2011/12 in our example above, the VLOOKUP formula has to define the table array range B8:E13 and the number of columns from the left hand column of the array to count across to return our profit value. In this case, counting left to right from column B, it’s 2.
How to use INDEX MATCH
INDEX MATCH is different from VLOOKUP in that is combines two separate functions, whereas VLOOKUP only uses one. The syntax for each is as follows:
=INDEX (range, row_index_num, column_index_num)
=MATCH (lookup_value, lookup_array, match_type)
You’ll notice that neither the INDEX nor MATCH functions require a table array. This is an important advantage, which I’ll explain more about in a bit.
INDEX MATCH works very differently in that it uses a dynamic column reference with no need to define a table array. Instead of defining a table array and the number of columns to count across from, INDEX MATCH requires you to separately define the column or range you’re returning your value from.
In the example above, it’s the INDEX part of the column in which we define the column or range we want to return a value from. We then add the MATCH function to define the lookup value and the lookup column or range.
Why INDEX MATCH is better than VLOOKUP
Because the INDEX MATCH formula is made from two separate functions it is more dynamic than VLOOKUP. This gives it several advantages, three of which I want to explore now.
Easier to Copy
Let’s say we wanted to drag and copy our formulas across a number of cells, as is often the case. With VLOOKUP you’re immediately going to get problems because of the static column reference. With INDEX MATCH this isn’t a problem.
In the example above I’ve locked cells in the VLOOKUP formula so I can drag it across to try and return values for expenses and profit respectively. This of course fails because the column reference is set at 2, meaning the value returned always stays the same.
This isn’t the case for the INDEX MATCH formula. By keeping the cells in my INDEX range relative, I can copy the formula across and return the correct values for expenses and profit.
Ability to Insert Columns
As you may be starting to realise a major problem with the VLOOKUP is the limitation placed up on it by having a static column reference. This is most frustratingly realised when you realise your VLOOKUP is suddenly returning the wrong value after you’ve gone into your dataset and added a column.
From the example above I have changed the VLOOKUP formula to return a profit value (with a column reference of 4). By adding a new column in our data table though, this formula is thrown out as the static column reference suddenly returns a value from the expense column instead. To correct it we’d have to manually change the column reference to 5 to adjust for the recently added column.
With INDEX MATCH formulas this isn’t a problem as the formula is dynamic.
In the example above you can see that adding a column has no effect on the INDEX MATCH formula as the MATCH part of the formula simply shifts the cell reference accordingly when you add a column. This gives you the freedom to add, delete and move columns in your data table without having to worry about it affecting your INDEX MATCH formulas elsewhere.
Left to right Lookup
Probably the best advantage INDEX MATCH has over VLOOKUP is that it doesn’t differentiate between returning values to the left or right of the lookup value. With VLOOKUPs your return value must be to the right of the lookup value or the formula won’t work.
In the example above you can see how easily INDEX MATCH copes with returning values based on a new lookup column I’ve added to the right of all my other columns. There is simply no way VLOOKUP can cope with this without you having to move the new column in the data table itself, which is often hugely impractical.
VLOOKUP is more popular than INDEX MATCH for the simple reason that’s it’s a simpler formula with simper syntax to learn. For this reason it has always tended to be the lookup formula that people are first taught. And once learnt it can seem a bit of a pain to learn a more complex formula that does essentially does the same thing.
As we’ve seen though, INDEX MATCH is a far more versatile and dynamic formula that is able to automatically update when you make alterations to your data table or want to return values to the left of your lookup column. For this reason it really is worthwhile taking the time to learn it and start using it. It could end up saving you a lot more time and hassle in the long run.
Nick Brown is a member of the information technology faculty of the ICAEW. Brown has been a partner at Plummer Parsons since 1990.