So verwenden Sie die XVERWEIS-Funktion in Excel 2016

Excel 2016 für Office 365-Abonnenten unter Windows und Mac unterstützt jetzt eine neue XVERWEIS-Funktion, die als wesentlich einfacherer und vielseitigerer Ersatz für die sehr beliebte (aber oft verleumdete) vertikale Nachschlagefunktion SVERWEIS (weiß nicht, was das X in XVERWEIS) ist, angepriesen wird steht für; umfangreich vielleicht?).

Für diejenigen unter Ihnen, die noch nicht mit SVERWEIS vertraut sind (wird als dritthäufigste Funktion direkt nach SUM und AVERAGE angesehen), diese Funktion durchsucht die ganz linke Spalte einer bestimmten Lookup-Tabelle vertikal nach Zeilen von oben nach unten, bis sie einen Wert in a findet Nachschlagespalte, die durch eine Offset-Nummer gekennzeichnet ist, die mit der von Ihnen gesuchten übereinstimmt oder diese überschreitet. Obwohl die SVERWEIS-Funktion sehr nützlich ist, um bestimmte Elemente in einer langen Liste oder Spalte einer Datentabelle in Ihrem Arbeitsblatt zu finden, weist sie mehrere Einschränkungen auf, die diese neue Nachschlagefunktion nicht teilt, wie XVERWEIS:

• Findet standardmäßig exakte Übereinstimmungen für Ihren Lookup-Wert im Lookup-Bereich
• Kann sowohl vertikal (nach Zeile) als auch horizontal (nach Spalte) in einer Tabelle suchen, wodurch die Verwendung der HVERWEIS-Funktion bei der horizontalen Suche nach Spalten ersetzt wird
• Kann nach links oder rechts suchen, sodass sich der Nachschlagebereich in Ihrer Nachschlagetabelle nicht in einer Spalte links von der als Rückgabebereich festgelegten Spalte befinden muss, damit die Funktion funktioniert
• Wenn der Standardwert für exakte Übereinstimmung verwendet wird, funktioniert es auch dann, wenn die Werte im Nachschlagebereich nicht in einer bestimmten Reihenfolge sortiert sind
• Kann mit einem optionalen Suchmodusargument von der untersten Reihe nach oben im Lookup-Array-Bereich suchen

Die XLOOKUP-Funktion hat fünf mögliche Argumente, von denen die ersten drei erforderlich und die letzten beiden optional sind, wobei die folgende Syntax verwendet wird:

XLOOKUP( lookup_value , lookup_array , return_array ,[ match_mode ],[ search_mode ])

Das erforderliche Argument lookup_value bezeichnet den Wert oder das Element, nach dem Sie suchen. Das erforderliche Array- Argument look_up gibt den Zellbereich an, der nach diesem Lookup-Wert durchsucht werden soll, und das Argument return_array bezeichnet den Zellbereich , der den Wert enthält, der zurückgegeben werden soll, wenn Excel eine genaue Übereinstimmung findet.

* Denken Sie daran, wenn Sie die Argumente lookup_array und return_array in Ihrer XLOOKUP-Funktion angeben, dass beide Bereiche gleich lang sein müssen, sonst gibt Excel den #VALUE! Fehler in deiner Formel. Aus diesem Grund verwenden Sie beim Definieren dieser Argumente eher Bereichsnamen oder Spaltennamen einer bestimmten Datentabelle, als auf sie hinzuweisen oder ihre Zellbezüge einzugeben .

Das optionale Argument match_mode kann einen der folgenden vier Werte enthalten:

• 0 für eine genaue Übereinstimmung (der Standardwert, genauso wie wenn kein match_mode- Argument angegeben ist)
• -1 für genaue Übereinstimmung oder nächstkleineren Wert
• 1 für exakte Übereinstimmung oder nächsthöheren Wert
• 2 für teilweise Übereinstimmung mit Platzhalterzeichen, die mit der Zellreferenz im lookup_value- Argument verbunden sind

Das optionale Argument search_mode kann einen der folgenden vier Werte enthalten:

• 1 , um von Anfang bis Ende zu suchen, d. h. von oben nach unten (Standardeinstellung, genauso wie wenn kein Suchmodus- Argument angegeben ist)
• -1 , um von unten nach oben zu suchen
• 2 für eine binäre Suche in aufsteigender Reihenfolge
• -2 für binäre Suche in absteigender Reihenfolge

Der beste Weg, um die Leistungsfähigkeit und Vielseitigkeit der neuen XLOOKUP-Funktion zu verstehen, besteht darin, sie in einem Excel-Arbeitsblatt in Aktion zu sehen. In der folgenden Abbildung habe ich ein Arbeitsblatt mit einer einfachen Verkaufsdatentabelle für 2019, die nach Ländern geordnet ist. Um XVERWEIS zu verwenden, um den Gesamtumsatz aus dieser Tabelle in Zelle E4 basierend auf dem Land zurückzugeben, das Sie in Zelle D4 des Arbeitsblatts eingeben, führen Sie die folgenden Schritte aus:

Positionieren Sie den Zellcursor in Zelle E4 des Arbeitsblatts

Klicken Sie auf der Registerkarte Formeln auf die Option Nachschlagen und Referenz, gefolgt von XVERWEIS unten im Dropdown-Menü, um das Dialogfeld Funktionsargumente zu öffnen.

Klicken Sie im Arbeitsblatt auf Zelle D4, um den Zellbezug in das Textfeld Lookup_value-Argument einzugeben.

Drücken Sie die Tabulatortaste, um das Argumenttextfeld Lookup_array auszuwählen, klicken Sie dann auf Zelle A4 und halten Sie die Umschalttaste gedrückt, während Sie die Strg-Pfeiltaste nach unten drücken, um A4:A8 als zu durchsuchenden Bereich auszuwählen (da der Bereich A3:B8 als Excel-Datentabelle definiert ist, Tabelle1[Land] erscheint im Textfeld anstelle des Bereichs A4:A8).

Drücken Sie die Tabulatortaste, um das Argumenttextfeld Return_array auszuwählen, klicken Sie dann auf Zelle B4 und halten Sie die Umschalttaste gedrückt, während Sie die Strg-Pfeiltaste nach unten drücken, um B4:B8 als den Bereich auszuwählen, der die Werte enthält, die basierend auf den Ergebnissen der Suche zurückgegeben werden sollen (erscheint als Tabelle1[Gesamtumsatz] im Textfeld).

Klicken Sie auf OK, um die XVERWEIS-Formel in Zelle E4 einzugeben.

Erstellen einer Formel mit XVERWEIS in Zelle E4, die Verkäufe basierend auf dem in Zelle D4 eingegebenen Land zurückgibt.

Excel gibt die XLOOKUP-Formel in Zelle E4 des Arbeitsblatts ein und gibt 4900 als Ergebnis zurück, da Costa Rica derzeit in die Nachschlagezelle D4 eingegeben wird und wie Sie in der Verkaufstabelle 2019 sehen können, ist dies tatsächlich der Gesamtumsatz für dieses Land.

Da XLOOKUP von rechts nach links genauso gut funktioniert wie von links nach rechts, können Sie diese Funktion auch verwenden, um das Land aus dieser Verkaufstabelle basierend auf einer bestimmten Verkaufszahl zurückzugeben. Die folgende Abbildung zeigt Ihnen, wie Sie dies tun. Diesmal erstellen Sie die XLOOKUP-Formel in Zelle D4 und legen den in Zelle E4 eingegebenen Wert (in diesem Fall 11.000) als lookup_value-Argument fest.

Darüber hinaus geben Sie -1 als match_mode-Argument ein, um den genauen Übereinstimmungsstandard der Funktion zu überschreiben, sodass Excel das Land mit einer genauen Übereinstimmung mit dem in der Nachschlagezelle E4 eingegebenen Verkaufswert oder das Land mit dem nächstniedrigeren Gesamtumsatz (Mexiko mit 10.000 USD in diesem Fall, da in dieser Tabelle kein Land mit 11.000 USD des Gesamtumsatzes enthalten ist). Ohne ein match_mode-Argument für diese Formel anzugeben, würde Excel als Ergebnis #NA zurückgeben, da es in dieser Verkaufstabelle keine genaue Übereinstimmung mit 11.000 $ gibt.

Erstellen einer Formel mit XLOOKUP in Zelle D4, die das Land basierend auf den in Zelle E4 eingegebenen Verkäufen zurückgibt

Da die XVERWEIS-Funktion die horizontale Suche nach Spalten genauso komfortabel wie die vertikale Suche nach Zeile ist, können Sie sie verwenden, um eine Formel zu erstellen, die eine Zwei-Wege-Suche durchführt (und die Notwendigkeit, eine Formel zu erstellen, die die INDEX- und MATCH-Funktionen kombiniert, wie in der Vergangenheit). Die folgende Abbildung, die die Produktionsplantabelle 2019 für die Teilenummern AB-100 bis AB-103 für die Monate April bis Dezember enthält, zeigt Ihnen, wie dies geschieht.

Erstellen einer Formel mit verschachtelten XLOOKUP-Funktionen, um die Anzahl der produzierten Einheiten für ein Teil in einem bestimmten Monat zurückzugeben

In Zelle B12 habe ich folgende Formel erstellt:

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(date_lookup,$B$2:$J$2,$B$3:$J$6))

Diese Formel beginnt mit der Definition einer XLOOKUP-Funktion, die vertikal zeilenweise nach einer genauen Übereinstimmung mit dem Teileeintrag sucht, der in der Zelle namens part_lookup (in diesem Fall Zelle B10) im Zellbereich $A$3:$A$6 der Produktionstabelle vorgenommen wurde . Beachten Sie jedoch, dass das Argument return_array für diese ursprüngliche LOOKUP-Funktion selbst eine zweite XLOOKUP-Funktion ist.

Diese zweite, verschachtelte XLOOKUP-Funktion durchsucht den Zellbereich $B$2:$J$2 horizontal nach Spalten nach einer genauen Übereinstimmung mit dem Datumseintrag, der in der Zelle date_lookup (in diesem Fall Zelle B11) vorgenommen wurde. Das Argument return_array für diese zweite, verschachtelte XLOOKUP-Funktion ist $B$3:$J$6, der Zellbereich aller Produktionswerte in der Tabelle.

Diese Formel funktioniert so, dass Excel zuerst das Ergebnis der zweiten, verschachtelten XLOOKUP-Funktion berechnet, indem es eine horizontale Suche durchführt, die in diesem Fall das Array im Zellbereich D3: D6 der Spalte vom 19. Juni (mit den Werten: 438, 153, 306 und 779) als Ergebnis. Dieses Ergebnis wird wiederum das Argument return_array für die ursprüngliche XLOOKUP-Funktion, die eine vertikale Suche nach Zeilen nach einer genauen Übereinstimmung mit dem Teilenummerneintrag in Zelle B11 (mit dem Namen part_lookup) durchführt. Da in diesem Beispiel diese part_lookup-Zelle AB-102 enthält, gibt die Formel nur den Produktionswert vom 19. Juni, 306, aus dem Ergebnis der zweiten, nächsten XVERWEIS-Funktion zurück.

Hier hast du es! Ein erster Blick auf XLOOKUP, eine leistungsstarke, vielseitige und ziemlich einfach zu verwendende neue Lookup-Funktion, die nicht nur die von den SLOOKUP- und HLOOKUP-Funktionen durchgeführten Einzelwert-Lookups, sondern auch die durch Kombination der INDEX- und MATCH-Funktionen ebenfalls.

* Leider ist die XVERWEIS-Funktion nicht abwärtskompatibel mit früheren Versionen von Microsoft Excel, die nur die SVERWEIS- und HVERWEIS-Funktionen unterstützen, oder kompatibel mit aktuellen Versionen, die sie noch nicht als eine ihrer Nachschlagefunktionen enthalten, wie Excel 2019 und Excel Online . Das bedeutet, dass alle diese Formeln #NAME? Fehlerwerte, wenn sie das Arbeitsblatt öffnen.