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 :

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)

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])

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 :

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…

• Articles - Excel
• ★★ Intermediary