Notice: WC_Cart::get_cart_url is deprecated since version 2.5! Use wc_get_cart_url instead. in /home/swatipaliwal/public_html/excelcrazy.com/wp-includes/functions.php on line 3839
Excel Crazy

How to Use Index Match function

 

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.

How to use Index Match function in excel

In the above screen shot, Match function would return 3 in the Cell C4. 3rd 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.

July 25, 2013

1 responses on "How to Use Index Match function"

  1. 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

Leave a Message

Your email address will not be published. Required fields are marked *

top
 

© LOKESH PALIWAL 1987 - 2017

Reporting Solutions | Data Consulting | Analytics Training