VBA VLOOKUP with multiple conditions

[syzer]

Hello!
I have a workbook that lists products we sell, for who, when and how many we have sold of each product. I would like to add macro into workbook, which based on multiple criterias can figurate the exact matching and return the requested relevant information, what I need.

To simplify my question, here is one example how it can be solve with excel functional method the request.

I would like to use this functional to defining the variable in the macro, but after running of macro always there is Type mismatch error occurs.

qty = Application.WorksheetFunction.Index(Range("D2:D11"), Application.WorksheetFunction.Match(1, (Range("G1") = Range("A2:A11")) * (Range("G2") = Range("B2:B11")) * (Range("G3") = Range("C2:C11")), 0))

Does somebody have idea how I can search relevant cell data based at least on 3 criterias do get the variable?

Thank you!

 

[Alex Blakenburg]

The simplest workaround is going to be something like this:

    qty = Evaluate("INDEX(D2:D11,MATCH(1,(A2:A11=G1)*(B2:B11=G2)*(C2:C11=G3),0),0)")

 

[syzer]

The simplest workaround is going to be something like this:

    qty = Evaluate("INDEX(D2:D11,MATCH(1,(A2:A11=G1)*(B2:B11=G2)*(C2:C11=G3),0),0)")

Thank you, it's very simple and fast solution in my case. The result evaluating is working perfectly!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.