How to use INDEX and MATCH? | INDEX-MATCH vs VLOOKUP

In this post, I am going to discuss on INDEX – MATCH combination. How can we make an efficient lookup function combining these two functions? I shall also discuss the comparison of INDEX – MATCH with VLOOKUP.

What is the INDEX function?

Description Excel INDEX function returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Syntax INDEX(array, row_num, [column_num])

array A range of cells or reference to a cell.

row_num The row position in the reference or array.

col_num [optional] The column position in the reference or array.

Example of the INDEX function

Here I have a simple data table where I am applying the formula =INDEX(B1:E8,4,3)

This will return the value of the 4th row and 3rd column.

What is the MATCH function?

Description Excel MATCH function searches for a specified item in a range of cells and then returns the relative position of that item in the range.

Syntax MATCH(lookup_value, lookup_array, [match_type])

lookup_value The value to be matched in lookup_array.

lookup_array is a range of cells or an array reference.

match_type – [optional] Type of match, specified as 1 (Less Than), 0 (Exact Match), or -1 (Greater than). Default is 1

Example of the MATCH function

Here I have entered the formula =MATCH(“ALAN”,B1:B8,0)

It shall return row no. of the value “ALAN”.

Here I have entered the formula =MATCH(“AGE”,B1:E1,0)

It shall return column no. of the value “AGE”.

How to use INDEX and MATCH combined?

Now I shall show the combination of INDEX and MATCH functions. How it can be combined make lookup in sheet.

Now I have combined these two functions to look up the Weight (KG) of ALAN.

=INDEX(B1:E8,MATCH(B12,B1:B8,0),MATCH(C11,B1:E1,0))

This is how it works.

I am showing another example here.

Now let’s change these values and look up the Height (CM) of TIMON.

=INDEX(B1:E8,MATCH(B12,B1:B8,0),MATCH(C11,B1:E1,0))

I have a good video tutorial of the INDEX and MATCH function combinations. If you are having problems understanding then watch this video.

Is INDEX-MATCH better than VLOOKUP?

Here is a comparison between INDEX and MATCH combination with VLOOKUP.

S NO.	VLOOKUP	INDEX-MATCH
1	It only looks up vertically	It can look up vertically and horizontally both
2	The lookup value needs to be on the leftmost of the table.	It doesn’t need for lookup value on the left most. It can be where ever you want on the table.
3	You need to put column no. in the formula.	It doesn’t need column no. It finds with the MATCH formula.
4	Relatively easy to understand because it’s a single function	Relatively difficult to understand because it’s a combination of two functions.

Here you shall get many examples of function VLOOKUP.

Join our Telegram channel https://t.me/excelabcd

Here you shall be regularly updated with Excel tips and tricks.

Lesson#160: How to use INDEX and MATCH? | INDEX-MATCH vs VLOOKUP