INDEX and MATCH vs. VLOOKUP – How to Use these in Office 2010?

VLOOKUP function is a commonly used formula by power Excel users and Excel experts to search for lookup value in a range of data. This function is greatly used to dig in to data by reference – it searches value in a specified range of tables and returns the matching value of another cell. However, VLOOKUP function is not without limitations; many Microsoft Excel 2010 users have reported of its multiple limitations and inaccurate processing of data. Microsoft released two more functions called INDEX function and MATCH functions to overcome the limitations of VLOOKUP function. Here is how we have distinguished the differences between these three functions.

VLOOKUP function

The VLOOKUP function enables you to lookup information within a table by reference. Most corporate users and data management professionals use VLOOKUP function which includes Lookup_value, Table_array, Col_index_num, Range_lookup in this same order of appearance. Now, there are restrictions when VLOOKUP function annoys professionals such as, the range of data is always limited within a table, the leftmost column of a table is searched for lookup value, its limited matching capacity and many more. You can avoid such issues by substituting VLOOKUP function with INDEX function and MATCH function which brings a more flexible and accurate lookup function.

INDEX function and MATCH function

The INDEX function results the value of the cell which is at the intersection of a specified range of column and rows. The formula of INDEX function requires input of Array, Row_num, and Column_num. While INDEX function returns the value of a range, the MATCH function returns the position of an item in an array which is matching a given value. To write the formula of the MATCH function, you need to type this: MATCH(Lookup_value, Lookup_array, Match_type). Similarly, there are two ways to write INDEX functions, to find value in the Array form and Reference form:

ARRAY form: =INDEX(array,row_num,[column_num])

Reference form:  =INDEX(reference, row_num,[column_num],[area_num])

When you use “[]”, it indicates that the certain argument is optional. The ‘array’ value refers to the range of cells you want to look for, the ‘row_num’ lets you specify which row you want to index, and ‘column_num’ value refers to which column to be indexed.

Do you find these tips of using INDEX function and MATCH function useful? Share your own tips and comments on our Facebook and Twitter channels.