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.

Previous

Next

Comments

Manoj Patel
April 10, 2020 at 1:45 pm

Have this excel file, please share

Reply ↓
Darrin Stephens
March 19, 2020 at 10:23 pm

The only one that worked. Thanks!

Reply ↓
- Manish Singh
  March 25, 2020 at 3:17 pm
  
  Thanks Darrin
  
  Reply ↓
Ramez
December 19, 2019 at 4:43 pm

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 ?

Reply ↓
Rajendra p soni
May 13, 2019 at 12:04 pm

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

Reply ↓
Mahdi
December 27, 2018 at 12:52 pm

I love learn vba

Reply ↓
Yuhanna Sherriff
December 8, 2016 at 8:29 am

I would also like a copy of the sheet to review. Thank you very much

Reply ↓
Third Ricohermoso
April 8, 2016 at 2:05 am

Can I ask for a copy of the excel file for this?

Reply ↓
- Dane
  June 20, 2016 at 6:12 am
  
  I would also like a copy of the excel sheet for review please. Thx. Dane
  
  Reply ↓

Leave a Reply to Third Ricohermoso Cancel reply

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.