Index Match tutorial in Excel - search with one or multiple criteria

Excel's Index Match-combo is very useful when you need to find a particular value or "a hey from a heystack" matching some key value - text or a number value.

As you might know already, the Lookup, Vlookup and Hlookup functions are also very useful, but if the source data is not static - the heys in the heystack move around and do not stay always on the same rows or columns, your best option is to use index match-combo.


Index function returns a value of a cell in the intersection of a particular row and column in a given data range. 
Syntax: INDEX(array;row number;column number)

Match function returns the relative position of an item in an array that matches specified value in a specified order.

Syntax: MATCH(lookup value;lookup array;match type)

Index match with one criteria

 

Below we have a data table containing some names, departments and test scores and we want to find test scores for some people.


Column A Column B Column C
Row 1 Name Department Test score
Row 2 John Sales 57
Row 3 Robert Marketing 97
Row 4 Harry Sales 52
Row 5 Mary Manufacturing 94
Row 6 Jill Marketing 53
Row 7 Glenn Manufacturing 72
Row 8 Mary Science 97
Row 9 Robert Science 53
Row 10 Mary Sales 97


Syntax if we type the search value "Jill" in the cell A13:
=INDEX(C2:C10;MATCH(A13;A2:A10;0)) returns 53.
Explanation: 
 
  • C2:C10: this is the array from which we are looking for the matching value a.k.a. the test score.
  • A13 is the lookup value.
  • A2:A10 is the lookup array from where we expect to find the value typed in A13.
  • The last 0 means we want to find the exact match. 

 Index match with two criteria

Below we have a data table containing some names, departments, test scores and pay.


Column A Column B Column C Column D
Row 1 Name Department Test score Pay
Row 2 John Sales 57 7901
Row 3 Robert Marketing 97 8844
Row 4 Harry Sales 52 6205
Row 5 Mary Manufacturing 94 8331
Row 6 Jill Marketing 53 7916
Row 7 Glenn Manufacturing 72 4902
Row 8 Mary Science 97 6717
Row 9 Robert Science 53 3616
Row 10 Mary Sales 78 3245
 

Find pay with two criteria: search for Mary that has a test score of 97. Syntax if we type the search value "Mary" in cell A13 and "97" in cell B13:
=INDEX(D2:D10;MATCH(1;(A13=A2:A10)*(B13=C2:C10);0)) returns 6717.
This is an array function, so you need to press CTRL + SHIFT + ENTER to enter the function. You will then see Excel adding curly brackets {} around the function.

Explanation: 
  • D2:D10: this is the array from which we are looking for the matching value a.k.a. the test score.
  • Lookup value is 1. Wait, why? What?
See the lookup array part: (A13=A2:A10)*(B13=C2:C10), you can see what this does below:


Name Department Test score Pay value for name value for score  multiplied
John Sales 57 7901 0 0 0x0=0
Robert Marketing 97 8844 0 1 0x1=0
Harry Sales 52 6205 0 0 0x0=0
Mary Manufacturing 94 8331 1 0 1x0=0
Jill Marketing 53 7916 0 0 0x0=0
Glenn Manufacturing 72 4902 0 0 0x0=0
Mary Science 97 6717 1 1 1x1=1
Robert Science 53 3616 0 0 0x0=0
Mary Sales 78 3245 1 0 1x0=0


The formula returns 0 if the value in cell A13 ("Mary") is not found and 1 if it is found. Same applies to the value in cell B13 (test score "97"). The formula multiplies each value and returns 1 only when both criterias are found 1 X 1 = 1. This is why the lookup value is 1. Pretty simple, isn't it!

As you can see, the order of the data does not matter, because the match function gives us the right row (where "Mary" or "Jill" is) every time (or column if the data were transposed) .

1 comment:

  1. AWS Training in Bangalore - Live Online & Classroom
    myTectra Amazon Web Services (AWS) certification training helps you to gain real time hands on experience on AWS. myTectra offers AWS training in Bangalore using classroom and AWS Online Training globally. AWS Training at myTectra delivered by the experienced professional who has atleast 4 years of relavent AWS experince and overall 8-15 years of IT experience. myTectra Offers AWS Training since 2013 and retained the positions of Top AWS Training Company in Bangalore and India.


    IOT Training in Bangalore - Live Online & Classroom
    IOT Training course observes iot as the platform for networking of different devices on the internet and their inter related communication. Reading data through the sensors and processing it with applications sitting in the cloud and thereafter passing the processed data to generate different kind of output is the motive of the complete curricula. Students are made to understand the type of input devices and communications among the devices in a wireless media.

    ReplyDelete