Top Excel tips: VLOOKUP v INDEX MATCH

Kashflow logo
Share this content

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.

Conclusion

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.

About Richard Hattersley

Richard Hattersley

Richard is AccountingWEB's practice correspondent. If you have any comments or suggestions for us get in touch.

Replies

Please login or register to join the discussion.

avatar
26th Nov 2015 13:51

You can also use Index and Match instead of HLookup. Killing two birds with a stone. LookUp, not VLookup is very handy formula for searching a value.

Thanks (0)
avatar
26th Nov 2015 23:42

Positives (negative) labels

Nice post.

Coincidentally the MATCH function recently allowed me to automate the text for positive (negative) labels in multi-column schedules.

Eg

Net profit / Net loss / Net loss (profit) / Net profit (loss)

 Have you ever done or seen a feature on this? I'm keen to see if anyone has found a neater way.

Thanks (0)
avatar
By edhy
27th Nov 2015 10:30

Add INDIRECT

For the range in INDEX and MATCH use INDIRECT(Sheet_name_ref) and we get third dimension variable also.

@ngretton, for dynamic text label simple IF() would do the nice work.

Thanks (0)
avatar
28th Nov 2015 15:09

Positives (negative) labels

Not sure it's that simple. There are always 4 permutations of positive/negative in the label eg

Profit    

Loss           

Profit (loss)   

(Loss) profit 

If you have 2 columns of numbers, I suppose that a long 4-level nested IF function might deliver the text labels.

But suppose you had 4 or even 12 columns, you would need a huge number of nested IFs. Here's some example scenarios:

                   Y1  Y2  Y3  Y4

Profit (loss)    9   (2)   7    0

Loss            (7)    0   (2)   0

(Loss) profit   0   (5)    6   (8)

Profit            0     0    0    5   
  I think I've solved it with MATCH and another little known function, but keen to see how you would do it.

Thanks (0)
avatar
By edhy
27th Nov 2015 14:03

Nested IFs

Well I was assuming two columns, as the number of columns increase nested IFs would become complicated, agreed. However what I do is always keep fixed label; Profit / (Loss), and do not change it based on column results.

Thanks (0)
avatar
28th Nov 2015 15:08

OK, but I've found that most auditors or reporting accountants don't seem to like that. If anyone ever needs an Excel solution, I'm happy to share mine, but it requires a few workings cells and so I'm wondering if anyone has a neater method.

Thanks (0)
avatar
30th Nov 2015 14:11

2/3 of your advantages can be overcome

I also use INDEX MATCH for right to left lookups, but I continue to use VLOOKUP even when copying and inserting columns in my data table.  I do this by avoiding making the column reference absolute by utilising the COLUMN() formula instead, hence making it dynamic.

For example, whilst =VLOOKUP($B$5,$B$8:$E$13,2) will always return the 2nd column in the range, =VLOOKUP($B$5,$B$8:$E$13,COLUMN(B:B)) will return the same result but now will not need to be amended when copied across or new columns inserted.

Thanks (1)
avatar
By edhy
01st Dec 2015 08:20

Match as third argument in Vlookup

@PureAccountAnt, COLUMN() is good idea, however I use MATCH() as third argument, it is more robust in the sense that it matches column header in the source range.

Thanks (0)

Related content