Falls das mit SVERWEIS zu langsam ist, dann per VBA so:
Sub SpieleLaden()
Dim arrIn(), arrAus(), ArrHome(), ArrAway(), arrAusHome(), arrAusAway(), i&, j&
Tabelle1.Range("H11:BE" & Tabelle1.Cells(Rows.Count, 8).End(xlUp).Row).ClearContents
arrIn = Tabelle5.Range("A2:AO" & Tabelle5.Cells(Rows.Count, 1).End(xlUp).Row).Value
arrIn = Application.Index(arrIn, Evaluate("row(1:" & UBound(arrIn, 1) & ")"), Array(1, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41))
ArrHome = Tabelle1.Range("F11:F" & Tabelle1.Cells(Rows.Count, 6).End(xlUp).Row).Value
ArrAway = Tabelle1.Range("G11:G" & Tabelle1.Cells(Rows.Count, 7).End(xlUp).Row).Value
ReDim arrAus(1 To UBound(ArrHome), 1 To 50)
For i = 1 To UBound(ArrHome)
For j = 1 To UBound(arrIn)
If ArrHome(i, 1) = arrIn(j, 1) Then
ReDim arrAusHome(1 To UBound(ArrHome), 1 To 26)
arrAusHome = Application.Index(arrIn, Evaluate("row(" & j & ":" & j & ")"), Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26))
End If
Next j
For j = 1 To UBound(arrIn)
If ArrAway(i, 1) = arrIn(j, 1) Then
ReDim arrAusAway(1 To UBound(ArrAway), 1 To 26)
arrAusAway = Application.Index(arrIn, Evaluate("row(" & j & ":" & j & ")"), Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26))
End If
Next j
For j = 1 To 25
arrAus(i, j) = arrAusHome(j + 1)
arrAus(i, j + 25) = arrAusAway(j + 1)
Next j
Next i
Tabelle1.Range("H11").Resize(UBound(arrAus, 1), UBound(arrAus, 2)) = arrAus
End Sub
Zu beachten ist, da ich keine Lust hatte noch ein paar Schleifen zwischen den Übergaben in den Arrays zu schreiben, wird diese Prozedur nur funktionieren, wenn die Spalten der Tabellenblätter im A B C Format also kein Haken im Z1S1 Bezugsart eingestellt sind.
https://www.herber.de/bbs/user/158985.xlsb
Gruß Uwe