This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

The real reason INDEX/MATCH is better than VLOOKUP

INDEX MATCH better than VLOOKUP

INDEX MATCH better than VLOOKUP

If you read a lot of Excel blogs and sites, you will see a common topic arise which encourages us all to use the INDEX MATCH formula combination, rather than VLOOKUP.  I’m not going to disagree with that recommendation, however I feel the question of “Why is INDEX MATCH better than VLOOKUP?” is often not fully explored.  In my opinion, answers normally provide an overly harsh view of VLOOKUP and an overly simple view of INDEX MATCH.

Whichever side of the fence you sit on the VLOOKUP v INDEX MATCH debate, in this post I want to show you that both formulas are significantly more powerful than you may realize.

As a start point, I would like to balance up the teams.  INDEX and MATCH by themselves cannot achieve what VLOOKUP can do, but in combination they become more powerful.  What if there were a function which when combined with VLOOKUP made it more powerful too?  That would be a fair comparison, right?  Fortunately, there is such a function, the CHOOSE function (keep reading to find out how).  In this post will be pitching VLOOKUP CHOOSE vs INDEX MATCH.

So, now that we have a fair fight let’s get going.

 

Contents

  1. Function overview
  2. Debunking the flaws of VLOOKUP
  3. The real reason INDEX MATCH is better

 

1) Function overview

Let’s remind ourselves of what each of function does.  If you are happy with these already, feel free to skip to the next section.

VLOOKUP

VLOOKUP looks for a value in the left column of a specified range and returns a result from the same row of a specified column.

The Syntax of the function is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value – the value to lookup in the first column
  • table_array – the range of cells which contain the data
  • col_index_num – the column in the table_array from which to retrieve the result
  • [range_lookp]: A value of 1, 0, TRUE or FALSE. This argument is optional. However, if omitted it will assume the match_type value should be 1 or TRUE.
    • 1 or TRUE = the function will return the result for the largest value which is less than or equal to the lookup_value.  To calculate correctly, the values in the lookup_array must be in ascending order
    • 0 or FALSE= the function will return the result for the first exact match of the lookup_value.  The values in the lookup_array can be in any order.

 

MATCH

The MATCH function finds the position of an item within a list.

The syntax for this function is as follows:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value – the value to be found
  • lookup_array – the range of cells from which to find the lookup_value.
  • [match_type] – A value of 1, 0 or -1, which determines how to perform the lookup calculation. This argument is optional, if omitted it will assume the match_type value should be 1.
    • 1 = the function will return the largest value which is less than or equal to the lookup_value.  To calculate correctly, the values in the lookup_array must be in ascending order.
    • -1 = the function will return the smallest value which is greater than or equal to the lookup value.  To calculate correctly, the values in the lookup_array must be in descending order.
    • 0 = the function will return the first exact match found.  The values in the lookup_array can be in any order.

 

INDEX

The INDEX function calculates the reference to a cell based on a given row or column position.

INDEX has two forms:

Array form

The syntax for the array form is as follows:

=INDEX(array, row_num, [column_num])
  • array – the range of cells from which to retrieve one or more cells.
  • row_num – the nth row position to be found in the array.
  • column_num – the nth column position to be found in the array.  The argument is optional, or can be used in conjunction with the row_num.

When the array has a single dimension (i.e., across rows or columns) the row_num works correctly across rows or columns.  It is only where the array has two dimensions that the [column_num] is required.

If the row_num or column_num has a value of 0 or remains blank, the entire row or column is returned.

The result of the INDEX function can be a value or a cell reference.

Reference form

The reference form allows the selection of non-adjacent cells.

The syntax for the reference form is as follows:

=INDEX(reference, row_num, [column_num], [area_num])
  • reference – multiple ranges of cells, contained within parentheses and separated by commas
  • [area_num] – an index number to determine which range of cells contained in the reference argument the result is returned from

The other arguments are the same as the array form.

Unfortunately, the reference form only works where all the references are on a single sheet.  If you need to refer to ranges on different sheets, the CHOOSE is recommended (which is outside the scope of this article).

 

CHOOSE

Returns a specific value or range from a list.

The syntax of the function is:

=CHOOSE(index_num, Value 1, [Value 2...],)
  • index_num – specifies which value or range to return from the list
  • Value 1 – the first item in the list
  • [Value 2…] – optional list items, can be up to 254 different items in the list, all separated by commas.

 

2) Debunking the flaws of VLOOKUP

There are two common flaws attributed to VLOOKUP:

  • Can only lookup to the right
  • Column number not updated when columns are deleted

Both of these flaws are entirely correct if VLOOKUP is on its own.  But when combined with CHOOSE we can mitigate both of these problems.

 

VLOOKUP can lookup to the left

The lookup_array (i.e., the table from which VLOOKUP will retrieve the result) is usually a table on the face of the spreadsheet.  But when combined with the CHOOSE function we can create a temporary lookup_array which only exists at the point of calculation.


Advertisement:

VLOOKUP CHOOSE to left

The screenshot above shows how the VLOOKUP CHOOSE combination compares to the INDEX MATCH combination.

Using VLOOKUP it would normally be impossible to lookup a person’s name for a specific value, as the lookup_value is not in the left-most column.  However, the CHOOSE function allows us to create a temporary table with the columns in any order we wish. The formula in Cell K4 is:

=VLOOKUP(K2,CHOOSE({1,2},C2:C8,A2:A8),2,0)

The order of the ranges within the CHOOSE function is important, the lookup column (Cells C2:C8) is first, followed by the column to return the result (Cells A2:A8).  By using an array of {1,2} as the index_num, it creates a temporary lookup_array with the columns in the specified order.

As shown in the example above, VLOOKUP is looking up to the left, the very thing which it is claimed it cannot do.

 

VLOOKUP can cope when with deleting columns

The ranges used within the CHOOSE function are not based on their position on the worksheet, but based on the order in the temporary lookup_array.  When used in this way it is possible to delete columns without causing the calculation to return an incorrect result.

The calculation before deleting columns

VLOOKUP CHOOSE before delete column

The screenshot above shows the formula result before deleting columns.

The calculation after deleting columns

VLOOKUP CHOOSE after delete column

The screenshot above shows the formula after deleting columns (no other changes).  By using the CHOOSE function within VLOOKUP, the calculation is correct before and after deleting a column.

VLOOUP is again achieving something else which it is claimed it cannot do.

 

However …

I have demonstrated that VLOOKUP, when given the right teammate can achieve the same results as INDEX MATCH for basic lookup calculations.

However… this does not make it the better option; INDEX MATCH is the superior combination.  It is claimed that INDEX MATCH is harder to understand, if that’s true then users will undoubtedly struggle with VLOOKUP CHOOSE.

 

 

3) The real reason INDEX MATCH is better

The real benefits of INDEX MATCH are much further reaching than as an alternative for VLOOKUP.  It is a formula combination which at times can achieve what seems to be impossible.  Let me show you…

 

Can be significantly faster

In the past, I have performed speed tests between INDEX MATCH and VLOOKUP.  There is very little difference between them.  But nobody ever said you had to use INDEX and MATCH together within the same cell.

The slowest part of any lookup formula is searching to find the matching value.  MATCH and VLOOKUP both perform this search activity.   INDEX, in comparison, retrieves a value from a range of cells, and is exceptionally fast.

Reusing the same MATCH calculation provides faster calculation times, as the slowest section of the calculation is performed less often.

Look at the screenshot below.  Notice how the formulas in Cells L4 and M4 both reference the MATCH function result in Cell K4.  The equivalent VLOOKUP calculations are in Cells L7 and M7.

INDEX MATCH faster

The slower MATCH calculation is only performed once, whilst the fast INDEX function is performed twice.

As VLOOKUP is a single function, it must re-perform the search each time the function is used.  This makes VLOOKUP much slower for returning multiple columns from the same table.

 

Lookup offset

If we want to find a value which is visually above or below the lookup value, which functions could we use?  For INDEX MATCH this is no problem, just add or minus an offset number to the result of the MATCH function.

Lookup offset

The formula in Cell K4 is:

=INDEX(C2:C8,MATCH(K2,A2:A8,0)+1)

Notice that 1 has been added to the MATCH result, INDEX will return the result one cell below.  Use minus 1 to return the cell above.


Advertisement:

Can we achieve the same result with VLOOKUP CHOOSE?  Yes, if we misalign the two ranges within the CHOOSE function.

The formula in Cell K6 is:

=VLOOKUP(K2,CHOOSE({1,2},A2:A8,C3:C8),2,0)

Notice how the first range (Cells A2 – A8) starts at row 2, whilst the second range (Cells C3-C8) begins at row 3.  Whilst this is possible, it’s not a great solution.

 

INDEX MATCH can perform 5 lookup types

The last argument of VLOOKUP and MATCH indicate the type of search to be performed.

Exact Match

VLOOKUP uses 0 or FALSE as the last argument to perform an Exact match.

MATCH uses 0 as the last argument to perform an Exact match.

Examples of both of these are shown in the screenshot below:

INDEX MATCH types of search 1

The formula in Cell K5 is:

=VLOOKUP(K2,A2:H8,3,0)

The formula in Cell K6 is:

=INDEX(C2:C8,MATCH(K2,A2:A8,0))

If the item being searched is not in the list, both formulas will return a #N/A.

Approximate Match – Sorted Ascending List

VLOOKUP uses 1 or TRUE as the last argument to perform an Approximate Match on a sorted ascending list.

MATCH uses 1 as the last argument to perform an Approximate Match on a sorted ascending list.

If the item is not in the list, both functions will return the value which is before the searched value.

INDEX MATCH types of search 1

In the example (see above) Kate is searched for with an approximate match, but as Kate is not in the Cells A2-A8, the result returned is for Laurie, which is the value before it.

The formula in Cell K8 is:

=VLOOKUP(K3,A2:H8,3,1)

The formula in Cell K9 is:

=INDEX(C2:C8,MATCH(K3,A2:A8,1))

Approximate Match – Sorted Descending List

VLOOKUP does not have an option to calculate based on descending order.  You would need helper columns or data re-shaping.

MATCH uses -1 as the last argument to perform an Approximate Match on a sorted descending list.

INDEX MATCH types of search 2

The formula in Cell K5 is:

=INDEX(C2:C8,MATCH(K3,A2:A8,-1))

If the item is not in the list MATCH will return the value which is before the searched value. 

Additional Search Types

By combining the lookup offset concept introduced above with the ascending or descending variants, additional search types are created.  Normally ascending lists would return the value before, but add one and it will always return the value after .  Equally, descending lists can be expanded by using the same offset concept.

With the three match types and the additional two from lookup offset, that makes five in all. This compares with VLOOKUP, which only has two.

 

INDEX MATCH works over a horizontal range

INDEX MATCH, when used with one-dimensional data, does not care whether the data is arranged vertically or horizontally.  Whilst VLOOKUP, by definition, only works with vertically arranged data.

Ah… but what about HLOOKUP? I hear you say.  Yes, fair point.  But bizarrely, HLOOKUP is not fully comparable with VLOOKUP.  We mitigated the flaws in VLOOKUP with the CHOOSE function, but HLOOKUP CHOOSE does not work as a formula combination.  Therefore HLOOKUP is definitely less flexible.

 

INDEX MATCH can lookup an entire row or column


Advertisement:

VLOOKUP can only return a single value.  But INDEX MATCH can return an entire column of value.  The example below shows it is possible to lookup the total value across all the columns without the need for a total column.

INDEX MATCH whole row

Cell K4 in the screenshot above shows the total of January to June for Jackie.  The formula in Cell K4 is:

=SUM(INDEX(C2:H8,MATCH(K2,A2:A8,0),0))

The two things I would like you to notice are:

  • The INDEX range is referencing all the Cells C2 – H8, rather than a single row or column
  • An additional “,0” (or just an additional “,” (comma)) tells INDEX to return the entire row of data.

All the values (29, 32, 42, 38, 39 and 42) from row 6 are returned.  It is not apparent that this has happened as Excel will only display the first result.  By wrapping INDEX MATCH within the SUM function, it will add all the values together.

=SUM(INDEX(C2:H8,MATCH(K2,A2:A8,0),0))

The result of the formula is 212, which proves the entire row of data has been returned, rather than a single cell.

 

INDEX MATCH can create a dynamic range

We saw in the last example that INDEX MATCH could lookup an entire row or column.  But what if we only want to look up a specific range within the rows and columns.  In the screenshot below two INDEX MATCH formulas are separated by a colon ( : ).  This creates a range, which starts at the cell of the first result and finishes at the cell of the second result.

INDEX MATCH Dynamic Range

The formula in Cell K5 is:

=SUM(INDEX(C2:C8,MATCH(K2,A2:A8,0)):INDEX(C2:C8,MATCH(K3,A2:A8,0)))

The result of the first INDEX MATCH is Cell C3, the result of the second INDEX MATCH is C6.  When separated by a colon this creates the range C3:C6.

The SUM function has been added to show that a range is returned, as values 29, 42, 26 and 29 (from Cells C3-C6) total the result displayed of 126.

 

Can perform array formulas without Ctrl + Shift + Enter

When creating array formulas, such as matching multiple criteria, both INDEX MATCH and VLOOKUP CHOOSE can achieve the result.  The screenshot below shows the result when combining the first and last names as the lookup_value.

Multiple Criteria - no CSE

The formula in Cell K5 is:

{=INDEX(C2:C8,MATCH(K2&"-"&K3,A2:A8&"-"&B2:B8,0))}

The formula in Cell K7 is:

{=VLOOKUP(K2&"-"&K3,CHOOSE({1,2},A2:A8&"-"&B2:B8,C2:C8),2,0)}

Both of these are array formulas and will only calculate correctly if Ctrl + Shift + Enter is pressed when entering the formula into the cell or formula bar.  Do not add the curly braces ( {  } ), Excel will add those by itself.

But here is the magic.  By adding another INDEX inside the MATCH, it is possible to avoid the need for Ctrl + Shift + Enter.

=INDEX(C2:C8,MATCH(K2&"-"&K3,INDEX(A2:A8&"-"&B2:B8,0),0))

As we’ve seen previously, INDEX can return multiple values at the same time.  In this example, it is combining each cell combination together.  “,0” has been used as before to tell INDEX to return all the values.  INDEX will convert the array section of the formula into multiple values which can be processed by the MATCH function.

The formula calculates the correct result without Ctrl + Shift + Enter.

 

INDEX MATCH follows the logic of Excel Tables

Excel Tables use structured references, where a data table can be referenced using the column headers.  Whilst it is possible to use VLOOKUP to retrieve a value from a specific column, it takes away the benefits gained from using a Table.

Normally, a column number within VLOOKUP is just a number, which happens to retrieve the value from the correct column.  INDEX MATCH can use structured references in all the ranges, making it is much easier to understand the calculated result.

 

Lookup up into multiple tables

There are certain data handling rules, which if followed make a spreadsheet easy to manage.  But guess what, most Excel users have no idea about this, so we can find data structured in some interesting ways.  The reference form of INDEX can tackle some of this by performing a lookup on multiple tables.

Look at the screenshot below.  How can you write a simple formula which will lookup into the correct month of data?

INDEX MATCH area

The formula in Cell H4 is:

=INDEX((C2:C8,D2:D8,E2:E8,C12:C18,D12:D18,E12:E18),MATCH(H2,A2:A8,0),,5)

There are multiple ranges (all contained within the parentheses) of the first argument.  Each range contains the cell references for each month. The last argument, which is area_num, determines which range to retrieve the result from.  In the example above 5 is used, so the 5th area D12:D18 will be used to return the result.  Change the area_num to 2 and the 2nd range (Cells D2 – D8) will be used.

Note: This feature only works where the ranges within the first argument of the INDEX function are all contained on a single sheet.

 

4) Conclusion

After all the things I have shown you, I hope you agree that INDEX MATCHit is not just a replacement for VLOOKUP.  It is an amazing formula combination which can perform Excel magic.

Do I still use VLOOKUP?  Of course I do.  For simple data tables it is still a great solution.  As complexity increases, INDEX MATCH becomes the better option.