I am trying to assign a Matrix formula with Index and Match functions to a variable in VBA. I know it is possible to assign matrix formulas to cells, but that is not what I want per se. I want it in a variable in VBA where I use it, not in a cell.
The function below works like I expect it to:
But I want multiple search criteria so I tried (for starters with 1 criterium):
This fails on a type mismatch. Apparently it can't handle: "CLA" = Sheets("Parameters - DUMMY_ALL_BLOCKS").Range("A:A")
But this definately works in a regular excel cell. Does anybody know how to solve this?