Index Match in Excel

Index Match can be used as an alternative to VLookup. Index match comprises of two functions Index and Match.

Now let us take the same example where we want to grade the students assignments and update the grade corresponding to the score.

  

We have a reference table which contains Score range with Grade.

 

Suppose the marking criteria is as below:

 100  >  Score >  90   :  A+

 90  >  Score >  85   :  A

 85  >  Score >  80   :  B

 80  >  Score >  75   :  C

 75  > Score              : Fail

A quick way to define range name is as shown in screen shot where you can directly write the name in the highlighted area.

Once you have defined names for the ranges, lets proceed to use Index Match. Since Index Match is made up of two independent functions Index and Match, lets first use Index function to input the result range. Here Grade is the result column since we want to see the grades which correspond to the score.

Now, lets add Match function to match our input value , in VLookup its the Lookup value. Here it would be B2.

 Match function also needs the lookup array in which it will match the input value. Since we are matching scores to get grades, we will add Score array in the lookup array.

Now, the last parameter is the condition. Here since our scores are in descending order, we will use -1 i.e. greater than as the match type.

Once all the parameters are included, press enter and check result. Drag it vertically to see the results for all the values of score.

You can also check similar article at : http://spreadsheeto.com/index-match/

References

WONG, M. (2010, January 16). How to Use INDEX MATCH. Retrieved from RANDOM WoK: http://www.randomwok.com/excel/how-to-use-index-match/