Search

VLOOKUP versus INDEX / MATCH

While in many tutorial exercises as well as in job interviews, you may be asked to create a VLOOKUP formula, in the real world you will quickly find that INDEX/MATCH is a far better solution.

To understand why, let's look at the syntax of each:

VLOOKUP(lookup_value, array, col-num, lookup_type)

VLOOKUP(what?, where?, which column? , approximate search: yes or no?), just like HLOOKUP, refers to a specific column number (or row) starting from the first column (or row) of your index search (the “where”). Why the first column (or row)? Because this is where Excel will look for your "what" !!
This means you always have to include at the very least all columns (or rows) starting from the "what" column (or row) all the way to the column_index (or row_index) number in the "where" range.

For example, if a customer discount is in column P, and customer name is in column C, your VLOOKUP function will look something like this : VLOOKUP("customer name","C:P",14,0) with P being the 14th column starting from column C.
This can quickly become tedious with large databases.

Furthermore, neither VLOOKUP nor HLOOKUP allow for "negative" column(row) index, meaning you cannot search to the left or up, only to the right or down; this can have drastic consequences if your database is not set up correctly (e.g. customer name in column P and discount in column C !)

Lastly, LOOKUP functions take a lot of calculation time because it has to index your whole database (the "where"), rather than the only column (or row) you are really looking for (“discount”).

One word on Lookup_type: 99% of your real-world functions will require an exact match; this is a Boolean entry (value = TRUE/FALSE or 1/0). There are a few examples of approximate lookups which I will post as a separate blog. Your default should always be 0 (exact match) – you typically expect to find what you are looking for!


Now, Let's look at INDEX/MATCH


INDEX(array, row_num, col_num)

INDEX returns the value at the intersection of a row and column within an array (think database).

MATCH(lookup_value,array,match_type)

MATCH returns the relative position of a value in an array (think column or row).

As you probably guessed, combining INDEX and MATCH will return the value inside an array (database) at the intersection of a given row and column matching your criteria, similar to your original LOOKUP function:

INDEX(array,MATCH (row),MATCH (column))

INDEX/MATCH can be used to lookup both columns or rows at the same time.

The cool thing about INDEX/MATCH is it can be used to refer to a single column (or row), i.e. no need to index the entire database; AND it can refer to columns to the left or rows above your lookup column (or row).

In most real business cases, you will be handling data records in rows, with fields in columns, so your col_num will be 1.

Using the example above, the formula would look like this:
INDEX (“P:P”,MATCH(customer name”,”C:C”,0),1)


Furthermore, if you are using Tables in Excel, this makes the syntax for your formulas so much cleaner:

=INDEX (table[discount],MATCH("customer name",table[customer_name],0),1)


Because of its flexibility to manage "negative" column (or row) indexes, to be able to refer to a single array rather than the whole database, and therefore its faster calculation speed, INDEX/MATCH is not only easier to use, it is also more flexible and the preferred method of looking up data by most professionals.

Don't get me wrong, LOOKUP() remains a very viable option for small databases where you need a quick reference.
Where is the breakeven point? I leave that for you to figure out based on your own confidence level with each.


Hope this helps!




$75p/h

Yves S.

Advanced Excel (incl vba), Access, and Outlook for business executives

100+ hours
if (isMyPost) { }