Xverweis mit mehreren Kriterien mit VBA

Forumthread: Xverweis mit mehreren Kriterien mit VBA

23.06.2022 14:41:21

Martin

Hallo zusammen,
ich versuche aktuell mit VBA die XVERWEIS Funktion zu nutzen. Das klappt auch alles wunderbar, solange ich nur ein Suchkriterium habe. Ich habe aber zwei Kriterien.
Mit der Xverweis Funktion in Excel bekomme ich das richtige Ergebnis, in VBA leider den Fehler "Typen unverträglich".
Die Formel in VBA funktioniert wenn ich nur 1 Suchkriterium und 1 Suchbereich habe. Das Problem liegt anscheinend an der Verknüpfung der beiden Kriterien / Bereiche mit dem "&" Zeichen.
Hat jemand eine Idee wie ich die Formel in VBA hinbekomme?
Vielen Dank!

Sub versuch()
Dim suchkriterium1, suchkriterium2, suchmatrix1 As Range, suchmatrix2 As Range, rueckgabematrix As Range, ergebnis, ersatztext, zeile
Set suchmatrix1 = Tabelle2.Columns("AY")
Set suchmatrix2 = Tabelle2.Columns("AZ")
Set rueckgabematrix = Tabelle2.Columns("BA")
zeile = 2
suchkriterium1 = Tabelle1.Range("N" & zeile).Value
suchkriterium2 = Tabelle1.Range("S" & zeile).Value
ergebnis = Application.WorksheetFunction.XLookup(suchkriterium1 & suchkriterium2, suchmatrix1 & suchkriterium2, rueckgabematrix, ersatztext)
MsgBox ergebnis
End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Xverweis mit mehreren Kriterien mit VBA

23.06.2022 15:30:13

Daniel

Hi
das VBA kann zwar Excel-Funktionen ausführen, beherrscht aber keine Matrixformel-Funktionalität.
dh wärend die Excelfunktion XVergleich in einer Zelle bei suchmatrix1 & suchkriterium2 in einer Schleife über die Zekkbereiche läuft und versucht, jede Einzelzelle aus Suchmatrix1 mit der parallelen Einzelzelle aus Suchmatrix2 zu verknüpfen und dann dieses Matrix in die Formel übernimmt, versucht VBA hier stur die beiden Zellbereiche wie Einzelwerte mti & zu verknüpfen und das funktioniert nicht.
entweder musst du die Schleife dann entsprechend selber programmieren, oder du erzeugst die Excelformel als Text in englischer A1-Schreibweise für eine Formel in einer Zelle und berechnest diesen Text mit der funktion EVALUATE.
Gruß Daniel

AW: Xverweis mit mehreren Kriterien mit VBA

23.06.2022 15:43:45

ChrisL

Hi
Ganz schöner Handstand, wenn man auch einfach eine Formel verwenden könnte:


Sub versuch()
Dim suchkriterium1 As String, suchkriterium2 As String
Dim suchmatrix1 As String, suchmatrix2 As String, rueckgabematrix As String
Dim ergebnis As Variant
Dim ersatztext As String, zeile As Long
suchmatrix1 = Tabelle2.Columns("AY").Address(0, 0, , 1)
suchmatrix2 = Tabelle2.Columns("AZ").Address(0, 0, , 1)
rueckgabematrix = Tabelle2.Columns("BA").Address(0, 0, , 1)
zeile = 2
suchkriterium1 = Tabelle1.Range("N" & zeile).Address(0, 0, , 1)
suchkriterium2 = Tabelle1.Range("S" & zeile).Address(0, 0, , 1)
ersatztext = "ein Fehler"
'Formel: =XVERWEIS(1;(AY:AY=Tabelle1!N2)*(AZ:AZ=Tabelle1!S2);BA:BA;"ein Fehler")
ergebnis = Evaluate("XLOOKUP(1,(" & suchmatrix1 & "=" & suchkriterium1 & ")*(" & suchmatrix2 & "=" & suchkriterium2 & ")," & rueckgabematrix & ",""" & ersatztext & """)")
MsgBox ergebnis
End Sub

Übrigens... sollte eine eindeutige Zahl (keine Mehrfachtreffer) gesucht werden, könnte man einfach SUMMEWENNS verwenden.
cu
Chris

AW: Xverweis mit mehreren Kriterien mit VBA

24.06.2022 10:12:21

Martin

Servus Chris,
vielen Dank das klappt.
Ja ich weiß, dass das umständlich ist. Nur leider habe ich nicht eine Formel, sondern hunderte und über mehrere Spalten verteilt. Wenn ich die Funktionalität mit Formeln realisiere (das hatte ich bisher auch), kann ich mit der Datei nicht mehr arbeiten weil ständig was berechnet wird. Filtern wird dann unmöglich.
Ja ich könnte die automatische Berechnung ausschalten. Aber das erklär mal allen Kollegen drum herum, die die Datei auch noch nutzen.
So kann ich die Datei einmal aktualisieren und danach arbeitet jeder mit der Datei.
Nochmal vielen Dank und ein schönes Wochenende!
Gruß
Martin

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Vorbereitung: Stelle sicher, dass Du Excel VBA verwendest (Excel 2019 oder höher wird empfohlen). Öffne den VBA-Editor (ALT + F11).
VBA-Modul erstellen: Füge ein neues Modul hinzu, indem Du mit der rechten Maustaste auf "VBAProject" klickst und "Einfügen" > "Modul" wählst.

Code einfügen: Verwende den folgenden Code als Grundlage für Deinen Xverweis mit mehreren Kriterien:

Sub versuch()
    Dim suchkriterium1 As String, suchkriterium2 As String
    Dim suchmatrix1 As String, suchmatrix2 As String, rueckgabematrix As String
    Dim ergebnis As Variant
    Dim ersatztext As String, zeile As Long

    suchmatrix1 = Tabelle2.Columns("AY").Address(0, 0, , 1)
    suchmatrix2 = Tabelle2.Columns("AZ").Address(0, 0, , 1)
    rueckgabematrix = Tabelle2.Columns("BA").Address(0, 0, , 1)

    zeile = 2
    suchkriterium1 = Tabelle1.Range("N" & zeile).Address(0, 0, , 1)
    suchkriterium2 = Tabelle1.Range("S" & zeile).Address(0, 0, , 1)
    ersatztext = "ein Fehler"

    ' Formel: =XVERWEIS(1;(AY:AY=Tabelle1!N2)*(AZ:AZ=Tabelle1!S2);BA:BA;"ein Fehler")
    ergebnis = Evaluate("XLOOKUP(1,(" & suchmatrix1 & "=" & suchkriterium1 & ")*(" & suchmatrix2 & "=" & suchkriterium2 & ")," & rueckgabematrix & ",""" & ersatztext & """)")

    MsgBox ergebnis
End Sub

Anpassen: Ändere die Spaltenbezüge nach Bedarf, um sicherzustellen, dass sie mit Deinen Daten übereinstimmen.
Ausführen: Führe das Makro aus (F5), um den Xverweis mit mehreren Kriterien in VBA zu testen.

Häufige Fehler und Lösungen

Typen unverträglich: Dieser Fehler tritt häufig auf, wenn Du versuchst, Bereiche mit & zu verknüpfen. Stelle sicher, dass Du die Adressierung korrekt verwendest und die Bereiche in der richtigen Form übergibst.
Xverweis funktioniert nicht: Überprüfe, ob die Excel-Version die XLOOKUP-Funktion unterstützt. Diese ist nur in neueren Versionen verfügbar.
Fehlermeldung bei der Ausführung: Achte darauf, dass Du die richtigen Variablenarten definiert hast. Beispielsweise sollten suchkriterium1 und suchkriterium2 als String deklariert werden.

Alternative Methoden

Wenn VBA nicht die gewünschte Lösung bietet, kannst Du die SUMMEWENNS-Funktion verwenden, um mehrere Bedingungen in einer Formel zu berücksichtigen:

=SUMMEWENNS(BA:BA; AY:AY; N2; AZ:AZ; S2)

Diese Methode ist einfacher, kann jedoch in großen Datenmengen langsamer sein.

Praktische Beispiele

Hier ist ein Beispiel für die Verwendung des Xverweis mit zwei Bedingungen:

Angenommen, Du hast in Tabelle2 die Spalten AY und AZ als Suchmatrix und BA als Rückgabematrix.
Die Kriterien sind in Tabelle1 in den Zellen N2 und S2 definiert.
Das Ergebnis wird in einer MessageBox ausgegeben, wenn die Kriterien erfüllt sind.

Tipps für Profis

Verwende WorksheetFunction: Du kannst auch Application.WorksheetFunction.XLookup verwenden, um die Xverweis-Funktion in VBA auszuführen.
Fehlerbehandlung einfügen: Füge eine Fehlerbehandlung in Deinen Code ein, um sicherzustellen, dass das Makro robust ist und nicht ohne Rückmeldung abstürzt.
Optimierung: Wenn Du mit vielen Daten arbeitest, überlege, die Berechnung auf manuell zu setzen, um die Performance zu verbessern.

FAQ: Häufige Fragen

1. Wie kann ich den Xverweis mit mehreren Kriterien in Excel verwenden?
Du kannst die Xverweis-Funktion in einer Zelle mit einer Formel verwenden, die mehrere Kriterien verknüpft, z. B. =XVERWEIS(1; (AY:AY=N2)*(AZ:AZ=S2); BA:BA; "kein Treffer").

2. Welche Excel-Version benötige ich für den Xverweis?
Der Xverweis ist in Excel 2019 und Microsoft 365 verfügbar. Ältere Versionen unterstützen diese Funktion nicht.

Beliebteste Forumthreads (12 Monate)

Xverweis mit mehreren Kriterien mit VBA

Forumthread: Xverweis mit mehreren Kriterien mit VBA

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Xverweis mit mehreren Kriterien in VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Xverweis mit mehreren Kriterien mit VBA

Forumthread: Xverweis mit mehreren Kriterien mit VBA

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Xverweis mit mehreren Kriterien in VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread