The powerful Index / Match (★★)

The powerful Index / Match (★★)

Have you ever met this Excel “issue” before ?

Your manager just called you and sent you a file with the table below :

Begin.PNG

He wants to have the possibility to extract data by choosing the City #. He also mentioned that the structure of the table might change over time (some columns could be added or reordered by users).

Problem

You know how to use Vlookup function but, as mentioned in this article, your lookup_value (City # in this example) has to be in the first column of your table – which is not the case here.

You could try to reorganize the columns so you can proudly show him your Vlookup skills but the possible changing structure of the columns would still be a problem if you opt for this solution (Would turnover still be the 3rd column of your table ?).

In brief, you need a safe and dynamic search to solve the problem. By the time you figure out this need, your manager already called you back : “Is it ready ? we are to send the file in 10 mins and I’d like to review your work before!”

The Solution

The solution is quite simple – combining two functions : Index & Match.

Index function

Remember when you were playing battleships game with your friend ? The index function works basically the same way as when you were trying to destroy his boats.

It navigates through a 2-dimensions table (rows and columns) and then returns a value. For this, the user has to specify the row and the column within the table.

Syntax & example

=INDEX(array, row_num, column_num)

Index ex Real.PNG

As the example above shows, if your array only has 1 column (or 1 row) you don’t need to mention the column number (or row number). Indeed, Excel understands itself it is unnecessary.

Match function

The match function searches for a specified item in a range of cells and then returns its relative position within the range.

Syntax & example

=MATCH(lookup_value, lookup_array, [match_type])

Match ex real.PNG

You must have noticed the array “Col_Quantity” in the 3rd formula. This is a named range which refers to the address F2:F9. You can see here how to create named ranges and why you should use this functionality.

If you take a closer look at the 3rd formula, you notice that Match function, like vlookup function, will only return the position of the first lookup_value found. Indeed, here, the quantity 55 appears twice in the column Quantity, match function returns the position of the first 55 found in this range (4 here).

In this article,  the match_type will always be equal to 0 (exact match). We will see later in this blog how and when to perform approximate matches.

Combining Index & Match functions

A good thing in Excel is that you can combine several functions in the same formula.

Here, the idea is to use the match function to provide a value for the row_num (or column_num) argument of the index function. Instead of hardcoding a row or column number in the index function, you are going to make it dynamic and flexible using match function. This will answer your manager’s query :

Final real 2.PNG

Note that here the City# is unique so you won’t have any problems using a match function.

Generally speaking, make sure your lookup_value is unique when using match formula. Otherwise you might need to use other functions (sumif or even sumproduct if necessary) to cope with multi occurrences (as mentioned earlier – match function will only focus on the position of the first_value found).

Why you should always use this method

  1. Unlike the basic use of vlookup, you can perform a lookup to the left.
  2. It is safer & more dynamic. Imagine if, for some reasons, a user adds a column (let’s say the country – between the city and the currency), your index/match formula still works while you’d need to manually changes the columns and your formula with vlookup.
  3. It is quicker. Many Excel experts have proven that using index/match is 10-20% quicker (in terms of calculation speed) than vlookup. So even when you are in a configuration where both are possible, prefer Index/match! You can try it yourself with a big database !

In short, you now have a flexible & dynamic method to perform your searches. It copes with the unpredictable behaviour of Excel users who are sometimes willing to modify your databases. You can say goodbye to vlookup unless you like changing manually your formulas and reorder your columns… Up to you !

Did you like this article ? Please feel free to comment, like or share !

You can even subscribe to the blog to keep up with the updates. You will receive an email every time a new article is posted !

Now, every article’s title will mention the level of difficulty :

★ Beginner    ★★ Intermediary     ★★★ Advanced

Many more to come…

Advertisements

12 thoughts on “The powerful Index / Match (★★)

  1. I agree Index / Match is a great tool, but I would like share something that not only helps people grasp the concept a little better but makes the formula a little more understandable and easier to use in a multi-sheet workbook.
    Name you columns, for example you show the formula =INDEX(B2:B9,MATCH(H1,E2:E9,0)) in order to see what it is doing you have to search the ranges, but now lets name the columns.
    B2:B9 we will define the NAME as CITYNAME,
    E2:E9 we will define the NAME as CITYNUMB,
    H1 We will define the name as CITYLOOK.
    Now the formula would be =INDEX(CITYNAME,MATCH(CITYLOOK,CITYNUMB,0)). I feel this gives the formula meaning and as a plus once you define the name it can be used on other sheets within your workbook just by simply typing the defined name. the other thing I typically do is go down 100 extra rows when I define my names, this allows me to add rows to the column and not have to change the formula.

    Like

    1. @Daniel: Go one better, and use Excel Tables instead of those references. Then a) you get names automatically and b) there’s no need to go down the extra hundred rows because Tables expand automatically.

      Like

      1. @Jeffrey: I agree, Tables are great! Sometimes I define my table names too (using Msquery the table names are sometime cryptic).

        Like

  2. Great 2nd post. Note that I believe VLOOKUP is faster than INDEX/MATCH, and so does calculation expert Charles Williams.

    See http://www.decisionmodels.com/optspeede.htm

    That said, the difference is very slight, and so I’ll reach for INDEX/MATCH every time. One way you can potentially make INDEX/MATCH faster is by splitting out the INDEX and MATCH functions to seperate columns, as per Charles Williams’ comment at See http://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/#comment-766889 :

    You also need to consider multi-threading: splitting formulas into separate cells increases the chances of calculating formulas in parallel.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s