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.

img1

 

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

img2

 

  • 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)

img3

 

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

img4

 

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

img5

  • Click on Paste List

 

 

 

img7

 

We need to follow the below steps to launch VB editor

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

 

img8

 

 

  • Click on Insert then Module

 

img9

 

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

img9

 

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

 

img10

 

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

 

img11

 

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



3 thoughts on “How To Use Index Match For 2 Criterias Using VBA

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>