VLOOKUP mit Match

Die Vlookup-Formel funktioniert nur, wenn sich das Tabellenarray in der Formel nicht ändert. Wenn jedoch eine neue Spalte in die Tabelle eingefügt oder eine Spalte gelöscht wird, liefert die Formel ein falsches Ergebnis oder spiegelt einen Fehler wider, um die Formel fehlerfrei zu machen In solchen dynamischen Situationen verwenden wir die Übereinstimmungsfunktion, um den Index der Daten tatsächlich abzugleichen und das tatsächliche Ergebnis zurückzugeben.

Kombiniere VLOOKUP mit Match

Die vlookup-Formel ist die am häufigsten verwendete Funktion, mit der entweder derselbe Wert im angegebenen Spaltenindex oder der Wert aus einem anderen Spaltenindex unter Bezugnahme auf den übereinstimmenden Wert aus der ersten Spalte gesucht und zurückgegeben wird. Die größte Herausforderung bei der Verwendung von vlookup besteht darin, dass der anzugebende Spaltenindex statisch ist und keine dynamische Funktionalität aufweist. Insbesondere, wenn Sie an mehreren Kriterien arbeiten, bei denen Sie den Referenzspaltenindex manuell ändern müssen. Dadurch wird diese Anforderung erfüllt, indem die Formel „MATCH“ verwendet wird, um den häufig wechselnden Spaltenindex in der VLOOKUP-Formel besser in den Griff zu bekommen oder zu kontrollieren.

VLookup und Match Formula

# 1 - VLOOKUP Formel

Die Formel der VLOOKUP-Funktion in Excel

Hier sind alle einzugebenden Argumente obligatorisch.

  • Lookup_value - Hier sollte eine Referenzzelle oder ein Text mit doppelten Anführungszeichen eingegeben werden, um im Spaltenbereich identifiziert zu werden.
  • Tabellenarray - Für   dieses Argument muss der Tabellenbereich eingegeben werden, in dem der Lookup_value durchsucht werden soll, und die abzurufenden Daten befinden sich im jeweiligen Spaltenbereich.
  • Col_index_num - In diesem Argument muss die Spaltenindexnummer oder die Anzahl der Spalten aus der ersten Referenzspalte eingegeben werden, aus der der entsprechende Wert an derselben Position wie der in der ersten Spalte gesuchte Wert abgerufen werden muss.
  • [Range_lookup] - Dieses Argument bietet zwei Optionen.
  • TRUE - Ungefähre Übereinstimmung: - Das Argument kann entweder als TRUE oder als numerische „1“ eingegeben werden, wodurch die ungefähre Übereinstimmung zurückgegeben wird, die der Referenzspalte oder der ersten Spalte entspricht. Darüber hinaus müssen die Werte in der ersten Spalte des Tabellenarrays in aufsteigender Reihenfolge sortiert werden.
  • FALSE - Genaue Übereinstimmung: - Hier kann das einzugebende Argument entweder FALSE oder die Zahl „0“ sein. Diese Option gibt nur die genaue Übereinstimmung des entsprechenden Werts von der Position im ersten Spaltenbereich zurück. Wenn der Wert in der ersten Spalte nicht durchsucht wird, wird die Fehlermeldung "# N / A" zurückgegeben.

# 2 - Match Formel

Die Übereinstimmungsfunktion gibt die Zellenposition des für das angegebene Tabellenarray eingegebenen Werts zurück.

Alle Argumente innerhalb der Syntax sind obligatorisch.

  • Lookup_value - Hier kann das eingegebene Argument entweder die Zellreferenz des Werts oder eine Textzeichenfolge mit doppelten Anführungszeichen sein, deren Zellenposition gezogen werden muss.
  • Lookup_array - Der Array-Bereich für die Tabelle muss eingegeben werden, dessen Wert oder Zelleninhalt identifiziert werden soll.
  • [Übereinstimmungstyp] - Dieses Argument bietet drei Optionen, wie unten erläutert.
  • "1-kleiner als" - Hier ist das einzugebende Argument die numerische "1", die den Wert zurückgibt, der kleiner oder gleich dem Suchwert ist. Außerdem muss das Lookup-Array in aufsteigender Reihenfolge sortiert werden.
  • "0-Exakte Übereinstimmung" - Hier sollte das einzugebende Argument numerisch "0" sein. Diese Option gibt die genaue Position des übereinstimmenden Suchwerts zurück. Das Lookup-Array kann jedoch in beliebiger Reihenfolge sein.
  • "-1-Größer als" -  Das einzugebende Argument sollte numerisch "-1" sein. Die dritte Option ermittelt den kleinsten Wert, der größer oder gleich dem Suchwert ist. Hier muss die Reihenfolge für das Lookup-Array in absteigender Reihenfolge angegeben werden.

# 3 - VLOOKUP mit MATCH Formula

= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])

Wie verwende ich VLOOKUP mit Match Formula in Excel?

Das folgende Beispiel hilft beim Verständnis der Funktionsweise der Lookup- und Match-Formel beim Zusammenstellen.

Sie können diese VLookup mit Match Excel-Vorlage hier herunterladen - VLookup mit Match Excel-Vorlage

Beachten Sie die folgende Datentabelle, in der die Spezifikationen des zu kaufenden Fahrzeugs beschrieben sind.

Um die Klarheit der kombinierten Funktion für die Vlookup- und Match-Funktion zu erhalten, lassen Sie uns verstehen, wie die einzelnen Formeln funktionieren, und dann zu den Vlookup-Match-Ergebnissen gelangen, wenn sie zusammengestellt werden.

Schritt 1 - Wenden wir die Lookup-Formel auf individueller Ebene an, um zum Ergebnis zu gelangen.

Die Ausgabe wird unten gezeigt:

Hier bezieht sich der Suchwert auf $ B9, das Modell "E", und das Sucharray wird als Bereich der Datentabelle mit dem absoluten Wert "$" angegeben, der Spaltenindex wird auf die Spalte "4" bezogen, für die die Anzahl gilt Spalte "Typ" und die Bereichssuche erhalten eine genaue Übereinstimmung.

Daher wird die folgende Formel angewendet, um den Wert für die Spalte "Kraftstoff" zurückzugeben.

Die Ausgabe wird unten gezeigt:

Hier hilft der Suchwert mit der absoluten Zeichenfolge "$" für den Suchwert und lookup_array, die Referenzzelle zu reparieren, selbst wenn die Formel in eine andere Zelle kopiert wird. In der Spalte "Kraftstoff" müssen wir den Spaltenindex auf "5" ändern, da sich der Wert ändert, von dem die Daten abgerufen werden müssen.

Schritt 2 -  Wenden wir nun die Übereinstimmungsformel an, um die Position für den angegebenen Suchwert abzurufen.

Die Ausgabe wird unten gezeigt:

Wie im obigen Screenshot zu sehen ist, versuchen wir hier, die Spaltenposition aus dem Tabellenarray abzurufen. In diesem Fall wird die zu ziehende Spaltennummer als Zelle C8 bezeichnet, die Spalte "Typ" ist, und der zu durchsuchende Suchbereich wird als Bereich der Spaltenüberschriften angegeben, und der Übereinstimmungstyp erhält eine genaue Übereinstimmung als "Typ". 0 ”.

Somit gibt die folgende Tabelle das gewünschte Ergebnis für die Positionen der Spalte "Kraftstoff".

Hier wird nun die zu durchsuchende Spalte als Zelle D8 angegeben und der gewünschte Spaltenindex als "5" zurückgegeben.

Schritt 3 - Jetzt wird die Übereinstimmungsformel in der vlookup-Funktion verwendet, um den Wert von der identifizierten Spaltenposition abzurufen.

Die Ausgabe wird unten gezeigt:

In der obigen Formel wird die Übereinstimmungsfunktion anstelle des Spaltenindexparameters der vlookup-Funktion eingesetzt. Hier identifiziert die Übereinstimmungsfunktion die Nachschlagewertreferenzzelle "C8" und gibt die Spaltennummer über das angegebene Tabellenarray zurück. Diese Spaltenposition dient als Eingabe für das Spaltenindexargument in der vlookup-Funktion. Was wiederum hilft vlookup dabei, den Wert zu identifizieren, der von der resultierenden Spaltenindexnummer zurückgegeben werden soll?

In ähnlicher Weise haben wir vlookup mit Übereinstimmungsformel auch für die Spalte "Kraftstoff" angewendet.

Die Ausgabe wird unten gezeigt:

Damit können wir diese Kombinationsfunktion auch für andere Spalten „Typ“ und „Kraftstoff“ anwenden.

Dinge, an die man sich erinnern sollte

  • VLOOKUP kann nur auf der linken Seite auf Suchwerte angewendet werden. Alle Werte, die auf der rechten Seite der Datentabelle gesucht werden sollen, geben den Fehlerwert "# N / A" zurück.
  • Der im zweiten Argument eingegebene Bereich von table_array sollte die absolute Zellreferenz "$" sein. Dadurch wird der feste Tabellenarraybereich beibehalten, wenn die Suchformel auf andere Zellen angewendet wird. Andernfalls werden die Referenzzellen für den Tabellenarraybereich in die nächste Zelle verschoben Referenz.
  • Der im Suchwert eingegebene Wert sollte nicht kleiner sein als der kleinste Wert in der ersten Spalte des Tabellenarrays. Andernfalls gibt die Funktion den Fehlerwert "# N / A" zurück.
  • Denken Sie immer daran, das Tabellenarray in aufsteigender Reihenfolge zu sortieren, bevor Sie im letzten Argument eine ungefähre Übereinstimmung „TRUE“ oder „1“ anwenden.
  • Die Übereinstimmungsfunktion gibt nur die Position des Werts im Array der vlookup-Tabelle zurück und gibt den Wert nicht zurück.
  • Wenn die Übereinstimmungsfunktion die Position des Suchwerts im Tabellenarray nicht identifizieren kann, gibt die Formel im Fehlerwert "# N / A" zurück.
  • Bei Vlookup- und Match-Funktionen wird die Groß- und Kleinschreibung nicht berücksichtigt, wenn der Lookup-Wert mit dem übereinstimmenden Textwert im Tabellenarray abgeglichen wird.