index/match array vba

Sub test1()
    Dim testing As Integer
    Dim region As Range
    Dim region2 As Range
    Dim region3 As Range
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    i = 0
    j = 0
    k = 0
    
    For j = 0 To 11
     For i = 0 To 11
         For k = 0 To 5
         
    Worksheets("Lista_lunara_repere").Activate
    
    Set region = ActiveSheet.Range(Cells(11, i * 6 + 5), Cells(50, i * 6 + 9))
    Set region2 = ActiveSheet.Range(Cells(11, i * 6 + 5), Cells(55, i * 6 + 5))
    Set region3 = ActiveSheet.Range(Cells(11, i * 6 + 9), Cells(55, i * 6 + 9))
       
    Worksheets("TestMacro").Activate
    testing = Application.index(region, Application.Match(Cells(j * 16 + 3, 3) & Cells(j * 16 + 3, k * 1 + 4), region2 & region3, 0), 4)
    
   Cells(j * 16 + 4, 4).Value = testing
    
         Next k
     Next i
  Next j
End Sub

Hello,
I am trying to replace a lot of index forumlas that have started to slow my workbook down quite a lot with a macro that i hope will help me reduce "operating" time.
This setup works up untilli added another condition and another range for the match part wich i guess turned the "formula" into an array. Now i get a run-time error '13': type mysmatch. Help preety plz.

It is not possible to join arrays using & in VBA, nor to use them in Match like this. You may either loop through ranges or create formula string to use with Evaluate method.

It will be like this:

testing = Application.Evaluate("INDEX(Lista_lunara_repere!" & region.Address & ",MATCH(" & Cells(j * 16 + 3, 3).Address & "&" & Cells(j * 16 + 3, k * 1 + 4).Address & "," & region2.Address & "&" & region3.Address & ", 0), 4)")

Please explain what it is you want to see as output and why you may not simply use formulas.

You have said:

with the current macro i only get the first item that it finds. If i want the second one i would have to add another criteria and another range in the match subthingy

This is what I seek to clarify - I do not know what you want.

I want the quantity of the code "222" in relation with each suplier. So from January i need the quantity of 222 from "first" suplier and the quantity of 222 from "third" suplier.
So in Sheet "Results" C6 i want the quantity of 222 from the first suplier D6 quantity of 222 from second if there is any and so on.

Only for code 222?

I think your formulas will be much faster if you will create key columns instead of trying to join them inside array formulas.

I understand your criteria but if you will create one column in the source tables which concatenates the criteria fields, you will not require array formulas and they will perform better.