Multiple Criteria Index Match does not works

Question

In excel vba, i am trying to update a cell value based on vlookup on multiple columns. As per the suggestion online i tried using index/match function of vba but somehow it does not works.

 ActiveCell.Offset(0, 6) = Application.WorksheetFunction.Index(ExWs.Range("I:I"), _
 Application.WorksheetFunction.Match(inv, ExWs.Range("B:B"), 0), _
 Application.WorksheetFunction.Match("Planning Readiness Tollgate", ExWs.Range("H:H"), 0) _
            , 0)

If in the above code I keep only one conditions things are working fine. Please help !! Also I am not allowed to update anything on the lookup sheet, it's read only.

Thanks in Advance.

Regards, Bhavesh Jain

The criteria for INDEX is INDEX(Range,RelativeRow,RelativeColumn) you are trying to pass two row to INDEX when the second is being interpreted as a relative column number. The best thing to do here is to put the used range in all three into variant arrays and iterate the array until the two fulfill the criteria and then return the correct corresponding value from the other array. — Scott Craner, May 21 '21 at 14:54
But if you want to see how to do it with a formula see: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another — Scott Craner, May 21 '21 at 14:55

score 0 · Accepted Answer · answered May 21 '21 at 21:15

An alternative approach would be to use the Evaluate method. Assuming that Column B contains numerical values, try...

Dim LastRow As Long

With ExWs
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    ActiveCell.Offset(0, 6).Value = Evaluate("INDEX('" & .Name & "'!I2:I" & LastRow & ",MATCH(1,IF('" & .Name & "'!B2:B" & LastRow & "=" & inv & ",IF('" & .Name & "'!H2:H" & LastRow & "=""Planning Readiness Tollgate"",1)),0))")
End With

However, if Column B contains text values, you'll need to enclose the criteria within quotes. If so, try the following instead...

Dim LastRow As Long

With ExWs
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    ActiveCell.Offset(0, 6).Value = Evaluate("INDEX('" & .Name & "'!I2:I" & LastRow & ",MATCH(1,IF('" & .Name & "'!B2:B" & LastRow & "=""" & inv & """,IF('" & .Name & "'!H2:H" & LastRow & "=""Planning Readiness Tollgate"",1)),0))")
End With

Note that the Evaluate method has a 255 character limit.

Multiple Criteria Index Match does not works

1 Answers1