Die XLOOKUP-Funktion ist exklusiv für das Insider-Programm von Office 365. Die LOOKUP-Funktion hat viele Funktionen, die viele der Schwächen der SVERWEIS- und HVERWEIS-Funktion überwinden, aber leider steht sie uns derzeit nicht zur Verfügung. Aber keine Sorge, wir können eine XVERWEIS-Funktion erstellen, die genauso funktioniert wie die kommende XVERWEIS-Funktion MS Excel. Wir werden nach und nach Funktionalitäten hinzufügen.
VBA-Code der XLOOKUP-Funktion
Die folgende UDF-Suchfunktion löst viele Probleme. Kopieren Sie es oder laden Sie das untenstehende XL-Add-In herunter.
Funktion XLOOKUP(lk As Variant, lCol As Range, rCol As Range) XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0)) End Function
Erläuterung:
Der obige Code ist nur ein grundlegender INDEX-MATCH, der in VBA verwendet wird. Dies vereinfacht viele Dinge, mit denen ein neuer Benutzer konfrontiert ist. If löst die Komplexität der INDEX-MATCH-Funktion auf und verwendet nur drei Argumente. Sie können es in Ihre Excel-Datei kopieren oder die .xlam-Datei unten herunterladen und als Excel-Add-In installieren. Wenn Sie nicht wissen, wie Sie ein Add-In erstellen und verwenden, klicken Sie hier, es wird Ihnen helfen.
XLOOKUP-Add-In
Mal sehen, wie es auf dem Excel-Arbeitsblatt funktioniert.
Syntax von XLOOKUP
| =XLOOKUP(lookup_value, lookup_array, result_array) |
Lookup-Wert: Dies ist der Wert, den Sie in der suchen möchten lookup_array.
lookup_array: Es handelt sich um einen eindimensionalen Bereich, in dem Sie suchen möchten Lookup-Wert.
result_array: Es ist auch ein eindimensionaler Bereich. Dies ist der Bereich, aus dem Sie den Wert abrufen möchten.
Sehen wir uns diese XLOOKUP-Funktion in Aktion an.
XLOOKUP-Beispiele:
Hier habe ich eine Datentabelle in Excel. Lassen Sie uns mithilfe dieser Datentabelle einige Funktionen untersuchen.
Funktionalität 1. Genau Lookup auf der linken und rechten Seite des Lookup-Werts.
Wie wir wissen, kann die Excel SVERWEIS-Funktion keine Werte links vom Nachschlagewert abrufen. Dazu müssen Sie die komplexe INDEX-MATCH-Kombination verwenden. Aber nicht mehr.
Angenommen, wir müssen alle Informationen abrufen, die in der Tabelle einiger Rollennummern verfügbar sind. In diesem Fall müssen Sie auch die Region abrufen, die sich links in der Spalte mit den Rollennummern befindet.
Schreiben Sie diese Formel I2:
| =XVERWEIS(H2,$B$2:$B$14,$A$2:$A$14) |
Wir erhalten das Ergebnis Nord für die Rollennummer 112. Kopieren oder ziehen Sie die Formel in die unteren Zellen, um sie mit den entsprechenden Regionen zu füllen.
Wie funktioniert es?
Der Mechanismus ist einfach. Diese Funktion sucht nach Lookup-Wert in lookup_array und gibt den Index einer ersten genauen Übereinstimmung zurück. Verwendet dann diesen Index, um den Wert von abzurufen result_array. Diese Funktion funktioniert perfekt mit benannten Bereichen.
Verwenden Sie diese Formel auf ähnliche Weise, um den Wert aus jeder Spalte abzurufen.
Funktionalität 2. Genau Horizontal Lookup oberhalb und unterhalb des Lookup-Werts.
Der XLOOKUP funktioniert auch als exakte HLOOKUP-Funktion. Die HVERWEIS-Funktion hat dieselbe Einschränkung wie die SVERWEIS-Funktion. Es kann keinen Wert über dem Nachschlagewert abrufen. Aber XLOOKUP funktioniert nicht nur als HLOOKUP, es überwindet auch diese Schwäche. Mal sehen wie.
Hypothetisch, wenn Sie zwei Datensätze vergleichen möchten. Der Lookup-Datensatz, den Sie bereits haben. Der Datensatz, mit dem Sie vergleichen möchten, befindet sich oberhalb des lookup_range. Verwenden Sie in diesem Fall diese Formel.
| =XVERWEIS(H7,$A$9:$E$9,$A$2:$E$2) |
Ziehen Sie die Formel nach unten, und Sie haben den gesamten Datensatz der Vergleichszeile.
Funktionalität 3. Keine Notwendigkeit für Spaltennummer und exakte Standardübereinstimmung.
Wenn Sie die SVERWEIS-Funktion verwenden, müssen Sie die Spaltennummer angeben, aus der Sie die Werte holen möchten. Dazu müssen Sie die Spalten zählen oder einige Tricks anwenden, andere Funktionen in Anspruch nehmen. Mit diesem UDF XLOOKUP brauchen Sie das nicht zu tun.
Wenn Sie SVERWEIS verwenden, um nur einen Wert aus einer Spalte abzurufen oder zu überprüfen, ob ein Wert in der Spalte vorhanden ist, ist dies meiner Meinung nach die beste Lösung.
Funktionalität 4. Ersetzt INDEX-MATCH, SVERWEIS, HVERWEIS-Funktion
Für einfache Aufgaben ersetzt unsere XLOOKUP-Funktion die oben genannten Funktionen.
Einschränkungen von XLOOKUP:
Wenn es um komplexe Formeln geht, wie SVERWEIS mit Dynamic Col Index, bei denen SVERWEIS die Nachschlagespalte mit Überschriften identifiziert, schlägt dieser XVERWEIS fehl.
Eine weitere Einschränkung besteht darin, dass diese Funktion nutzlos ist, wenn Sie mehrere zufällige Spalten oder Zeilen aus der Tabelle nachschlagen müssen, da Sie diese Formel immer wieder schreiben müssen. Dies kann durch die Verwendung benannter Bereiche umgangen werden.
Im Moment haben wir die ungefähre Funktionalität nicht hinzugefügt, daher können Sie natürlich keine ungefähre Übereinstimmung erhalten. Wir werden das auch bald hinzufügen.
Wenn die XLOOKUP-Funktion den Nachschlagewert nicht findet, gibt sie den Fehler #VALUE zurück, nicht #N/A.
Also, Leute, so verwenden Sie XLOOKUP, um Werte in Excel-Tabellen abzurufen, zu suchen und zu validieren. Sie können diese benutzerdefinierte Funktion für eine problemlose Suche links oder oben vom Suchwert verwenden. Wenn Sie immer noch Zweifel oder spezielle Anforderungen in Bezug auf diese Funktion oder EXCEL 2010/2013/2016/2019/365 oder VBA-bezogene Abfrage haben, stellen Sie diese im Kommentarbereich unten. Sie erhalten sicher eine Antwort.
VBA-Funktion erstellen, um Array zurückzugeben | Um ein Array von einer benutzerdefinierten Funktion zurückzugeben, müssen wir es deklarieren, wenn wir die UDF benennen.
Arrays in Excel Formul|Erfahren Sie, was Arrays in Excel sind.
So erstellen Sie eine benutzerdefinierte Funktion über VBA | Erfahren Sie, wie Sie benutzerdefinierte Funktionen in Excel erstellen
Verwenden einer benutzerdefinierten Funktion (UDF) aus einer anderen Arbeitsmappe mit VBA in Microsoft Excel | Verwenden Sie die benutzerdefinierte Funktion in einer anderen Arbeitsmappe von Excel
Fehlerwerte von benutzerdefinierten Funktionen mit VBA in Microsoft Excel zurückgeben | Erfahren Sie, wie Sie Fehlerwerte von einer benutzerdefinierten Funktion zurückgeben können
Populäre Artikel:
Excel-Tabelle in mehrere Dateien basierend auf Spalten mit VBA aufteilen | Dieses VBA-Code-Split-Excel-Blatt basiert auf eindeutigen Werten in einer angegebenen Spalte. Laden Sie die Arbeitsdatei herunter.
Deaktivieren von Warnmeldungen mit VBA in Microsoft Excel 2016 | Um Warnmeldungen zu deaktivieren, die den laufenden VBA-Code unterbrechen, verwenden wir die Application-Klasse.
Hinzufügen und Speichern einer neuen Arbeitsmappe mit VBA in Microsoft Excel 2016 | Um Arbeitsmappen mit VBA hinzuzufügen und zu speichern, verwenden wir die Klasse Workbooks. Workbooks.Add fügt jedoch problemlos neue Arbeitsmappen hinzu…
