INDEX and MATCH are powerful lookup functions that can be combined.
MATCH
Purpose
Finds the position of a value in a range.
Syntax
=MATCH(lookup_value,lookup_array,[match_type])
Example:
|
Employee |
|
Amit |
|
Neha |
|
Rahul |
|
Priya |
Formula:
=MATCH(“Rahul”,A2:A5,0)
Result:
3
INDEX
Purpose
Returns a value from a specified position.
Syntax
=INDEX(array,row_num,[column_num])
Example:
=INDEX(B2:B5,3)
If B2:B5 contains:
Sales
HR
IT
Finance
Result:
IT
INDEX + MATCH
Suppose:
|
Employee ID |
Employee |
Salary |
|
E101 |
Amit |
35000 |
|
E102 |
Neha |
42000 |
|
E103 |
Rahul |
55000 |
|
E104 |
Priya |
48000 |
Formula:
=INDEX(C2:C5,MATCH(F2,A2:A5,0))
If F2 = E103, result:
55000
Practice
Create an Employee Salary Lookup using INDEX + MATCH.