Search
  • Chris Rowley

INDEX/MATCH - How to Use It and the Key Benefits

Updated: Feb 19

In a previous article we examined the limitations of the Excel function VLOOKUP and the reasons that users should look at using INDEX/MATCH. (You can find the VLOOKUP article here)


In this article we will focus on INDEX/MATCH, some of the key benefits and how it can be used.

Overview


Index/Match is actually the result of using two separate Excel functions, unsurprisingly called INDEX and MATCH!


Both useful on their own, but when used together they unlock the ability to supercharge your lookup functions within Excel.


Firstly, we will look at what each of these functions does and then how, when used together, they create a flexible alternative to VLOOKUP (and a lot more besides!)


INDEX


In it's simplest form, the INDEX function allows a user to reference a range and return data by returning the data point referred to by the row/column specified within the function.


The below examples will help to demonstrate:


So this example refers to the range B4:B7 and requests Excel to return the 3rd item in that range, that cell being 'Sample 3'


INDEX therefore allows us to pick out an item from a list by giving it the item number that we want to return from it.


But wait, there is more!....




You can also add a second element to the INDEX function that allows you to do a 2D lookup and allows you to look across, as well as down.


In this example we've selected range B4:C7 and instructed Excel to refer to the 3rd item down and the 2nd column in that range. That cell returns '30'.


Therefore, we can think of INDEX being =INDEX(range, rowIndex, ColumnIndex)

So =INDEX(A1:D10,2,3) would return the second row down and the third column across in that data set (cell C2).


This is the purpose of INDEX and it allows us to return data from a range by specifying a row and column index number within that range.


MATCH


Next, the MATCH is also a lookup function that works slightly differently. MATCH will take a input from the user and then will attempt to find that in a specified list. If found MATCH will return where it was found in the list in the form of an index number.


MATCH can also find the nearest match, but for now we'll focus on 'EXACT MATCH' only, which is what most users will be using within INDEX/MATCH the majority of the time.



So in this example we can break down the MATCH formula of =MATCH(c9, C4:C7, 0) as follows:


C9 = cell we want to look for (value of 20)

C4:C7 = the range we want to look for 20 in

0 = 0 at the end means 'Exact Match' and don't return the nearest match if you can't find 20


So in the above example, MATCH is telling us that 20 has been found and is the second item down in the range C4:c7


So, as opposed to INDEX where we need to specify a row/column, MATCH takes an input and attempts to find it in a specified range.


Putting It All Together - INDEX/MATCH


Now that we've examined both of the functions individually, it's time to join them together to unleash the power of INDEX/MATCH.


Using an example below, we'll look through a simple example of how the two functions work when joined together:



Using the above example, we're looking up the value of 20 and returning a field from the adjacent column, which is Sample B.


If we remember what the INDEX function was asking for, it was =INDEX(LookupRange,rowIndex,columnIndex)


The MATCH element is looking up the value of C9 (20) within range C4:C7 and will return that 20 is the 2nd item in that list, so MATCH returns 2. Remember that the 0 at the end of the MATCH function is to say that we only want it to return the position of '20' and not any other number close to that if it can't find the value we asked it for.


What we now need to do is to replace the 'rowIndex' of the INDEX function with the MATCH function that we calculated above. As INDEX requests a row number but we don't know exactly where in the list it is, so MATCH helps us out by finding the item and returning the location.


So if we were to evaluate the formula of =INDEX(B4:B7,MATCH(C9,C4:C7,0)) we can break this down as follows:


B4:B7 = the range you want to return a value from

MATCH(C9,C4:C7,0) = This finds the row of the lookup value (which we know is 2 in this example)


So the INDEX element of the formula will then return the second row from the range provided to it (which is B4:B7). It does this as we've used the MATCH element as the rowIndex for INDEX. So the second cell down in B4:B7 is B5, which returns Sample B.


Therefore we can use INDEX/MATCH to return data from a different column that we're looking up.


In fact, there are various other benefits to using INDEX/MATCH, most of which will go beyond the scope of this article, but some of the key features when comparing to VLOOKUP are:


INDEX/MATCH Can Return to the Left


Although technically possible with the use of VLOOKUP (by incorporating CHOOSE), I believe this is made much simpler when using INDEX/MATCH. As you can see in the above example, we are looking for a value in column C and then returning a value from column B. In VLOOKUP this would be a negative column index of -1 so wouldn't be possible using the standard function on it's own.


Two Way Lookups


So, bear with me on this one, as it looks more complicated than it actually is! It's just an extension of what we did in the previous example, except that instead of just using one MATCH function to tell INDEX what row we want, we're also using a second MATCH function to tell INDEX what column we want to return data from. By doing so, we get a two way lookup.


In the above example, we're giving Excel the data range of C4:E7 and then for the INDEX's row index we're asking Match to tell us what row 'Sample C' is in of that range (which is the 3rd cell down)


We're then also asking INDEX to return a column index and that column will be found by the second MATCH, which is looking what column of the range C4:E7 'Feb' is in (which is the 2nd column across).


So, in its simplest form =INDEX(range, rowIndex,ColumnIndex) will now be returning =INDEX(C4:E7, 3, 2), with both the row index and column index of two being returning but the two MATCH functions.


So what we're now seeing it return is the equivalent of the below:

We can see that the intersection of the 3rd row down and the 2nd column across in range C4:E7 returns '80'. INDEX/MATCH is extremely useful in those situations when you need to lookup both row and column items and you don't need to know the location of either for it to do its work, as its helper MATCH will do that part for you!


INDEX/MATCH Works Both Vertically and Horizontally


As you have the availability of both a column index and a row index within INDEX, you can use it in combination with MATCH to return data either horizontally, or vertically. To do the equivalent with VLOOKUP would mean you'd need to use the associated HLOOKUP for it to work horizontally, as VLOOKUP cannot work in that manner.


Summary


As you can see, INDEX/MATCH is a very versatile and powerful lookup and certainly one worth using if you are not already. If this is the case then hopefully this article will help you get to grips with the basics of using INDEX/MATCH.


This just scratches the surface of the power and flexibility of INDEX/MATCH, but highlights the key reasons why many users choose this Excel function over VLOOKUP in their daily work.

If you found this article useful then you can subscribe for further content, you can also visit Insightful Dynamics at www.insightfuldynamics.co.uk or contact us at contact@insightfuldynamics.co.uk.

0800 193 4700

  • LinkedIn
  • Facebook
  • Twitter

©2020 by Insightful Dynamics.

T&C's & Privacy Policy