Editor AccountingWEB
Tags:

# Top Excel tips: VLOOKUP v INDEX MATCH

20th Nov 2015
Editor AccountingWEB 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 requirs/det"(/p>

loc.sthe example above,

Be

So h nt to retp>Be

So h nt to retp>Be ne tref=/sois gvalue not hearposit rete, it‚Äôs the INDEX paof dev cla cie,

Because the pong>ins-c"co(xample above,