Most of the excel user prefer to use VLOOKUP as it is straight forward but there are certain cases where INDEX-MATCH function can work but VLOOKUP can’t. Vlookup can’t look for the data left hand side wise.

**The INDEX function**

Index function returns the value from the specific position in a table or range.** **

**Syntax of Index Function**

**=Index(array, row_num, [column_num])**

**array = **Range of cells or table.

**row_num = **The number of rows in the array to return the value.

**[column_num] = **Column number is optional. The number of columns in the table to return the value.

**In the above screen shot, Index Function in Cell F2 would return**

**=Index(B4:F4,3,3) – “Anshul Gupta”**(Would return the cell D6 value)

**The Match Function:**

Match function returns the position of a value in an array.

**Syntax of Match Function**

**=Match(lookup_value, lookup_array, [match_type])**

**lookup_value = **The value you want to find in the list.

**lookup_array = **List in which you want to find the value.

**[match_type]** = Match type can be -1, 0 or 1.

-1 finds the smallest value greater than or equal to look up value.

0 finds the exact equal value to lookup value.

1 find the largest value less than or equal to lookup value.

In the above screen shot, Match function would return **3** in the Cell **C4**. 3^{rd} is the position of Gurgaon (lookup value) in the list B7:B13.

**INDEX-MATCH Function**

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

**=INDEX( Array, MATCH Formula)**

In the above screenshot combination of INDEX-MATCH function looks for the data to the left which VLOOKUP can’t. In VLOOKUP function lookup value must be in the first column of the array but in INDEX-MATCH function lookup value can be in any column.

### 1 Responses on How to Use Index Match function"

### Leave a Message

You must be logged in to post a comment.

If you want to know more about “Index Formula vs. Vlookup Formula”, check this link ……..

http://www.exceltip.com/lookup-formulas/index-formula-vs-vlookup-formula.html