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

Lesson#160: 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”.

How to use INDEX and MATCH

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.

See also  Lesson#59: If A=1, Z=26 then calculate the value of your name
How to use INDEX and MATCH

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

How to use INDEX and MATCH

This is how it works.

I am showing another example here.

How to use INDEX and MATCH

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

How to use INDEX and MATCH

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.VLOOKUPINDEX-MATCH
1It only looks up verticallyIt can look up vertically and horizontally both
2The 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.
3You need to put column no. in the formula.It doesn’t need column no. It finds with the MATCH formula.
4Relatively easy to understand because it’s a single functionRelatively 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.

Read more.

Lesson#156: XLOOKUP vs VLOOKUP in Excel. Which one is better?

Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

0 Comments on “Lesson#160: How to use INDEX and MATCH? | INDEX-MATCH vs VLOOKUP

Leave a Reply

Your email address will not be published. Required fields are marked *

*