CODEWITHSUNDEEP
vbaexcel
Sanfly
Sanfly

Reputation: 1029

Index Match 2 criteria in VBA

I have a macro I'm trying to create to map a bunch of old values to new values. At one point I need to do a match on 2 columns and get the value from a third column. In normal excel I would run the formula below, but as it is a Formula Array (have to CTRL+SHIFT+ENTER to run) I'm not quite sure how to get this to work in VBA

=INDEX($D:$D,MATCH(1,(E2=$A:$A)*(F2=$B:$B),0))

Any help appreciated

Upvotes: 1

Views: 2719

Answers (2)

user4039065
user4039065

Reputation:

There are essentially three ways to evaluate an array formula to a result within VBA.

The first is 'square bracket' evaluation. This processes a formula just as if it is on the worksheet.

Dim result As Variant
result = [INDEX('Sheet1'!D:D,MATCH(1,(E2='Sheet1'!A:A)*(F2='Sheet1'!B:B),0))]
Debug.Print result

Care must be given to explicitly show worksheet parentage. In the above, the full columns have been given a parent worksheet but the individual cells have not. The individual cells will default to the ActiveSheet property which may or may not even be in the same workbook. Define all cell references explicitly!

The second method is with Application.Evaluate method. This allows more freedom with the formulas using string construction. 

Dim result As Variant, wsn As String
wsn = Worksheets("Sheet1").Name
result = Application.Evaluate("INDEX('" & wsn & "'!D:D, " & _
             "MATCH(1, (E2='" & wsn & "'!A:A)*(F2='" & wsn & "'!B:B),0))")
Debug.Print result

Again, only the full column references have been given parent worksheet references. This should be fixed before run-time use. This method is better for array formulas because you can use string construction to cut the full column references down to the rows in the Worksheet.UsedRange property.

As mentioned, the third method involves writing the formula to a worksheet cell using the Range.FormulaArray property and retrieving the answer from the cell.

Upvotes: 0

Sebastian B
Sebastian B

Reputation: 451

Have you tried? 

Cell.FormulaArray()

It should work with normal notation, although it is intended for R1C1

Upvotes: 2

Related Questions