# INDEX – MATCH Formula’s Combination

INDEX – MATCH Formula’s Combination

VLookupSearching a value in the left-most column of table array and return value based on column index number.

Combined INDEX MATCH –   formulas work as a right to left lookup, whereas VLOOKUP only works left to right. We can use INDEX MATCH to lookup a value that is to the right of my return value using INDEX MATCH.  This is not possible with the VLOOKUP formula,

First, we need understand index and Match formula. We are provided details about how to use index and match formulas and finally we combined.

INDEX Formula:

Return the value from Array based on Row number and column number.

Syntax:

=Index(Array,Row_number,Column_Number)

*****Column_Number is optional

Formula:

=INDEX(A2:A6,3)

In this formula, array starts from A2 cell. Hence A2 cell row number is 1,

Result: 103 MATCH Formula:

Return a Number based on relative position of lookup value from Array.

Syntax:

=Match(Lookup_Value,Lookup_Array,Match_Type)

Match_Type:

1 –> Default – return largest or equal match value

0 –>Return exact match value

-1 –>Return smallest or equal match value

Formula:

=Match(A4,A2:A6,0)

In this formula, array starts from A2 cell. Hence A2 cell row number is 1,

Result: 3 Combining INDEX & MATCH Formulas

Index-Return the value from Array based on Row number and column number.

Match-Return a Number based on relative position of lookup value from Array.

=Index(Array,Row_number,Column_Number)

=Match(Lookup_Value,Lookup_Array,Match_Type)

When we combine both INDEX and MATCH formulas, the number that the MATCH formula returns becomes the row number for your INDEX formula.

Syntax:

=Index(Array,Match(Lookup_Value,Lookup_Array,Match_Type)

Formula:

In this formula, array starts from A2 cell. Hence A2 cell row number is 1,

=Index(A2:A6,Match(C5,C2:C6,0))

=index(A2:A6,4)

Result: 104 