Mussten Sie jemals einen Wert aus einem Bereich basierend auf einem entsprechenden Wert in Ihrer Liste abrufen? Beispielsweise müssen Sie den Preis eines Produkts basierend auf der ID des Produkts dynamisch abrufen.
Wenn Ihr Datensatz die Produkt-ID enthält, können Sie eine Formel verwenden, um den Preis abzurufen.
Wenn diese Formel als Spaltenformel festgelegt ist, zeigt jede Zeile (und alle neu hinzugefügten Zeilen) den entsprechenden Preis aus dem Produktdatensatz an.
Unterschiedliche Ansätze für diese Formel
Es gibt drei Methoden, mit denen Sie Daten basierend auf einem entsprechenden Suchwert aus einem Bereich abrufen können:
- VLOOKUP
- INDEX(MATCH())
- INDEX(COLLECT())
Wir schauen uns jede dieser Formeln genauer an und besprechen die Vor- und Nachteile jeder Lösung.
VLOOKUP
Eine VLOOKUP-Formel sucht nach einem Wert und gibt einen Wert in derselben Zeile, aber aus einer anderen (definierten) Spalte zurück. Das Format für eine VLOOKUP-Formel finden Sie nachfolgend:
=VLOOKUP([Suchwert], [durchsuchter Datensatz], [Spaltennummer im Datensatz], [„false“ oder „true“ basierend auf Anforderungen für exakte Übereinstimmung])
Um den Preis im obigen Beispiel mithilfe von VLOOKUP abzurufen, würde Ihre Formel wie folgt aussehen:
=VLOOKUP([Associated Product ID]@row, {Product Data | Product}, 4, false)
Der sheetübergreifende Referenzbereich {Product Data | Product} sieht wie folgt aus:
Und die Formel gibt die Werte in der Preisspalte „Price“ wie folgt aus:
TIPP: Umschließen Sie die Formel mit einer IFERROR-Formel für den Fall, dass kein übereinstimmender Wert im durchsuchten Datensatz gefunden wird. In diesem Beispiel würde die Formel wie folgt aussehen:
=IFERROR(VLOOKUP([Associated Product ID]@row, {Product Data | Product}, 4, false), "No Match Found")
Vorteile:
- Einfache/schnellste Formel
Nachteile:
- Der gesuchte Wert muss sich in der ersten Spalte des durchsuchten Datensatzes befinden.
- Werte links neben der Spalte für den Suchwert können nicht abgerufen werden.
- Formel funktioniert nicht, wenn zwischen dem Suchwert und der eingezogenen Spalte eine neue Spalte hinzugefügt oder eine Spalte entfernt wird oder wenn Spalten neu angeordnet werden.
INDEX(MATCH())
Eine INDEX(MATCH())-Formel durchsucht einen Bereich und erfasst den Wert, der den angegebenen Kriterien entspricht. Das Format für eine INDEX(MATCH())-Formel finden Sie nachfolgend:
=INDEX([Bereich mit auszugebendem Wert], MATCH([Suchwert], [Bereich mit gesuchtem Wert], [0, 1 oder -1 je nach Suchtyp]))
Um den Preis im obigen Beispiel mithilfe von INDEX(MATCH()) abzurufen, würde Ihre Formel wie folgt aussehen:
=IFERROR(INDEX({Product Data | Price}, MATCH([Associated Product ID]@row, {Product Data | Product ID}, 0)), "No Match Found")
Der sheetübergreifende Referenzbereich {Product Data | Price} sieht wie folgt aus:
Der sheetübergreifende Referenzbereich {Product Data | Product ID} sieht wie folgt aus:
Wie oben erwähnt, haben wir auch unsere INDEX(MATCH())-Funktion mit IFERROR versehen, um „No Match Found“ (keine Treffer) anzuzeigen, wenn für die Zeile keine übereinstimmende Produkt-ID gefunden wird, sodass die Formel die Werte in der Preisspalte wie folgt zurückgibt:
Vorteile:
- Ermöglicht Änderungen der Spaltenreihenfolge oder das Löschen unbenutzter Spalten ohne Unterbrechung
- Kann Werte aus Spalten links oder rechts vom Suchwertebereich abrufen
- Schneller für größere Datensätze
- INDEX(MATCH(),MATCH()) für den dynamischen Abgleich von Spalten und Zeilen verwendbar
- Gesamtzahl der referenzierten Zellen in der Regel niedriger, damit Sie die Gesamtgrenze von 100.000 Zellen, auf die in sheetübergreifenden Referenzen verwiesen werden kann, nicht überschreiten
Nachteile:
- Erfordert mehr als eine sheetübergreifende Referenz für Fälle, in denen sich Referenzdaten in einem separaten Sheet befinden
- Auf ein einzelnes Übereinstimmungskriterium beschränkt
INDEX(COLLECT())
Eine INDEX(COLLECT())-Formel durchsucht einen Bereich und erfasst den Wert, der einem oder mehreren angegebenen Kriterien entspricht. Das Format für eine INDEX(COLLECT())-Formel finden Sie nachfolgend:
=INDEX(COLLECT([Bereich mit auszugebendem Wert], [Bereich mit Kriterium], [Kriterium], [Bereich 2 mit Kriterium], [Kriterium] usw.), [1 für auszugebenden Zeilenindex])
Um den Preis im obigen Beispiel mithilfe von INDEX(COLLECT()) abzurufen, würde Ihre Formel wie folgt aussehen:
=IFERROR(INDEX(COLLECT({Product Data | Price}, {Product Data | Product ID}, [Associated Product ID]@row), 1) "No Match Found")
Der sheetübergreifende Referenzbereich {Product Data | Price} sieht wie folgt aus:
Der sheetübergreifende Referenzbereich {Product Data | Product ID} sieht wie folgt aus:
Wie oben erwähnt, haben wir auch unsere INDEX(COLLECT())-Funktion mit IFERROR versehen, um „No Match Found“ (keine Treffer) anzuzeigen, wenn für die Zeile keine übereinstimmende Produkt-ID gefunden wird, sodass die Formel die Werte in der Preisspalte wie folgt zurückgibt:
Vorteile:
- Ermöglicht Änderungen der Spaltenreihenfolge oder das Löschen unbenutzter Spalten ohne Unterbrechung
- Kann Werte aus Spalten links oder rechts vom Suchwertebereich abrufen
- Normalerweise schneller als VLOOKUP, aber möglicherweise langsamer als INDEX/MATCH
- Ermöglicht die Verwendung mehrerer Kriterien innerhalb der COLLECT-Formel, um Übereinstimmungen in mehreren Spalten zu finden oder komplexere Kriterien zu erstellen
- Ermöglicht es Ihnen, anstelle der ersten Übereinstimmung die zweite, dritte usw. Übereinstimmung anzugeben, indem Sie die „1“ am Ende der Formel ersetzen
Nachteile:
- Erfordert mehr als eine sheetübergreifende Referenz für Fälle, in denen sich Referenzdaten in einem separaten Sheet befinden
- Kann langsamer als INDEX/MATCH sein, insbesondere wenn mehrere Kriterien verwendet werden
Sie benötigen weitere Hilfe?
In den Vorlagen im Formel-Handbuch finden Sie weitere Support-Ressourcen und mehr als 100 Formeln, inklusive eines Glossars aller Funktionen, mit dem Sie die Arbeit in Echtzeit üben können, sowie Beispiele für häufig verwendete und erweiterte Formeln.
In der Smartsheet-Community können Sie Beispiele dafür finden, wie andere Smartsheet-Kunden diese Funktion verwenden oder eine Frage zu Ihrem spezifischen Anwendungsfall stellen.
Die Community fragen