Vlookup in Google -Blättern mit Formel Beispielen

Das Tutorial erklärt die Syntax der Google Sheets Vlookup-Funktion und zeigt, wie Vlookup-Formeln zur Lösung realer Aufgaben verwendet werden.

Bei der Arbeit mit miteinander verbundenen Daten besteht eine der häufigsten Herausforderungen darin, Informationen über mehrere Blätter hinweg zu finden. Sie führen häufig solche Aufgaben im Alltag aus, beispielsweise beim Scannen einer Flugplanplatine nach Ihrer Flugnummer, um die Abflugzeit und den Status zu erhalten. Google Sheets Vlookup funktioniert auf ähnliche Weise - schaut nach und ruft die übereinstimmenden Daten aus einer anderen Tabelle auf demselben Blatt oder aus einem anderen Blatt ab.

Eine weit verbreitete Meinung ist, dass Vlookup eine der schwierigsten und dunkelsten Funktionen ist. Aber das ist nicht wahr! Tatsächlich ist es einfach, Vlookup in Google Sheets zu machen, und in einem Moment werden Sie davon sicherstellen.

• Google Sheets Vlookup - Syntax und Nutzung
• So verwenden Sie Vlookup in Google Sheets - Formel Beispiele
  • Vlookup aus einem anderen Blatt
  • Vlookup mit Wildcard -Charakteren
  • Google Sheets Index Match -Formel für linke Vlookup
  • Fallsensitive Google Sheets Vlookup-Formel
• Vlookup in Google-Blättern ohne Formeln (Merge Sheets Add-On)
  • Video: So verwenden Sie zusammengeführte Blätter zu vlookup ohne Formeln
• Vlookup mehrere Matches auf einfache Weise
  • Video: So Vlookup mehrere Übereinstimmungen mit dem Add-On

Tipp. Für Microsoft Excel -Benutzer haben wir ein separates Excel Vlookup -Tutorial mit Formel -Beispielen.

Google Sheets Vlookup - Syntax und Nutzung

Die Vlookup -Funktion in Google Sheets ist so konzipiert, dass sie eine vertikale Suche nach einem Schlüsselwert (eindeutige Bezeichner) in der ersten Spalte in einem angegebenen Bereich durchführen und einen Wert in derselben Zeile aus einer anderen Spalte zurückgeben.

Die Syntax für die Google Sheets Vlookup -Funktion lautet wie folgt:

Vlookup (Search_key, Bereich, Index, [is_sorted])

Die ersten 3 Argumente sind erforderlich, die letzte ist optional:

Search_key - ist der Wert für die Suche (Suchwert oder eindeutige Kennung). Beispielsweise können Sie nach dem Wort "Apple", Nummer 10 oder dem Wert in Zelle A2 suchen.

Bereich - zwei oder mehr Datenspalten für die Suche. Die Google Sheets Vlookup -Funktion sucht immer in der ersten Spalte des Bereichs .

INDEX - Die Spaltennummer im Bereich , aus dem ein übereinstimmender Wert (Wert in derselben Zeile wie Search_key ) zurückgegeben werden sollte.

Die erste Spalte im Bereich hat Index 1. Wenn der Index weniger als 1 ist, gibt eine Vlookup -Formel den #Value zurück! Fehler. Wenn es größer ist als die Anzahl der Spalten im Bereich im Bereich , gibt Vlookup die #REF zurück! Fehler.

IS_SORTED - Zeigt an, ob die Suchspalte sortiert (wahr) oder nicht (falsch). In den meisten Fällen wird FALSE empfohlen.

• Wenn IS_Sorted wahr ist oder weggelassen ist (Standard), muss die erste Spalte des Reichweite in aufsteigender Reihenfolge sortiert werden, dh von a bis z oder von kleinstes bis größtes.

  In diesem Fall gibt eine Vlookup -Formel eine ungefähre Übereinstimmung zurück. Genauer gesagt sucht es zuerst nach exakten Übereinstimmung. Wenn keine genaue Übereinstimmung gefunden wird, sucht die Formel nach der nächsten Übereinstimmung , die weniger als oder gleich dem Search_Key ist. Wenn alle Werte in der Suchspalte größer sind als der Suchschlüssel, wird ein #n/ein Fehler zurückgegeben.

• Wenn IS_Sorted auf False eingestellt ist, ist keine Sortierung erforderlich. In diesem Fall sucht eine Vlookup -Formel nach genauer Übereinstimmungen . Wenn die Suchspalte 2 oder mehr Werte enthält, die genau gleich Search_key sind, wird der erste Wert zurückgegeben.

Auf den ersten Blick mag die Syntax etwas kompliziert erscheinen, aber das folgende Beispiel für Google Sheookup -Formel erleichtert das Verständnis.

Angenommen, Sie haben zwei Tabellen: Haupttabelle und Nachschlagtabelle, wie im folgenden Screenshot gezeigt. Die Tabellen verfügen über eine gemeinsame Spalte ( Auftrags -ID ), die ein eindeutiger Kennung ist. Sie wollen den Status jeder Bestellung aus der Nachschlagtabelle zum Haupttisch ziehen.

Wie verwenden Sie Google Sheets Vlookup, um die Aufgabe zu erfüllen? Lassen Sie uns zunächst die Argumente für unsere Vlookup -Formel definieren:

• Search_key - Bestell -ID (A3), der Wert, nach dem in der ersten Spalte der Nachschlagtabelle gesucht werden muss.
• Reichweite - Die Nachschlagtabelle ($ F $ 3: $ G $ 14). Bitte achten Sie darauf, dass wir den Bereich sperren , indem wir absolute Zellreferenzen verwenden, da wir vorhaben, die Formel in mehrere Zellen zu kopieren.
• Index - 2 Da die Statusspalte , aus der wir eine Übereinstimmung zurückgeben möchten, die 2 ^-ND -Spalte im Bereich ist.
• IS_SORTED - Falsch, weil unsere Suchspalte (f) nicht sortiert ist.

Wenn wir alle Argumente zusammenfügen, erhalten wir diese Formel:

=VLOOKUP(A3,$F$3:$G$14,2,false)

Geben Sie es in die erste Zelle (D3) der Haupttabelle ein, kopieren Sie die Spalte und Sie erhalten ein ähnliches Ergebnis:

Ist die Vlookup -Formel für Sie immer noch schwer zu verstehen? Dann schauen Sie es sich so an:

5 Dinge, die Sie über Google Sheets Vlookup wissen sollten

Wie Sie bereits verstanden haben, ist die Google Sheets Vlookup -Funktion eine Sache mit Nuancen. Wenn Sie sich an diese fünf einfachen Fakten erinnern, können Sie in Schwierigkeiten heraushalten und Ihnen helfen, die häufigsten Vlookup -Fehler zu vermeiden.

1. Google Sheets Vlookup kann nicht links schauen, es sucht immer in der ersten (linken) Spalte des Bereichs. Verwenden Sie eine linke Vlookup , um die Google Sheets -Index -Übereinstimmungsformel zu verwenden.
2. Vlookup in Google Sheets ist von Fall unempfindlich , was bedeutet, dass es nicht unterschriebene Kleinbuchstaben und Großbuchstaben unterscheiden. Verwenden Sie diese Formel für die sensible sensible Suche .
3. Wenn VLOOKUP falsche Ergebnisse zurückgibt, setzen Sie das IS_SORTED -Argument auf false, um genaue Übereinstimmungen zurückzugeben. Wenn dies nicht hilft, überprüfen Sie andere mögliche Gründe, warum Vlookup fehlschlägt.
4. Wenn IS_SORT auf True oder weggelassen wurde, denken Sie daran, die erste Spalte des Bereichs in aufsteigender Reihenfolge zu sortieren. In diesem Fall verwendet die Vlookup -Funktion einen schnelleren binären Suchalgorithmus, der nur bei sortierten Daten korrekt funktioniert.
5. Google Sheets Vlookup kann mit teilweise Übereinstimmungen basierend auf den Wildcard -Charakteren suchen: Das Fragezeichen (?) Und Sternchen (*). Weitere Informationen finden Sie in diesem Beispiel für Vlookup -Formel.

So verwenden Sie Vlookup in Google Sheets - Formel Beispiele

Nachdem Sie eine grundlegende Vorstellung davon haben, wie Google Sheets Vlookup funktioniert, ist es an der Zeit, sich selbst zu versuchen, ein paar Formeln selbst zu erstellen. Um die folgenden VLOOKUP -Beispiele zu vereinfachen, können Sie das Beispiel -Google -Sheet -Sample -Vlookup -Sheet öffnen.

Wie man aus einem anderen Blatt ausgeht

In realen Tabellenkalkulationen befinden sich der Haupttisch und der Nachschlagtisch häufig auf verschiedenen Blättern. Um Ihre Vlookup -Formel in einem anderen Blatt in derselben Tabelle zu verweisen, setzen Sie den Arbeitsblattnamen, gefolgt von einem Ausrufezeichen (!) Vor der Bereichsreferenz. Zum Beispiel:

=VLOOKUP(A2,Sheet4!$A$2:$B$13,2,false)

Die Formel sucht nach dem Wert in A2 im Bereich A2: A13 auf Blatt4 und gibt einen Übereinstimmungswert aus Spalte B (2 ^ND -Spalte in Bereich ) zurück.

Wenn der Blechname Leerzeichen oder nicht-alphabetische Zeichen enthält, sollten Sie ihn unbedingt in einzelnen Anführungszeichen einschließen. Zum Beispiel:

=VLOOKUP(A2,'Lookup table'!$A$2:$B$13,2,false)

Tipp. Anstatt manuell auf ein anderes Blatt einzugeben, können Sie ihn automatisch für Sie einfügen. Geben Sie dazu Ihre VLOOKUP -Formel ein und wechseln Sie, wenn es um das Bereichsargument geht, zum Lookup -Blatt und wählen Sie den Bereich mit einer Maus aus. Dadurch wird eine Reichweite der Formel hinzugefügt, und Sie müssen nur eine relative Referenz (Standard) in eine absolute Referenz ändern. Geben Sie dazu entweder das $ -Szeichen vor dem Spaltenbuchstaben und der Zeilennummer ein oder wählen Sie die Referenz aus und drücken Sie F4, um zwischen verschiedenen Referenztypen zu wechseln.

Google Sheets Vlookup mit Wildcard -Charakteren

In Situationen, in denen Sie nicht den gesamten Suchwert kennen (Search_key), aber einen Teil davon kennen, können Sie mit den folgenden Wildcard -Charakteren einen Lookup durchführen:

• Fragezeichen (?), Die zu einem einzelnen Charakter übereinstimmen, und
• Sternchen (*), um eine beliebige Abfolge von Zeichen zu entsprechen.

Angenommen, Sie möchten Informationen zu einer bestimmten Reihenfolge aus der folgenden Tabelle abrufen. Sie können sich nicht an die Bestell -ID erinnern, aber Sie erinnern sich, dass der erste Charakter "ein" ist. Sie verwenden also ein Sternchen (*), um den fehlenden Teil wie folgt auszufüllen:

=VLOOKUP("a*",$A$2:$C$13,2,false)

Besser noch, Sie können den bekannten Teil des Suchschlüssels in einer Zelle eingeben und diese Zelle mit "*" verkettet, um eine vielseitigere Vlookup -Formel zu erstellen:

Um den Artikel zu ziehen: =VLOOKUP($F$1&"*",$A$2:$C$13,2,false)

Um den Betrag zu ziehen: =VLOOKUP($F$1&"*",$A$2:$C$13,3,false)

Tipp. Wenn Sie nach einem tatsächlichen Fragezeichen oder einem Sternchenzeichen suchen müssen, stellen Sie eine Tilde (~) vor das Zeichen, z. "~*".

Google Sheets Index Match -Formel für linke Vlookup

Eine der bedeutendsten Einschränkungen der Vlookup -Funktion (sowohl in Excel als auch in Google -Blättern) ist, dass sie die linke nicht betrachten kann. Das heißt, wenn die Suchspalte nicht die erste Spalte in der Nachschlagtabelle ist, fällt Google Sheets Vlookup fehl. Verwenden Sie in solchen Situationen eine leistungsstärkere und dauerhaftere Index -Match -Formel:

INDEX ( return_range , match ( such_key , Lookup_range , 0))

Um beispielsweise den A3 -Wert (Search_key) in G3: G14 (Lookup_Range) nachzuschlagen und eine Übereinstimmung von F3: F14 (return_range) zurückzugeben, verwenden Sie diese Formel:

=INDEX($F$3:$F$14, MATCH (A3, $G$3:$G$14, 0))

Der folgende Screenshot zeigt diese Index -Match -Formel in Aktion:

Ein weiterer Vorteil der Index -Match -Formel im Vergleich zu Vlookup besteht darin, dass sie immun gegen strukturelle Änderungen ist, die Sie in den Blättern vornehmen, da sie die Rückgaberäule direkt verweist. Insbesondere das Einfügen oder Löschen einer Spalte in die Such-Tabelle durchbricht eine VLOOKUP-Formel, da die "hart codierte" Indexnummer ungültig wird, während die Index-Match-Formel sicher und fundiert bleibt.

Weitere Informationen zur Indexübereinstimmung finden Sie in der Indexübereinstimmung eine bessere Alternative zu VLOOKUP. Obwohl die oben genannten Tutorial -Ziele Excel, funktionieren die Indexübereinstimmung in Google Sheets genauso, mit Ausnahme verschiedener Namen der Argumente.

Fallempfindlicher Vlookup in Google Sheets

In Fällen, in denen der Textfall wichtig ist, übereinstimmen die Index in Kombination mit den wahren und genauen Funktionen, um eine Fall-sensitive Google Sheets Vlookup- Array-Formel zu erstellen:

ArrayFormula (index ( return_range , Match (true, exact ( Lookup_Range , Search_key )), 0))))

Unter der Annahme, dass der Suchschlüssel in Zelle A3 ist, ist der Suchbereich G3: G14 und der Rückkehrbereich F3: F14, die Formel lautet wie folgt:

=ArrayFormula(INDEX($F$3:$F$14, MATCH (TRUE,EXACT($G$3:$G$14, A3),0)))

Wie im Screenshot unten gezeigt, hat die Formel kein Problem mit der Unterscheidung zwischen Groß- und Kleinbuchstaben wie A-1001 und A-1001:

Tipp. Durch das Drücken der Strg -Schalteingabeteilung beim Bearbeiten einer Formel fügt die ArrayFormula -Funktion am Anfang der Formel automatisch ein.

Vlookup -Formeln sind die häufigsten, aber nicht die einzige Möglichkeit, in Google -Blättern nachzublicken. Der nächste und der letzte Abschnitt dieses Tutorials zeigt eine Alternative.

Merge Sheets: Formel-freie Alternative für Google Sheets Vlookup

Wenn Sie nach einer visuell formelfreien Möglichkeit suchen, um Google Spreadsheet Vlookup durchzuführen, sollten Sie das Merge-Sheets-Add-On verwenden. Sie können es kostenlos aus dem Google Sheets Add-Ons Store erhalten:

Sobald das Add-On zu Ihren Google-Blättern hinzugefügt wird, finden Sie es unter der Registerkarte Erweiterungen :

Mit dem Add-On für Merge-Blätter sind Sie bereit, ihm einen Feldtest durchzuführen. Die Quelldaten sind Ihnen bereits bekannt: Wir werden Informationen aus der Statusspalte basierend auf der Bestell -ID ziehen:

1. Wählen Sie jede Zelle mit Daten im Hauptblatt aus und klicken Sie auf Erweiterungen > SCHLACHTE > START .

   In den meisten Fällen nimmt das Add-On die gesamte Tabelle automatisch für Sie auf. Wenn dies nicht der Fall ist, klicken Sie entweder auf die Schaltfläche automatisch ausgewählt oder wählen Sie den Bereich in Ihrem Hauptblatt manuell aus und klicken Sie dann auf Weiter :

   

2. Wählen Sie den Bereich im Suchblatt aus. Der Bereich muss nicht unbedingt die gleiche Größe wie der Bereich im Hauptblatt haben. In diesem Beispiel enthält die Nachschlagtabelle 2 mehr Zeilen als die Haupttabelle.

   

3. Wählen Sie eine oder mehrere Schlüsselspalten (eindeutige Kennungen) aus, um sie zu vergleichen. Da wir die Blätter mit der Bestell -ID vergleichen, wählen wir nur diese Spalte aus:

   

4. Wählen Sie unter Suchspalten die Spalten (n) in dem Suchblatt aus, aus dem Sie Daten abrufen möchten. Wählen Sie unter den Hauptspalten die entsprechenden Spalten im Hauptblatt aus, in die Sie die Daten kopieren möchten.

   In diesem Beispiel ziehen wir Informationen aus der Statusspalte im Suchblatt in die Statusspalte auf dem Hauptblatt:

   

5. Wählen Sie optional eine oder mehrere zusätzliche Aktionen aus. Meistens möchten Sie nicht übereinstimmende Zeilen zum Ende der Haupttabelle hinzufügen , dh die Zeilen kopieren, die nur in der Nachschlagtabelle bis zum Ende der Haupttabelle vorhanden sind. Und aktualisieren Sie Ihre Haupttabelle genau dort, wo es sich befindet:

   

Klicken Sie auf Merge , lassen Sie die Merge-Blätter einen Moment für die Verarbeitung zu. Sie können loslegen!

Video: So verwenden Sie zusammengeführte Blätter zu vlookup ohne Formeln

Vlookup mehrere Matches auf einfache Weise!

Filter & Extract -Daten ist ein weiteres Google Sheets -Tool für die erweiterte Suche. Das Add-On kann alle Übereinstimmungen zurückgeben, nicht nur die erste wie die vlookup-Funktion. Darüber hinaus kann es mehrere Bedingungen bewerten, in eine beliebige Richtung nachschlagen und alle oder die angegebene Anzahl von Übereinstimmungen als Werte oder Formeln zurückgeben.

Wenn wir uns daran erinnern, dass ein Bild mehr als tausend Worte wert ist, sehen wir uns an, wie das Add-On bei realen Daten funktioniert. Angenommen, einige Bestellungen in unserer Beispieltabelle enthalten mehrere Elemente, und Sie möchten alle Elemente einer bestimmten Bestellung abrufen. Eine Vlookup -Formel kann dies nicht tun, während eine leistungsfähigere Abfragefunktion kann. Das Problem ist, dass diese Funktion die Kenntnis der Abfragesprache oder zumindest SQL -Syntax erfordert. Haben Sie keine Lust, Tage zu studieren? Installieren Sie den Filter und extrahieren Sie Daten zu Daten und erhalten Sie in Sekunden einen makellosen Formel!

Klicken Sie in Ihrem Google -Blatt auf Erweiterungen > Filter und extrahieren Sie Daten > Starten und definieren Sie die Suchkriterien:

1. Wählen Sie den Bereich mit Ihren Daten aus (A1: D15).
2. Geben Sie an, wie viele Übereinstimmungen zurückkehren sollen (alle in unserem Fall).
3. Wählen Sie aus, welche Spalten die Daten aus ( Element , Betrag und Status ) zurückgeben.
4. Setzen Sie eine oder mehrere Bedingungen. Wir möchten die Informationen zur Auftragsnummer in F2 ziehen, daher konfigurieren wir nur eine Bedingung: Bestell -ID = F2.
5. Wählen Sie die obere linke Zelle für das Ergebnis.
6. Klicken Sie auf Vorschau -Ergebnis , um sicherzustellen, dass Sie genau das erhalten, wonach Sie suchen.
7. Wenn alles gut ist, klicken Sie auf entweder Formel einfügen oder einfügen .

In diesem Beispiel haben wir uns für die Rückkehr von Übereinstimmungen als Formeln entschieden. So können Sie jetzt eine beliebige Bestellnummer in F2 eingeben, und die im folgende Screenshot angezeigte Formel wird automatisch neu berechnet:

Um mehr über das Add-On zu erfahren, besuchen Sie die Filter- und Extrahieren von Daten-Homepage oder erhalten Sie es jetzt vom Google Workspace Marketplace:

Video: So Vlookup mehrere Übereinstimmungen mit dem Add-On

So können Sie Google Sheets suchen. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche in unserem Blog zu sehen!

Tabelle mit Formel Beispielen

Beispiel Vlookup -Tabelle

Das obige ist der detaillierte Inhalt vonVlookup in Google -Blättern mit Formel Beispielen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!