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.

Users are saying about us...

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>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube