Die XLOOKUP-Funktion ist exklusiv für das Insider-Programm von Office 365 verfügbar.

Die LOOKUP-Funktion verfügt über viele Funktionen, die viele der Schwächen der VLOOKUP- und HLOOKUP-Funktion überwinden. Leider steht sie uns derzeit nicht zur Verfügung. Aber keine Sorge, wir können eine XLOOKUP-Funktion erstellen, die genauso funktioniert wie die kommende XLOOKUP-Funktion MS Excel. Wir werden nacheinander Funktionen hinzufügen.

VBA-Code der XLOOKUP-Funktion Die folgende UDF-Suchfunktion löst viele Probleme. Kopieren Sie es oder laden Sie das unten stehende XL-Add-In herunter.

Function 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 einfaches INDEX-MATCH, das in VBA verwendet wird. Dies vereinfacht viele Dinge, mit denen ein neuer Benutzer konfrontiert ist. 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 unten stehende .xlam-Datei herunterladen und als Add-In-Excel installieren. Wenn Sie nicht wissen, wie Sie ein Add-In erstellen und verwenden, klicken Sie auf `link: / excel-macros-and-vba-add-in-vb [klicken Sie hier, es wird Ihnen helfen].

image 48XLOOKUP Add-In]

Mal sehen, wie es auf Excel-Arbeitsblatt funktioniert.

Syntax von XLOOKUP

=XLOOKUP(lookup_value, lookup_array, result_array)

lookup_value: Dies ist der Wert, den Sie im

suchen möchten === lookup_array.

lookup_array: Dies ist ein eindimensionaler Bereich, in dem Sie den lookup_value durchsuchen möchten. result_array: Es ist auch ein eindimensionaler Bereich. Dies ist der Bereich, aus dem Sie den Wert abrufen möchten.

Lassen Sie uns diese XLOOKUP-Funktion in Aktion sehen.

XLOOKUP Beispiele:

image

Hier habe ich eine Datentabelle in Excel. Lassen Sie uns anhand dieser Datentabelle einige Funktionen untersuchen.

Funktionalität 1.ExactLookup auf der linken und rechten Seite des Suchwerts. Da wir wissen, dass die Excel VLOOKUP-Funktion keine Werte links vom Suchwert abrufen kann. Dafür müssen Sie die komplexe INDEX-MATCH-Kombination verwenden. Aber nicht mehr.

image

Angenommen, wir müssen alle in der Tabelle einiger Rollennummern verfügbaren Informationen abrufen. In diesem Fall müssen Sie auch die Region abrufen, die sich links von der Rollennummernspalte befindet.

Schreiben Sie diese Formel, I2:

=XLOOKUP(H2,$B$2:$B$14,$A$2:$A$14)

Wir erhalten das Ergebnis Nord für Rolle Nummer 112. Kopieren oder ziehen Sie die Formel in die folgenden Zellen, um sie mit den jeweiligen Regionen zu füllen.

Wie funktioniert es?

Der Mechanismus ist einfach. Diese Funktion sucht den lookup_value in lookup_array und gibt den Index einer ersten exakten Übereinstimmung zurück. Verwendet dann diesen Index, um den Wert aus result_array abzurufen. Diese Funktion funktioniert perfekt mit benannten Bereichen.

Verwenden Sie diese Formel ebenfalls, um den Wert aus jeder Spalte abzurufen.

image

Funktionalität 2. ExactHorizontalLookup über und unter dem Suchwert. Das XLOOKUP funktioniert auch als exakte HLOOKUP-Funktion. Die HLOOKUP-Funktion hat die gleiche Einschränkung wie die VLOOKUP-Funktion. Es kann keinen Wert über dem Suchwert abrufen. XLOOKUP funktioniert aber nicht nur als HLOOKUP, sondern überwindet auch diese Schwäche. Mal sehen wie.

Hypothetisch, wenn Sie zwei Datensätze vergleichen möchten. Der Suchdatensatz, den Sie bereits haben. Der Datensatz, mit dem Sie vergleichen möchten, befindet sich über dem Lookup-Bereich. Verwenden Sie in diesem Fall diese Formel.

=XLOOKUP(H7,$A$9:$E$9,$A$2:$E$2)

Ziehen Sie die Formel nach unten, und Sie haben den gesamten Datensatz zum Vergleichen der Zeile.

image

Funktionalität 3. Keine Spaltennummer und standardmäßige exakte Übereinstimmung erforderlich.

Wenn Sie die VLOOKUP-Funktion verwenden, müssen Sie die Spaltennummer angeben, aus der Sie die Werte abrufen möchten. Dazu müssen Sie die Spalten zählen oder einige Tricks anwenden, die Hilfe anderer Funktionen in Anspruch nehmen. Mit diesem UDF XLOOKUP müssen Sie das nicht tun.

Wenn Sie VLOOKUP verwenden, um nur einen Wert aus einer Spalte abzurufen oder um zu überprüfen, ob ein Wert in der Spalte vorhanden ist, ist dies für mich die beste Lösung.

Funktionalität 4. Ersetzt die Funktionen INDEX-MATCH, VLOOKUP, HLOOKUP

Für einfache Aufgaben ersetzt unsere XLOOKUP-Funktion die oben genannten Funktionen.

Einschränkungen von XLOOKUP:

Wenn es um komplexe Formeln geht, wie //lookup-formulas/vlookup-with-dynamic-col-index.html[VLOOKUP with Dynamic Col Index] `wo wir VLOOKUP die Lookup-Spalte mit Headern identifizieren, schlägt dieses XLOOKUP fehl.

Eine weitere Einschränkung besteht darin, dass diese Funktion unbrauchbar 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 von „benannte Bereiche“ überwunden werden.

Im Moment haben wir die ungefähre Funktionalität nicht hinzugefügt, daher können Sie die ungefähre Übereinstimmung natürlich nicht erhalten. Wir werden das zu früh hinzufügen.

Wenn die XLOOKUP-Funktion den Suchwert nicht findet, wird der Fehler #VALUE zurückgegeben, nicht # N / A.

Also ja Leute, so benutzt ihr 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 auf dem Suchwert verwenden. Wenn Sie noch Zweifel oder spezielle Anforderungen in Bezug auf diese Funktion oder eine EXCEL 2010/2013/2016/2019/365- oder VBA-bezogene Abfrage haben, fragen Sie diese im Kommentarbereich unten. Sie werden sicherlich eine Antwort bekommen.

Verwandte Artikel:

link: / custom-functions-in-vba-create-vba-function-to-return-array [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.

link: / excel-array-formeln-arrays-in-excel-formel [Arrays in Excel Formul] | Erfahren Sie, welche Arrays in Excel enthalten sind.

link: / vba-user-defined-function [So erstellen Sie eine benutzerdefinierte Funktion über VBA] | Erfahren Sie, wie Sie benutzerdefinierte Funktionen in Excel `link erstellen: / Benutzerdefinierte Funktionen-Benutzerdefinierte-Funktionen-aus-anderen-Arbeitsmappen-mit-vba-in-Microsoft-Excel [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 `link: / custom-functions-return-error-values-from-user-defined-functions-using-vba-in-microsoft-excel [Rückgabe von Fehlerwerten aus benutzerdefinierten Funktionen Funktionen mit VBA in Microsoft Excel] `| Erfahren Sie, wie Sie Fehlerwerte von einer benutzerdefinierten Funktion

zurückgeben können === Beliebte Artikel:

link: / allgemeine-themen-in-vba-teilen-excel-blatt-in-mehrere-dateien-basierend-auf-spalte-mit-vba [Excel-blatt in mehrere dateien basierend auf spalte mit vBA teilen] | Diese VBA-Code-Split-Excel-Tabelle basiert auf eindeutigen Werten in einer angegebenen Spalte.

Laden Sie die Arbeitsdatei herunter.

link: / allgemeine-themen-in-vba-ausschalten-warnwarnmeldungen-mit-vba-in-microsoft-excel [Warnmeldungen mit VBA in Microsoft Excel 2016 ausschalten] | Um Warnmeldungen zu deaktivieren, die den laufenden VBA-Code unterbrechen, verwenden wir die Application-Klasse.

link: / files-workbook-and-worksheets-in-vba-add-and-save-new-workbook-using-vba-in-microsoft-excel [Neue Arbeitsmappe mit VBA in Microsoft Excel 2016 hinzufügen und speichern] | Zum Hinzufügen und Speichern von Arbeitsmappen mit VBA verwenden wir die Klasse Arbeitsmappen. Workbooks.Add fügt problemlos neue Arbeitsmappen hinzu …​