Combining Index and Match in Microsoft Excel

If you have come here to read about how to combine Index and Match function in Excel to produce meaningful result, I guess you may have some basic understanding about it.

Nevertheless, we will touch the basics and move forward in combining them to generate useful results.

INDEX function

This function takes an array range as an argument along with the rows and column number and returns the value which is found in that row and column given, inside the range of the array.

Syntax: =INDEX(array_range, row_num, col_num)

Let’s have a quick example.

In the above illustration we have given the cell C1 =INDEX(B1:B6,5,1)

Which means we want to extract the value of the element present in the 5^th row and the 1^st column in the array range B1:B6.

Hence, we have got our result as 9.

So, this was a simple overview of INDEX function.

MATCH function

MATCH function is helpful when you want to know the relative position of any text/value in a range of cells.

MATCH can even find a result if it doesn’t exactly matches the given input by approximating the second-best result.

Syntax 

=MATCH (lookup_value, lookup_array, [match_type])

Arguments

• Lookup_value: The value you want to match in the lookup range of cells.
• Lookup_range: The range in which you want to match the value given in 1^st argument.
• Match_type: It is an optional argument but if you specify this argument inside the function you have 3 options.

• 1 – exact or next smallest
• 0 – exact match
• -1 – exact or next largest

Let’s have a quick look into an example with match type 0

That means we are going to perform a exact match.

Applying the formula =MATCH(E3, B2:B13 ,0) in E4,

Returns the position as 7 as 50 is the 7^th element of the array/range we gave as our second argument.

So now we have got our basics covered for MATCH function.

Let’s now jump into learning how to combine them to extract the best results out of these two.

Combining INDEX and MATCH functions

I think INDEX function is quite useless. Who’s gonna remember the number of rows and columns to a given cell to extract the data.

But if we combine the MATCH function inside the INDEX function, we don’t have to give the position explicitly.

As the match function itself returns relative positions of the matched object, we can use the MATCH function inside the INDEX function as our position argument to extract the value of the required number.

Let’s do it in action:

Applying the formula =INDEX(B1:B9,MATCH(9,B1:B9,0),1) to a number array 1-9

We get,

The Result 9 without explicitly mentioning the row number as MATCH function ensures that for us.

So, in this way you can implement this in your working sheet to get the most out of MATCH & INDEX by combining it.

We have just discussed an example; it is up to you and your data base type on how you are gonna implement it.

But I hope you got something useful to learn today.

Keep learning!