Wie man die NEUE & ERWEITERTE XLOOKUP-Funktion in Excel verwendet (10 Beispiele)
Das neue XLOOKUP von Excel ist die leistungsstärkste und einfachste Suchfunktion, die Excel bieten kann. Durch unermüdliche Bemühungen hat Microsoft diese Funktion endlich veröffentlicht, um VLOOKUP, HLOOKUP, INDEX+MATCH und andere Suchfunktionen zu ersetzen.
In diesem Tutorial zeigen wir Ihnen, welche Vorteile XLOOKUP bietet und wie Sie es erhalten und anwenden können, um verschiedene Suchprobleme zu lösen.
XLOOKUP-Beispieldatei herunterladen
Wie erhält man XLOOKUP?
Da die XLOOKUP-Funktion nur in Excel für Microsoft365, Excel2021 und späteren Versionen sowie Excel für das Web verfügbar ist. Wenn Sie Excel2019 oder früher verwenden, sollten Sie ein Upgrade in Betracht ziehen, um Zugriff auf XLOOKUP zu erhalten.
Syntax
Die Funktion sucht einen Bereich oder ein Array und gibt dann den Wert des ersten übereinstimmenden Ergebnisses zurück. Die Syntax ist wie folgt:
Argumente:
- Lookup_value (erforderlich): der Wert, den Sie suchen. Er kann in jeder Spalte des Tabellenbereichs sein.
- Lookup_array (erforderlich): das Array oder der Bereich, in dem Sie nach dem Suchwert suchen.
- Return_array (erforderlich): das Array oder der Bereich, aus dem Sie den Wert erhalten möchten.
- If_not_found (optional): der Wert, der zurückgegeben wird, wenn keine gültige Übereinstimmung gefunden wird. Sie können den Text im [if_not_found] anpassen, um anzuzeigen, dass keine Übereinstimmung vorliegt.
Andernfalls wird der Rückgabewert standardmäßig #N/A sein. - Match_mode (optional): hier können Sie festlegen, wie lookup_value mit den Werten in lookup_array abgeglichen werden soll.
- 0 (Standard) = Exakte Übereinstimmung. Wenn keine Übereinstimmung gefunden wird, wird #N/A zurückgegeben.
- -1 = Exakte Übereinstimmung. Wenn keine Übereinstimmung gefunden wird, wird der nächstkleinere Wert zurückgegeben.
- 1 = Exakte Übereinstimmung. Wenn keine Übereinstimmung gefunden wird, wird der nächstgrößere Wert zurückgegeben.
- 2 = Teilweise Übereinstimmung. Verwenden Sie Platzhalterzeichen wie *, ? und ~, um eine Platzhalter-Übereinstimmung durchzuführen.
- Search_mode (optional): hier können Sie die Suchreihenfolge festlegen.
- 1 (Standard) = Suche des lookup_value vom ersten bis zum letzten Element im lookup_array.
- -1 = Suche des lookup_value vom letzten bis zum ersten Element. Dies hilft, wenn Sie das letzte übereinstimmende Ergebnis im lookup_array erhalten müssen.
- 2 = Durchführung einer binären Suche, die erfordert, dass das lookup_array in aufsteigender Reihenfolge sortiert ist. Wenn nicht sortiert, wäre das Rückgaberesultat ungültig.
- -2 = Durchführung einer binären Suche, die erfordert, dass das lookup_array in absteigender Reihenfolge sortiert ist. Wenn nicht sortiert, wäre das Rückgaberesultat ungültig.
Für detaillierte Informationen zu den Argumenten, gehen Sie bitte wie folgt vor:
1. Geben Sie die unten stehende Syntax in eine leere Zelle ein, beachten Sie bitte, dass Sie nur eine Seite der Klammer eingeben müssen.
2. Drücken Sie Ctrl+A, dann erscheint ein Eingabefeld, das die Funktionsargumente zeigt. Und die andere Seite der Klammer wird automatisch abgeschlossen.
3. Ziehen Sie das Datenfeld nach unten, dann können Sie alle sechs Funktionsargumente von XLOOKUP sehen.
 | >>> |  |
Beispiele
Ich bin sicher, Sie haben die grundlegenden Prinzipien von XLOOKUP jetzt gemeistert. Lassen Sie uns direkt in die praktischen Beispiele von XLOOKUP eintauchen.
Beispiel1: Exakte Übereinstimmung
Eine exakte Übereinstimmung mit XLOOKUP durchführen
Haben Sie sich jemals geärgert, weil Sie den Modus für die exakte Übereinstimmung jedes Mal angeben mussten, wenn Sie VLOOKUP verwenden? Glücklicherweise existiert dieses Problem nicht mehr, wenn Sie die erstaunliche XLOOKUP-Funktion ausprobieren. Standardmäßig erzeugt XLOOKUP eine exakte Übereinstimmung.
Angenommen, Sie haben eine Liste mit Bürobedarf und müssen den Stückpreis eines Artikels, sagen wir Maus, wissen, gehen Sie bitte wie folgt vor.
Geben Sie die unten stehende Formel in die leere Zelle F2 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
=XLOOKUP(E2,A2:A10,C2:C10)
Jetzt kennen Sie den Stückpreis der Maus mit der erweiterten XLOOKUP-Formel. Da der Abgleichscode standardmäßig auf eine exakte Übereinstimmung eingestellt ist, müssen Sie ihn nicht angeben. Viel einfacher und effizienter als VLOOKUP.
Nur ein paar Klicks, um die exakte Übereinstimmung zu erhalten
Vielleicht verwenden Sie eine niedrigere Version von Excel und haben noch nicht vor, auf Excel2021 oder Microsoft365 zu aktualisieren. In diesem Fall empfehle ich eine praktische Funktion - "Daten in einem Bereich finden". Mit dieser Funktion können Sie das Ergebnis ohne komplizierte Formeln oder Zugriff auf XLOOKUP erhalten.
1. Klicken Sie auf die Zelle, um das übereinstimmende Ergebnis zu platzieren.
2. Gehen Sie zum "Kutools"-Tab, klicken Sie auf "Formelhelfer" und dann auf "Formelhelfer" in der Dropdown-Liste.
3. Konfigurieren Sie im Formelhelfer-Dialogfeld bitte wie folgt:
- Wählen Sie "Suchen" im Abschnitt "Formeltyp" aus;
- Wählen Sie im Abschnitt "Wählen Sie eine Formel aus" die Option "Daten in einem Bereich finden" aus;
- Im Abschnitt "Argumenteingabe" gehen Sie bitte wie folgt vor:
- Wählen Sie im Feld "Tabellenbereich" den Datenbereich aus, der den Suchwert und den Ergebniswert enthält;
- Wählen Sie im Feld "Suchwert" die Zelle oder den Bereich des Wertes aus, den Sie suchen. Beachten Sie bitte, dass er sich in der ersten Spalte des Tabellenbereichs befinden muss;
- Wählen Sie im Feld "Spalte" die Spalte aus, aus der Sie den übereinstimmenden Wert zurückgeben möchten.
4. Klicken Sie auf die Schaltfläche OK, um das Ergebnis zu erhalten.
Kutools für Excel - Verleihen Sie Excel mit über 300 essenziellen Tools einen echten Schub. Nutzen Sie dauerhaft kostenlose KI-Funktionen! Holen Sie es sich jetzt
Beispiel2. Ungefähre Übereinstimmung
Eine ungefähre Übereinstimmung mit XLOOKUP durchführen
Um eine ungefähre Suche durchzuführen, müssen Sie den Abgleichmodus im fünften Argument auf1 oder -1 einstellen. Wenn keine exakte Übereinstimmung gefunden wird, wird der nächstgrößere oder nächstkleinere Wert zurückgegeben.
In diesem Fall müssen Sie die Steuersätze der Einkommen Ihrer Mitarbeiter kennen. Auf der linken Seite der Tabelle befinden sich die Bundessteuerklassen für2021. Wie können Sie den Steuersatz Ihrer Mitarbeiter in Spalte E ermitteln? Keine Sorge. Gehen Sie bitte wie folgt vor:
1. Geben Sie die unten stehende Formel in die leere Zelle E2 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
Ändern Sie dann die Formatierung des zurückgegebenen Ergebnisses nach Bedarf.
=XLOOKUP(D2,B2:B8,A2:A8,,1)
 | >>> |  |
√ Hinweis: Das vierte Argument [If_not_found] ist optional, daher habe ich es einfach weggelassen.
2. Jetzt kennen Sie den Steuersatz der Zelle D2. Um die restlichen Ergebnisse zu erhalten, müssen Sie die Zellreferenzen des lookup_array und return_array in absolut umwandeln.
- Doppelklicken Sie auf Zelle E2, um die Formel =XLOOKUP(D2,B2:B8,A2:A8,,1) anzuzeigen;
- Wählen Sie den Suchbereich B2:B8 in der Formel aus, drücken Sie die F4-Taste, um $B$2:$B$8 zu erhalten;
- Wählen Sie den Rückgabebereich A2:A8 in der Formel aus, drücken Sie die F4-Taste, um $A$2:$A$8 zu erhalten;
- Drücken Sie die Eingabetaste, um das Ergebnis der Zelle E2 zu erhalten.
 | >>> |  |
3. Ziehen Sie dann den Füllgriff nach unten, um alle Ergebnisse zu erhalten.
√ Hinweis:
- Durch Drücken der F4-Taste auf der Tastatur können Sie die Zellreferenz in eine absolute Referenz ändern, indem Sie Dollarzeichen vor die Zeile und Spalte setzen.
- Nachdem wir die absolute Referenz auf den Such- und Rückgabebereich angewendet haben, haben wir die Formel in Zelle E2 auf diese Version geändert:
=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)
- Wenn Sie den Füllgriff von Zelle E2 nach unten ziehen, werden die Formeln in jeder Zelle der Spalte E nur im Aspekt des lookup_value geändert.
Zum Beispiel wird die Formel in E13 jetzt in diese umgewandelt:
=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)
Beispiel3: Platzhalter-Übereinstimmung
Eine Platzhalter-Übereinstimmung mit XLOOKUP durchführen
Bevor wir uns die XLOOKUP-Platzhalter-Übereinstimmungsfunktion ansehen, lassen Sie uns zuerst sehen, was Platzhalter sind.
In Microsoft Excel sind Platzhalter eine spezielle Art von Zeichen, die beliebige Zeichen ersetzen können. Sie sind besonders hilfreich, wenn Sie Teilübereinstimmungen durchführen möchten.
Es gibt drei Arten von Platzhaltern: ein Sternchen (*), ein Fragezeichen (?) und eine Tilde (~).
- Sternchen (*) repräsentiert eine beliebige Anzahl von Zeichen im Text;
- Fragezeichen (?) steht für ein beliebiges einzelnes Zeichen im Text;
- Tilde (~) wird verwendet, um die Platzhalter (*, ? ~) in literale Zeichen umzuwandeln. Setzen Sie die Tilde (~) vor die Platzhalter, um diese Funktion zu erfüllen;
In den meisten Fällen verwenden wir bei der Durchführung der XLOOKUP-Platzhalter-Übereinstimmungsfunktion das Sternchen (*)-Zeichen. Lassen Sie uns nun sehen, wie die Platzhalter-Übereinstimmung funktioniert.
Angenommen, Sie haben eine Liste der Marktkapitalisierung der50 größten amerikanischen Unternehmen und möchten die Marktkapitalisierung einiger Unternehmen wissen, aber die Unternehmensnamen sind abgekürzt, dies ist das perfekte Szenario für eine Platzhalter-Übereinstimmung. Bitte folgen Sie mir Schritt für Schritt, um den Trick zu machen.
√ Hinweis: Um eine Platzhalter-Übereinstimmung durchzuführen, ist es am wichtigsten, das fünfte Argument [match_mode] auf2 einzustellen.
1. Geben Sie die unten stehende Formel in die leere Zelle H3 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)
 | >>> |  |
2. Jetzt kennen Sie das Ergebnis der Zelle H3. Um die restlichen Ergebnisse zu erhalten, müssen Sie das lookup_array und return_array fixieren, indem Sie den Cursor im Array platzieren und die F4-Taste drücken. Dann wird die Formel in H3 zu:
=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)
3. Ziehen Sie den Füllgriff nach unten, um alle Ergebnisse zu erhalten.
√ Hinweis:
- Der lookup_value der Formel in Zelle H3 ist "*"&G3&"*". Wir verketten das Sternchen-Platzhalterzeichen (*) mit dem Wert G3 mithilfe des kaufmännischen Und-Zeichens (&).
- Das vierte Argument [If_not_found] ist optional, daher habe ich es einfach weggelassen.
Beispiel4: Suche nach links
Suche von rechts nach links mit XLOOKUP
Ein Nachteil von VLOOKUP ist, dass es darauf beschränkt ist, Suchen rechts von der Suchspalte durchzuführen. Wenn Sie versuchen, Werte links von der Suchspalte zu suchen, erhalten Sie den #N/A-Fehler. Keine Sorge. XLOOKUP ist die perfekte Suchfunktion, um dieses Problem zu lösen.
XLOOKUP ist darauf ausgelegt, Werte links oder rechts von der Suchspalte zu suchen. Es hat keine Grenzen und erfüllt die Bedürfnisse der Excel-Benutzer. Im folgenden Beispiel zeigen wir Ihnen den Trick.
Angenommen, Sie haben eine Liste von Ländern mit Telefoncodes und möchten den Ländernamen mit einem bekannten Telefoncode suchen.
Wir müssen Spalte C durchsuchen und den Wert in Spalte A zurückgeben. Gehen Sie bitte wie folgt vor:
1. Geben Sie die unten stehende Formel in die leere Zelle G2 ein.
=XLOOKUP(F2,C2:C11,A2:A11)
2. Drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
√ Hinweis: Die XLOOKUP-Suche nach links-Funktion kann Index und Match ersetzen, um Werte nach links zu suchen.
Wert von rechts nach links mit wenigen Klicks suchen
Für diejenigen, die sich keine Formeln merken möchten, empfehle ich hier eine nützliche Funktion - "Von rechts nach links suchen". Mit dieser Funktion können Sie eine Suche von rechts nach links innerhalb weniger Sekunden durchführen.
1. Gehen Sie zum "Kutools"-Tab in Excel, finden Sie "Erweiterte SVERWEIS" und klicken Sie auf "Von rechts nach links suchen" in der Dropdown-Liste.
2. Im Dialogfeld "Von rechts nach links suchen" müssen Sie wie folgt konfigurieren:
- Im Abschnitt "Bereich der zu durchsuchenden Werte und Ausgabebereich" geben Sie den Suchbereich und den Ausgabebereich an;
- Im Abschnitt "Datenbereich" geben Sie den Datenbereich ein und geben dann die "Schlüsselspalte" und "Rückgabespalte" an;
3. Klicken Sie auf die Schaltfläche OK, um das Ergebnis zu erhalten.
Kutools für Excel - Verleihen Sie Excel mit über 300 essenziellen Tools einen echten Schub. Nutzen Sie dauerhaft kostenlose KI-Funktionen! Holen Sie es sich jetzt
Beispiel5: Vertikale oder horizontale Suche
Eine vertikale oder horizontale Suche mit XLOOKUP durchführen
Als Excel-Benutzer sind Sie möglicherweise mit den Funktionen VLOOKUP und HLOOKUP vertraut. VLOOKUP dient dazu, vertikal in einer Spalte zu suchen, und HLOOKUP dient dazu, horizontal in einer Zeile zu suchen.
Jetzt kombiniert das neue XLOOKUP beide, was bedeutet, dass Sie nur eine Syntax verwenden müssen, um eine vertikale oder horizontale Suche durchzuführen. Genial, oder?
Im folgenden Beispiel werden wir veranschaulichen, wie Sie nur eine XLOOKUP-Syntax verwenden, um vertikale oder horizontale Suchen durchzuführen.
Um eine vertikale Suche durchzuführen, geben Sie die unten stehende Formel in die leere Zelle E2 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
=XLOOKUP(E1,A2:A13,B2:B13)
Um eine horizontale Suche durchzuführen, geben Sie die unten stehende Formel in die leere Zelle P2 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
=XLOOKUP(P1,B1:M1,B2:M2)
Wie Sie sehen können, ist die Syntax dieselbe. Der einzige Unterschied zwischen den beiden Formeln besteht darin, dass Sie bei der vertikalen Suche Spalten eingeben, während Sie bei der horizontalen Suche Zeilen eingeben.
Beispiel6: Zwei-Wege-Suche
Eine Zwei-Wege-Suche mit XLOOKUP durchführen
Verwenden Sie immer noch die Funktionen INDEX und MATCH um einen Wert in einer zweidimensionalen Tabelle zu suchen? Probieren Sie das verbesserte XLOOKUP aus, um Ihre Arbeit einfacher zu erledigen.
XLOOKUP kann eine doppelte Suche durchführen und den Schnittpunkt von zwei Werten finden. Indem man ein XLOOKUP in ein anderes verschachtelt, kann das innere XLOOKUP eine gesamte Zeile oder Spalte zurückgeben, dann wird diese zurückgegebene Zeile oder Spalte in das äußere XLOOKUP als Rückgabearray eingegeben.
Angenommen, Sie haben eine Liste von Schülernoten mit verschiedenen Disziplinen und möchten die Note des Fachs Chemie von Kim wissen.
Lassen Sie uns sehen, wie wir das magische XLOOKUP verwenden, um den Trick zu machen.
- Wir führen das „innere“ XLOOKUP aus, um Werte einer gesamten Spalte zurückzugeben. XLOOKUP(H2,B1:E1,B2:E10) kann einen Bereich von Chemienoten erhalten.
- Wir verschachteln das „innere“ XLOOKUP in das „äußere“ XLOOKUP, indem wir das „innere“ XLOOKUP als return_array in der vollständigen Formel verwenden.
- Dann kommt hier die endgültige Formel:
=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))
- Geben Sie die obige Formel in die leere Zelle H3 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
Oder Sie können es umgekehrt machen, verwenden Sie das „innere“ XLOOKUP, um Werte einer gesamten Zeile zurückzugeben, die alle Fachnoten von Kim sind. Dann verwenden Sie das „äußere“ XLOOKUP, um die Chemienote unter allen Fachnoten von Kim zu suchen.
- Geben Sie die unten stehende Formel in die leere Zelle H4 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))
Die Zwei-Wege-Suchfunktion von XLOOKUP ist auch die perfekte Veranschaulichung seiner vertikalen und horizontalen Suchfunktion. Probieren Sie es aus, wenn Sie möchten!
Beispiel7: Nicht gefundene Nachricht anpassen
Nicht gefundene Nachricht mit XLOOKUP anpassen
Wie bei anderen Suchfunktionen wird die #N/A-Fehlermeldung zurückgegeben, wenn keine Übereinstimmung gefunden werden kann. Dies könnte für einige Excel-Benutzer verwirrend sein. Aber die gute Nachricht ist, dass die Fehlerbehandlung im vierten Argument der XLOOKUP-Funktion verfügbar ist.
Mit dem eingebauten [if_not_found]-Argument können Sie eine benutzerdefinierte Nachricht festlegen, um das #N/A-Ergebnis zu ersetzen. Geben Sie den benötigten Text im optionalen vierten Argument ein und schließen Sie den Text in doppelte Anführungszeichen (") ein.
Zum Beispiel wird die Stadt Denver nicht gefunden, sodass XLOOKUP die #N/A-Fehlermeldung zurückgibt. Aber nachdem wir das vierte Argument mit dem Text „Keine Übereinstimmung“ angepasst haben, zeigt die Formel den Text „Keine Übereinstimmung“ anstelle der Fehlermeldung an.
Geben Sie die unten stehende Formel in die leere Zelle F3 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
=XLOOKUP(E2,A2:A11,C2:C11,"Keine Übereinstimmung")
#N/A-Fehler mit einer praktischen Funktion anpassen
Um den #N/A-Fehler schnell mit Ihrer benutzerdefinierten Nachricht zu überschreiben, ist Kutools für Excel ein perfektes Tool in Excel, um Ihnen zu helfen. Mit seiner eingebauten Funktion „Ersetze0 oder #N/A durch Leerzeichen oder einen bestimmten Wert“ können Sie die nicht gefundene Nachricht ohne komplizierte Formeln oder Zugriff auf XLOOKUP festlegen.
1. Gehen Sie zum "Kutools"-Tab in Excel, finden Sie "Erweiterte SVERWEIS" und klicken Sie auf "Ersetze0 oder #N/A durch Leerzeichen oder einen bestimmten Wert" in der Dropdown-Liste.
2. Im Dialogfeld "Ersetze0 oder #N/A durch Leerzeichen oder einen bestimmten Wert" müssen Sie wie folgt konfigurieren:
- Im Abschnitt "Bereich der zu durchsuchenden Werte und Ausgabebereich" wählen Sie den Suchbereich und den Ausgabebereich aus;
- Wählen Sie dann die Option "Ersetze0 oder #N/A durch einen bestimmten Wert" aus, geben Sie den gewünschten Text ein;
- Im Abschnitt "Datenbereich" wählen Sie den Datenbereich aus und geben dann die "Schlüsselspalte" und "Rückgabespalte" an.
3. Klicken Sie auf die Schaltfläche OK, um das Ergebnis zu erhalten. Die angepasste Nachricht wird angezeigt, wenn keine Übereinstimmung gefunden wird.
Kutools für Excel - Verleihen Sie Excel mit über 300 essenziellen Tools einen echten Schub. Nutzen Sie dauerhaft kostenlose KI-Funktionen! Holen Sie es sich jetzt
Beispiel8: Mehrere Werte
Mehrere Werte mit XLOOKUP zurückgeben
Ein weiterer Vorteil von XLOOKUP ist seine Fähigkeit, mehrere Werte gleichzeitig für dieselbe Übereinstimmung zurückzugeben. Geben Sie eine Formel ein, um das erste Ergebnis zu erhalten, dann werden andere zurückgegebene Werte automatisch in die benachbarten leeren Zellen übertragen.
Im folgenden Beispiel möchten Sie alle Informationen über die Schüler-ID „FG9940005“ erhalten. Der Trick besteht darin, einen Bereich als return_array in der Formel anzugeben, anstatt einer einzelnen Spalte oder Zeile. In diesem Fall ist der Rückgabebereich B2:D9, einschließlich drei Spalten.
Geben Sie die unten stehende Formel in die leere Zelle G2 ein und drücken Sie die Eingabetaste, um alle Ergebnisse zu erhalten.
=XLOOKUP(F2,A2:A9,B2:D9)
Alle Ergebniszellen zeigen dieselbe Formel an. Sie können die Formel in der ersten Zelle bearbeiten oder ändern, aber in anderen Zellen ist die Formel nicht bearbeitbar. Sie können sehen, dass die Formelleiste ausgegraut ist, was bedeutet, dass keine Änderungen daran vorgenommen werden können.
Alles in allem ist die Mehrwertfunktion von XLOOKUP eine nützliche Verbesserung im Vergleich zu VLOOKUP. Sie sind frei davon, jede Spaltennummer separat für jede Formel anzugeben. Daumen hoch!
Beispiel9. Mehrere Kriterien
Mehrkriteriensuche mit XLOOKUP durchführen
Ein weiteres erstaunliches neues Feature von XLOOKUP ist seine Fähigkeit, mit mehreren Kriterien zu suchen. Der Trick besteht darin, Suchwerte und Sucharrays mit dem "&"-Operator separat in der Formel zu verketten. Lassen Sie uns dies durch das folgende Beispiel veranschaulichen.
Wir müssen den Preis der mittelblauen Vase wissen. In diesem Fall sind drei Suchwerte (Kriterien) erforderlich, um eine Übereinstimmung zu finden. Geben Sie die unten stehende Formel in die leere Zelle I2 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)
√ Hinweis: XLOOKUP kann Arrays direkt verarbeiten. Es ist nicht erforderlich, die Formel mit Strg + Umschalt + Eingabe zu bestätigen.
Mehrbedingungssuche mit einer schnellen Methode
Gibt es eine schnellere und einfachere Möglichkeit, eine Mehrkriteriensuche als XLOOKUP in Excel durchzuführen? Kutools für Excel bietet eine erstaunliche Funktion - "Mehrere Bedingungen suchen". Mit dieser Funktion können Sie eine Mehrkriteriensuche mit nur wenigen Klicks durchführen!
1. Gehen Sie zum "Kutools"-Tab in Excel, finden Sie "Erweiterte SVERWEIS" und klicken Sie auf "Mehrere Bedingungen suchen" in der Dropdown-Liste.
2. Im Dialogfeld "Mehrere Bedingungen suchen" gehen Sie bitte wie folgt vor:
- Im Abschnitt "Bereich der zu durchsuchenden Werte und Ausgabebereich" wählen Sie den Suchwertbereich und den Ausgabebereich aus;
- Im Abschnitt "Datenbereich" führen Sie bitte die folgenden Berechnungen durch:
- Wählen Sie die entsprechenden Schlüsselspalten aus, die die Suchwerte enthalten, indem Sie die Strg-Taste im Feld "Schlüssel" gedrückt halten;
- Geben Sie die Spalte an, die die zurückgegebenen Werte im Feld "Rückgabe" enthält.
3. Klicken Sie auf die Schaltfläche OK, um das Ergebnis zu erhalten.
√ Hinweis:
- Der Abschnitt Ersetze nicht gefundene Werte (#N/A) durch angegebenen Wert ist im Dialogfeld optional, Sie können ihn angeben oder nicht.
- Die Anzahl der im Feld Schlüsselspalte eingegebenen Spalten muss gleich der Anzahl der im Feld Bereich der zu durchsuchenden Werte eingegebenen Spalten sein, und die Reihenfolge der Kriterien in beiden Feldern muss eins zu eins übereinstimmen.
Kutools für Excel - Verleihen Sie Excel mit über 300 essenziellen Tools einen echten Schub. Nutzen Sie dauerhaft kostenlose KI-Funktionen! Holen Sie es sich jetzt
Beispiel10. Wert mit der letzten Übereinstimmung finden
Das letzte übereinstimmende Ergebnis mit XLOOKUP erhalten
Um den letzten übereinstimmenden Wert in Excel zu finden, stellen Sie das sechste Argument auf die Suche in umgekehrter Reihenfolge ein.
Standardmäßig ist der Suchmodus in XLOOKUP auf1 eingestellt, was bedeutet, dass von zuerst bis zuletzt gesucht wird. Aber das Gute an XLOOKUP ist, dass die Suchrichtung geändert werden kann. XLOOKUP bietet das optionale [search mode]-Argument, um die Suchreihenfolge zu steuern. Stellen Sie einfach den Suchmodus im sechsten Argument auf -1 ein, die Suchrichtung wird geändert, um von zuletzt bis zuerst zu suchen.
Bitte sehen Sie sich das folgende Beispiel an. Wir möchten den letzten Verkauf von Emma in der Datenbank wissen.
Geben Sie die unten stehende Formel in die leere Zelle G2 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
=XLOOKUP(F2,B2:B11,D2:D11,,,-1)
√ Hinweis: Die vierte und fünfte Argumente sind optional und in diesem Fall weggelassen. Wir haben nur das optionale sechste Argument auf -1 gesetzt.
Einfach den letzten übereinstimmenden Wert mit einem erstaunlichen Tool suchen
Falls Sie keinen Zugriff auf XLOOKUP haben und sich auch keine komplizierten Formeln merken möchten, können Sie die Funktion "Von unten nach oben suchen" anwenden, um es einfach zu erledigen.
1. Gehen Sie zum "Kutools"-Tab in Excel, finden Sie "Erweiterte SVERWEIS" und klicken Sie auf "Von unten nach oben suchen" in der Dropdown-Liste.
2. Im Dialogfeld "Von unten nach oben suchen" müssen Sie wie folgt konfigurieren:
- Im Abschnitt "Bereich der zu durchsuchenden Werte und Ausgabebereich" wählen Sie den Suchbereich und den Ausgabebereich aus;
- Im Abschnitt "Datenbereich" wählen Sie den Datenbereich aus und geben dann die "Schlüsselspalte" und "Rückgabespalte" an.
3. Klicken Sie auf die Schaltfläche OK, um das Ergebnis zu erhalten.
√ Hinweis: Der Abschnitt Ersetze nicht gefundene Werte (#N/A) durch angegebenen Wert ist im Dialogfeld optional, Sie können ihn angeben oder nicht.
Kutools für Excel - Verleihen Sie Excel mit über 300 essenziellen Tools einen echten Schub. Nutzen Sie dauerhaft kostenlose KI-Funktionen! Holen Sie es sich jetzt
XLOOKUP-Beispieldatei herunterladen
Verwandte Artikel:
- Wie verwendet man INDEX und MATCH zusammen in Excel?
- Wie wendet man Fuzzy Match an, um ein ungefähres Übereinstimmungsergebnis in Excel zu finden?
- Wie verwendet man die Zwei-Wege-Suchformel in Excel?
- Wie sucht man einen Wert mit mehreren Kriterien in Excel?
- Wie sucht man Werte von rechts nach links in Excel?
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins