How To Use Index Match For 2 Criterias Using VBA

In this article, you will learn how to use INDEX & MATCH function in VBA to match 2 criteria’s in excel using VBA code.

To get the output we will use combination of OFFSET & COUNTA functions to create Name Manager list.


Let us understand with an example:

  • We have Student Report card with Student Name, Subject & Mark.



  • We want to find out the marks for the Student Name & Subject entered in cells F2 & G2



  • To calculate Marks using VBA code, we have used OFFSET function to create Named ranges for each category.
  • Press CTRL + F3 to open Name Manager Window (the lists are already created)



  • Using Paste Names feature in Formulas tab; we will get all the Defined Name range list in cells



  • Using Paste Names feature in Formulas tab; we will get all the Defined Name range list in cells


  • Click on Paste List






We need to follow the below steps to launch VB editor

  • Click on Developer tab
  • From Code group select Visual Basic





  • Click on Insert then Module




This will create new module.

Enter the following code in the Module


Sub IndexMatch()

myName = [F2]

mySubject = [G2]

mark = Application.WorksheetFunction.Index([StMark], _

Application.WorksheetFunction.Match(myName, ([StName]), 0) + _

Application.WorksheetFunction.Match(mySubject, [StSubject], 0) - 1)

[H2] = mark

End Sub



  • Press ALT + F8 shortcut key for opening Macro window & then select the macro.




  • Alternatively you can press F5 to run the code in VBA screen.
  • After executing the macro we will get the output in cell H2




This is how we can get results using Index Match worksheet function in VBA.


  1. What if the name was a column A2:A20 and the subject was row B1:H1 and the MARK are the area B2:H20
    can I have an excel sheet copy of that please ?

  2. Rajendra p soni

    Are we adding 2 rows given by 2 match functions - 1,
    here each match function gives independent Row number,
    resultant row number may change as per data position? may not give same result

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.