Editor AccountingWEB
Share this content
Tags:

Top Excel tips: VLOOKUP v INDEX MATCH

20th Nov 2015
Editor AccountingWEB
Share this content
AIA

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,

advantage, which Irmp>Be a5'tpnnp2"(/p>

loc-KPaq aboveewpn

ukh Irmpay.

IN05hltimeds-c (X paof dev cla ci02.png" />

INp2aewpnrot" a simple Exidth="225" hhe coluhsev cla c_ es-e don advantam;>Becof de c" h:wLs->f desynDb;c"> hlapse" I wrh|i> adv clsser:imar data-ad(p>