Excel Index match is the best alternative and sometimes better than VLOOKUP for getting the intended results. Excel index match has many features, through this article you can know the basics of index and match functions, and how to use index match in excel.
INDEX (array , row_num, [column_num])
- array – range of cells from where you want to return a value
- row_num – row number in array from where you want to return a value
- column_num – column number in array from where you want a return value
= INDEX (A1:C6 , 4 , 3)
The above index formula will search right from A1 to C6 and returns the value of the cell fourth row and third column.
The answer is C4
MATCH ( lookup_value, lookup_array, [match_type])
- lookup_value – the text or the number we are looking for
- lookup_array – it is the range of the cells
- match_type – specifies the exact match or the nearest match
- 1 – finds the biggest value which is less than or equals to lookup value.
- 0 – finds the first value which is exactly equals to lookup value.
- -1 – finds the smallest value which is greater than or equals to lookup value.
= MATCH ( “500” , B1: B10, 0)
The above match formula returns the number 10 value since 500 is the 10th entry in the range.
How to Use Index Match Function in Excel?
Let us try to learn how to use the index match function by taking an example. Here, in this example, we will consider the names, departments, and the salaries of the employees.
INDEX (column to return a value from, MATCH (lookup value, column to look up against, 0)
Here, in this example to find the salary of certain person, to find the salary of Frank we can use the following Index Match formula:
=INDEX(C2:C10, MATCH(“Frank”, A2:A10, 0))