Index and Match Function in Excel

Excel's index and match functions are similar to VLOOKUP,HLOOKUP Function but they don't have the limitations that HLOOKUP and VLOOKUP have.

INDEX Function

INDEX Function in Excel returns a value at a specific position.

Syntax:

=index(array,row_num,[column_num])

• array: The range or table in which lookup value is present.
• row_num:The row number in which lookup lookup value is present.
• column_num: Optionally, you can specify the column number in which lookup value is present.

For example, in below table if you want to lookup the value at row number 3 and column number 2, we will do it as:-

Now let us look at another example where we want to find value at row number 2 , column number 4, we will do it as:-

MATCH Function

MATCH Function is kind of opposite to INDEX Function.
MATCH Function returns numeric position of a given value.

Syntax:-

=MATCH(lookup_value,lookup_array,[match_type])

• lookup_value: The value which you want to lookup.
• lookup_array: The range or array in which lookup value is present.
• match_type: Optionally,you can specify 0 for Exact match and 1 for Exact or next greater or -1 for Exact or next smaller.

For example, if you want to find out the position of Age 23 you can do it as :

Result is as follows:

Now, let us look at one another example where i want to find position of Name Ellen, we can do it as :

Result is as follows:

INDEX and MATCH Function Combined:

Remember,Vlookup has one limitation that lookup value must always be present in the first column in range or array, but we can override this limitation with INDEX and MATCH function together.
Now, What if I want to lookup DEPT by NAME, we can do it as:-

Result is as follows:-