In diesem Excel-Tutorial erfahren Sie, wie Sie mit mehreren Übereinstimmungen (Ergebnissen) aus einer SVERWEIS-Funktion umgehen. Wir werden zwei verschiedene Techniken behandeln. Die erste verwendet tatsächlich die SVERWEIS-Funktion (zusammen mit COUNTIF). Die zweite verwendet INDEX / MATCH, um einen SVERWEIS zu simulieren.
SVERWEIS mit mehreren Ergebnissen
Um mehrere Übereinstimmungen mit der SVERWEIS-Funktion zu suchen, müssen Sie eine Hilfsspalte in der Datentabelle erstellen. Die Hilfsspalte verwendet die COUNTIF-Funktion, um für jede Instanz eine eindeutige ID zu erstellen. Die Hilfsspalte muss die Spalte ganz links im Datensatz sein. Wenn es nicht praktikabel ist, den Datensatz anzupassen, sollten Sie sich die andere Methode im nächsten Abschnitt ansehen.
Schauen wir uns diese Methode in der Praxis an:
Schritt 1:
Beachten Sie die Formel in Zelle F6:=B5&ZÄHLENWENN(B5:B$9,B5)
Insbesondere der referenzierte Bereich: B6:B$11 . Beachten Sie das $-Zeichen. Das $-Zeichen „sperrt“ die Zellreferenz: B$11. Wenn Sie also die Formel nach unten kopieren, bleibt B$11 gesperrt. B6 ist jedoch nicht gesperrt. Wenn Sie die Formel nach unten kopieren, wird B6 in B7 usw. umgewandelt. Bei dieser Technik wird für jede gefundene Instanz eine eindeutige Nummer erstellt. Der Grund, warum wir B6 entsperrt lassen, besteht darin, dass eine Instanz bei der Abrechnung aus der Gesamtzahl entfernt wird, wodurch die eindeutige Nummer erstellt wird.
Beachten Sie auch das &. & führt den Produktnamen mit seiner Instanznummer zusammen, um ein Feld zu erstellen, das wir in unserer Suche verwenden.
Schritt 2:
Wir haben die neue Hilfsspalte (project_adj) links neben den Datensatz in B14:C19 verschoben. Jetzt können wir den SVERWEIS für mehrere Ergebnisse ausführen. Anstatt nach Socken zu suchen, suchen Sie nach Socken1 und Socken2. Jetzt können Sie mehrere Zeilen mit SVERWEIS-Ergebnissen haben, die die mehreren gefundenen Übereinstimmungen darstellen.
Der Nachteil dieser Methode ist, dass Sie den ursprünglichen Datensatz bearbeiten (oder den Datensatz an anderer Stelle kopieren/einfügen müssen), um die Mehrfachergebnisse SVERWEIS auszuführen. Alternativ können Sie die INDEX / MATCH-Methode verwenden:
INDEX / MATCH für mehrere Match-Lookups
Die meisten Excel-Benutzer sind sich der Leistungsfähigkeit der SVERWEIS-Funktion bewusst, aber viele sind sich der Leistungsfähigkeit der INDEX-Funktion und der Match-Funktion, die in Kombination verwendet werden, nicht bewusst. Die Kombination INDEX / MATCH kann verwendet werden, um ein SVERWEIS zu emulieren, mit dem Vorteil einer größeren Flexibilität.
Hinweis: Das Bild direkt darunter enthält die Formeln. Das untere Bild enthält die Formelergebnisse.
Was passiert in den obigen Formeln?
MATCH - Sucht die Position eines Werts mit einem Bereich. In diesem Beispiel sucht MATCH nach „Socken“ in der Produktliste.
INDIREKT - Erstellt eine Referenz aus einer Textfolge. Wir verwenden dies, um das Sucharray für die Produktliste anzupassen. Sobald eine Übereinstimmung gefunden wurde, wird der Bereich angepasst, um diese Übereinstimmung von der Suche auszuschließen, sodass die nächste Übereinstimmung gefunden werden kann. In Zelle G5 setzen wir den Anfangsbereich auf B5: B10 (indem wir die anfängliche start_num in Zelle F5 auf 5 setzen). In G5 haben wir eine Übereinstimmung in Zeile 1 des Bereichs gefunden, also ist die start_num von F6 5+1=6.
INDEX - Gibt einen Wert aus einem Array basierend auf seiner Spalten-/Zeilennummerposition in diesem Array zurück.
!! Ich nehme an, dass Sie die Array-Formel einschließen, damit sie 1 Formel zum Verknüpfen haben …
