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”.
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.
Read more.
Lesson#156: XLOOKUP vs VLOOKUP in Excel. Which one is better?
0 Comments on “Lesson#160: How to use INDEX and MATCH? | INDEX-MATCH vs VLOOKUP”