Kurs
Wie kombiniere ich VLOOKUP() mit IF() in Excel?
Wenn du große Datensätze in Excel verwaltest, weißt du, wie schwierig es ist, sie effizient zu organisieren und zu analysieren. Aber ich habe eine Lösung für dich: Du kannst die Funktion VLOOKUP()
innerhalb einer IF()
Anweisung verschachteln, um dynamische Lookups basierend auf bestimmten Bedingungen zu erstellen. Auf diese Weise kannst du verschiedene Tabellen auf der Grundlage einer Bedingung nachschlagen und auch Fehler besser behandeln.
Um genau zu sein, findet VLOOKUP()
bestimmte Datenpunkte in einer Tabelle, und mit den IF()
Anweisungen kannst du auf der Grundlage dieser Daten bedingte Entscheidungen treffen. Beide Funktionen sind, wie wir wissen, extrem wichtig. In diesem Artikel fügen wir ein weiteres Werkzeug zum Werkzeugkasten hinzu: Du erfährst, wie du diese beiden Funktionen zusammen nutzen kannst, um bedingte Suchvorgänge durchzuführen, und wir veranschaulichen dies an praktischen Beispielen.
Die schnelle Antwort: Wie kombiniere ich VLOOKUP() und IF()?
Um eine bedingte Suche zu erstellen, beginnst du mit einer IF()
Anweisung und verwendest darin VLOOKUP()
, um je nach Bedingung unterschiedliche Ergebnisse zurückzugeben. Wir können den folgenden Code verwenden, um zu prüfen, ob ein Produkt anhand seiner Menge auf Lager ist. Folge einfach diesen Schritten:
-
Beginne mit der Funktion
IF()
:=IF()
-
Verwende innerhalb der Funktion
IF()
VLOOKUP()
.
=IF(VLOOKUP(C2, $A$2:$B$6, 2, FALSE) > 0, "In Stock", "Out of Stock")
Die VLOOKUP()- und IF()-Anweisungen in Excel verstehen
VLOOKUP()
hilft dir, Daten in einer Tabelle zu finden, während du mit den IF()
Anweisungen Entscheidungen auf der Grundlage dieser Daten treffen kannst. Gemeinsam helfen sie bei der Analyse von Informationen in Tabellenkalkulationen. Schauen wir uns jede Funktion einzeln an und führen sie dann zusammen.
Was ist VLOOKUP() in Excel?
VLOOKUP()
sucht nach einem bestimmten Wert in der ersten Spalte eines Bereichs und gibt einen Wert aus einer anderen Spalte in derselben Zeile zurück. Hier ist die Syntax, damit du die Formel VLOOKUP()
für deine großen Datensätze verwenden kannst.
=VLOOKUP(search_key, range, index, is_sorted)
Lass uns diese Syntax aufschlüsseln und verstehen:
-
Search_key
ist der Wert, nach dem du suchen willst. Dies kann eine Zahl, ein Text oder ein Verweis auf eine Zelle sein, die den Suchwert enthält. -
Range
definiert den Bereich der Zellen, der die Daten enthält. Die erste Spalte in diesem Bereich sollte diesearch_key
enthalten. -
Index
ist die Spaltennummer in dem Bereich, aus dem du den Wert abrufen möchtest. Die erste Spalte ist die 1, die zweite die 2, und so weiter. -
Is_sorted
ist ein logischer Wert (TRUE
oderFALSE
). Du kannstTRUE
(oder1
) für Zahlen undFALSE
(oder0
) für Text verwenden. WennTRUE
verwendet wird, gehtVLOOKUP()
davon aus, dass die erste Spalte des Bereichs in aufsteigender Reihenfolge sortiert ist und gibt die nächstliegende Übereinstimmung zurück. WennFALSE
, suchtVLOOKUP()
nach einer genauen Übereinstimmung. Wenn das Argument nicht angegeben wird, istTRUE
der Standard.
Lass uns das anhand eines Beispiels verstehen. Hier habe ich eine Liste von Produkten mit ihren IDs und Preisen. Ich möchte die Preise für bestimmte Produkte anhand ihres Namens finden.
Liste der Produkte mit ihrer ID und ihren Preisen. Quelle: Bild vom Autor.
Zunächst suche ich nach dem Preis für ein bestimmtes Tablet. Dazu gebe ich einen search_key
Wert ein (in meinem Fall Tablet
), um den Preis in einer beliebigen Zelle zu finden.
Gib den Wert ein, um den Preis dafür zu finden. Quelle: Bild vom Autor.
Dann wähle ich eine Zelle aus und gebe =VLOOKUP()
ein.
Tippe die VLOOKUP()-Formel ein. Quelle: Bild vom Autor.
Als nächstes wähle ich die Zelle aus, in die search_key
eingegeben wurde.
Auswählen der Spalte, in der sich das Produkt befindet. Quelle: Bild vom Autor.
Dann wähle ich den Bereich der Tabelle aus.
Auswählen des Bereichs der Tabelle. Quelle: Bild vom Autor.
Von links zählend gebe ich die Nummer einer Spalte ein, aus der ich die Daten abrufen möchte. Hier möchte ich den Preis wissen, also gebe ich 2
ein.
Auswählen des Index der Spalten. Quelle: Bild vom Autor.
Dann gebe ich FALSE
ein, um den genauen Treffer zu erhalten.
Tippe FALSE für die genaue Übereinstimmung. Quelle: Bild vom Autor.
Nachdem ich alle Werte eingegeben habe, drücke ich Enter:
Mit VLOOKUP() wird der Preis des Produkts ermittelt. Quelle: Bild vom Autor.
Wie du auf dem Bild sehen kannst, ruft VLOOKUP()
den Preis erfolgreich ab.
Was ist IF() in Excel?
IF()
Anweisungen vergleichen die Werte und prüfen sie gegen die angegebene Bedingung. Hier ist die Syntax:
=IF(logical_test, [value_if_true], [value_if_false])
Schauen wir uns die wichtigsten Teile an, um sie zu verstehen:
-
Logical_test
ist der Wert oder Ausdruck, den du alsTRUE
oderFALSE
auswerten möchtest. Das ist die Bedingung, die du überprüfen willst. -
Value_if_true
gibt den Wert zurück, wennlogical_test
TRUE
ist. -
Value_if_false
gibt den Wert zurück, wennlogical_test
FALSE
ist.
Nehmen wir ein Beispiel. Hier möchte ich den Schülern auf der Grundlage ihrer Noten Bemerkungen machen. Also bereite ich das Blatt mit zwei Spalten vor: SCHÜLER und GRADEN.
Ein Blatt, das die Liste der Schüler mit ihren Noten enthält. Quelle: Bild vom Autor.
Ich wähle eine Zelle aus und gebe =IF()
ein. Mein Ziel ist es, zu prüfen, ob die Gesamtzahlen größer als 50
sind und dann Excellent
zu drucken, oder, wenn die Note kleiner als 50
ist, drucke ich Bad
. Nachdem ich die Bedingungen angegeben habe, drücke ich Enter, um die Ergebnisse zu erhalten.
Zuweisung von Bemerkungen an alle Schüler mithilfe der IF-Anweisung. Quelle: Bild vom Autor.
Dann kopiere ich die Formel in die letzte gefüllte Zelle, indem ich sie ziehe. Du kannst die Ergebnisse sehen. Ich habe allen Schülern mit nur einer einzigen Formel Bemerkungen zugewiesen.
Wege zur Kombination von VLOOKUP() mit IF() in Excel
Schauen wir uns praktische Beispiele an, um zu verstehen, wie VLOOKUP()
mit IF()
funktioniert.
Bedingte Nachschlagewerke
Um eine bedingte Suche zu erstellen, beginnst du, wie gesagt, mit einer IF()
Anweisung und verwendest darin VLOOKUP()
, um je nach Bedingung unterschiedliche Ergebnisse zurückzugeben.
-
Beginne mit der Funktion
IF()
:=IF()
. -
Verwende innerhalb der Funktion
IF()
VLOOKUP()
.
Lass uns ein neues Beispiel ausprobieren: Hier habe ich eine Liste von Produktbestellungen mit den entsprechenden Bestellzeiten. Ich möchte sehen, ob ein bestimmtes Produkt vor 12:00 Uhr bestellt worden ist.
Eine Tabelle, die die Produktliste zusammen mit der Bestell-ID und der Uhrzeit enthält. Quelle: Bild vom Autor.
Zuerst wähle ich eine Spalte aus und gebe die folgende Formel ein:
=IF(VLOOKUP(A3, A2:C5,3, FALSE) < TIME(12, 0, 0), "Ordered Before Noon", "Ordered After Noon")
Hier sucht die Funktion VLOOKUP()
in Spalte A nach Banana
und gibt die entsprechende Bestellzeit aus Spalte B zurück.
Die Anweisung IF()
prüft, ob die Bestellzeit kleiner ist als 12
. Wenn ja, gibt sie Ordered Before Noon
zurück. Andernfalls gibt sie Ordered After Noon
zurück.
Berechnung der Lieferzeit durch Kombination der IF()- und VLOOKUP()-Formel. Quelle: Bild vom Autor.
Du kannst sehen, wie ich meine gewünschten Produktlieferungen mit Hilfe von bedingten Suchvorgängen leicht verfolgen kann.
Fehlerbehandlung
Um Fehler zu behandeln, beginnst du mit einer IF()
Anweisung und verwendest ISNA()
mit VLOOKUP()
darin, um auf Fehler zu prüfen. Zum Beispiel, um eine benutzerdefinierte Meldung anzuzeigen, wenn ein Produkt nicht gefunden wird:
-
Beginne mit der Funktion
IF()
:=IF()
. -
Innerhalb der Funktion
IF()
verwendest duISNA()
mitVLOOKUP()
, um auf Fehler zu prüfen.
Lass uns das anhand eines Beispiels verdeutlichen. Angenommen, ich habe eine Tabelle mit Produktpreisen und möchte eine benutzerdefinierte Meldung anzeigen, wenn ein Produkt nicht gefunden wird.
Eine Tabelle mit einer Liste von Produkten und deren Preisen. Quelle: Bild vom Autor.
Dazu wähle ich eine Zelle aus und gebe die folgende Formel ein, um den Preis zu ermitteln:
=(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)
Dann kombiniere ich es mit ISNA()
und IF()
, um alle Fehler zu behandeln.
=IF(ISNA(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)), "Product Not Found", (VLOOKUP(B7, $A$2:$B$5, 2, FALSE)))
Fehlerbehandlung durch die Kombination von IF()- und ISNA()-Formeln. Quelle: Bild vom Autor.
Hier prüft ISNA()
, ob die Funktion VLOOKUP()
einen Fehler #N/A
zurückgibt, was passieren würde, wenn sie nicht verfügbar ist. Mit anderen Worten: Wenn ISNA()
den Wert TRUE
ergibt, gibt die Anweisung IF()
den Wert Product Not Found
zurück; andernfalls wird der Preis aus VLOOKUP()
zurückgegeben.
Dynamische Spaltenindizierung
Um den Spaltenindex für VLOOKUP()
dynamisch auszuwählen, beginne mit einer IF()
Anweisung und verwende VLOOKUP()
darin, um verschiedene Spalten basierend auf einer Bedingung auszuwählen. Zum Beispiel, um verschiedene Spalten anhand eines Schwellenwerts nachzuschlagen:
-
Beginne mit der Funktion
IF()
:=IF()
. -
In der Funktion
IF()
kannst du mitVLOOKUP()
den Schwellenwert überprüfen und die Spalte auswählen.
Hier habe ich eine Produkttabelle, in der Spalte A die Produktnamen, Spalte B die Produktpreise und Spalte C die Lagermenge enthält. Ich möchte den Produktpreis oder die Lagermenge abfragen, je nachdem, ob der Preis über oder unter einem bestimmten Schwellenwert liegt, z.B. 50
.
Eine Tabelle mit einer Liste von Produkten, ihren IDs und Preisen. Quelle: Bild vom Autor.
Ich wähle eine Zelle aus und gebe die folgende Formel ein:
=IF(VLOOKUP(B9, $B$9:$D$14, 2, FALSE) > 50, VLOOKUP(B9,$B$9:$D$14, 3, FALSE), VLOOKUP(B9,$B$9:$D$14, 2, FALSE))
Anwendung von IF mit verschachteltem VLOOKUP(). Quelle: Bild vom Autor.
So funktioniert die Formel:
-
(VLOOKUP(B9, $B$9:$D$14, 2, FALSE)
sucht das Produkt inB9
aus Spalte A und gibt den Preis aus Spalte C zurück. -
Die Anweisung
IF()
prüft, ob der Preis größer als$50
ist. -
Wenn ja, gibt unser Code die Bestandsmenge zurück:
VLOOKUP(B9,$B$9:$D$14, 3, FALSE)
. -
Wenn false, gibt unser Code die Produkt-ID zurück:
VLOOKUP(B9,$B$9:$D$14, 2, FALSE))
.
Wenn du mit einem sehr großen Datensatz arbeitest, kannst du die Formel kopieren, indem du sie auf die letzte gefüllte Zelle ziehst.
Ergebnisse anzeigen. Quelle: Bild vom Autor.
Und das war's. Wie du siehst, können wir mit einer kombinierten Formel die gewünschten Informationen auf der Grundlage bestimmter Bedingungen abrufen.
Fortgeschrittene Techniken mit VLOOKUP() und IF()
Nachdem du nun ein grundlegendes Verständnis für die Kombination von IF()
Anweisungen mit VLOOKUP()
hast, wollen wir einige fortgeschrittene Techniken anhand von Beispielen lernen, die ich selbst ausprobiert habe.
Mehrere Kriterien kombinieren
Wenn du Daten anhand mehrerer Kriterien suchst, kannst du mehrere VLOOKUP()
Funktionen in einer IF()
Anweisung kombinieren, um zu prüfen, ob alle Bedingungen erfüllt sind.
Hier habe ich eine Tabelle mit den Kaufdaten des Kunden und dem Mitgliedsstatus. Und ich möchte prüfen, ob ein Kunde für ein Treueprogramm in Frage kommt, wofür mindestens 500
als GESAMTEINKAUF ($) und Gold
als MITGLIEDSCHAFTSSTATUS erforderlich sind.
Eine Tabelle mit den Daten der Kunden. Quelle: Bild vom Autor.
Ich erstelle eine weitere Spalte mit dem Namen " Förderfähigkeit", in der die Kriterien für die Förderfähigkeit aufgeführt sind. Dann gebe ich die folgende Formel ein und drücke die Eingabetaste:
=IF(AND(VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500, VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold"), "Eligible", "Not Eligible")
Prüfe die Anspruchsvoraussetzungen der Kunden, indem du mehrere Kriterien kombinierst. Quelle: Bild vom Autor.
So funktioniert diese Formel:
-
VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500
prüft, ob die Gesamteinkäufe des Kunden mindestens$500
betragen. -
VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold")
prüft, ob Johns MitgliedsstatusGold
war. AND
die Bedingungen kombiniert, um sicherzustellen, dass beide wahr sind.-
IF()
Eligible
zurück, wenn beide Bedingungen erfüllt waren. Andernfalls gab sieNot Eligible
zurück.
Verwendung von VLOOKUP() mit IF() für Berechnungen
Du kannst VLOOKUP()
verwenden, um einen Wert zu finden und dann eine IF()
Anweisung anwenden, um Berechnungen auf der Grundlage dieses Wertes durchzuführen.
Hier bereite ich ein Blatt mit Produkten und ihren Preisen vor. Dann möchte ich einen Rabatt von 10% auf Produkte über $100 gewähren.
Eine Tabelle mit einer Liste von Produkten und deren Preisen. Quelle: Bild vom Autor.
Ich erstelle also eine weitere Spalte mit dem Namen DISCOUNT und schreibe die folgende Formel in diese Spalte, um den Rabatt anzuzeigen.
=IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9, VLOOKUP(A2, $A$2:$B$10, 2, FALSE))
Und so funktioniert diese Formel:
-
VLOOKUP(A2, $A$2:$B$10, 2, FALSE)
den Preis des Produkts abgerufen, der 56 beträgt. -
=IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, ..., ...)
überprüft, ob der Preis des Produkts größer als 100 ist. -
Da die Bedingung erfüllt ist, hat
VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9
einen Rabatt von 10% gewährt. -
(VLOOKUP(A2, $A$2:$B$10, 2, FALSE))
die tatsächlichen Preise der Produkte gedruckt, bei denen die Rabattbedingung nicht zutraf.
So kam ich auf das Endergebnis der Formel für das Produkt, die 135
lautete. Um die gewünschten Ergebnisse für alle Produkte zu erhalten, kannst du die Formel nach unten ziehen und sie in die letzte ausgefüllte Zelle kopieren.
Benutze VLOOKUP() mit IF, um die Rabatte für Produkte zu berechnen, deren Preis größer als 100 ist. Quelle: Bild vom Autor.
Umgang mit großen Datensätzen
Du kannst VLOOKUP()
auch mit der Funktion IF()
kombinieren, um den Datenabruf und die Fehlerbehandlung bei der Arbeit mit großen Datensätzen zu optimieren.
Hier ist unser letztes Beispiel: Ich habe ein Blatt mit Mitarbeiterinformationen und möchte die Abteilung eines Mitarbeiters abrufen und Fälle behandeln, in denen die Mitarbeiter-ID nicht existiert.
Eine Tabelle mit einer Liste von Mitarbeitern mit ihren IDs und Abteilungen. Quelle: Bild vom Autor.
Ich wähle also eine Zelle aus und gebe die folgende Formel ein:
=IFERROR(VLOOKUP(E3, $A$2:$C$18, 3, FALSE), "Not Found")
Nachdem ich auf Enter gedrückt habe, ziehe ich die Formel, um sie auch in andere Zellen zu kopieren.
Abrufen der Abteilung des Mitarbeiters über die ID und Fehlerbehandlung, wenn der Mitarbeiter nicht gefunden wird. Quelle: Bild vom Autor.
Und die Ergebnisse kannst du oben sehen. So funktioniert diese Formel:
-
Die
VLOOKUP(E3, $A$2:$C$18, 3, FALSE)
ruft die Abteilung des Mitarbeiters ab. -
IFERROR
behandelt die Fehler. Anstelle einer Fehlermeldung (#N/A
) wird eine freundliche und angepasste Meldung angezeigt.
Einfach gesagt: Du musst nicht mehr in Laken wühlen. Denn wenn du die Anweisungen VLOOKUP()
und IF()
kombinierst, kannst du in Excel selbst die größten Datensätze leicht bewältigen.
Schlussgedanken
Durch die Kombination von VLOOKUP()
mit IF()
kannst du genauere und fehlerresistentere Tabellenkalkulationen erstellen. Experimentiere unbedingt mit den Beispielen, die ich dir gezeigt habe, um zu sehen, wie diese Techniken die Datenverwaltung vereinfachen und deine Excel-Kenntnisse verbessern können.
Wenn du deine Fähigkeiten verbessern möchtest, schau dir unseren Kurs Einführung in Excel an und steigere dann auf den Lernpfad Excel Grundlagen, um die Grundlagen zu beherrschen. Wenn du mit diesen Funktionen vertraut bist, kannst du deine analytischen Fähigkeiten mit unserem Kurs Datenanalyse in Excel verbessern. Wenn du dich aber eher mit Finanzen beschäftigst, solltest du dir unseren Kurs Finanzmodellierung in Excel ansehen, um finanzielle Erkenntnisse zu integrieren.
Darüber hinaus hilft dir unser Kurs Datenvorbereitung in Excel dabei, deinen Datenbereinigungsprozess zu optimieren, und der Kurs Datenvisualisierung in Excel hilft dir, deine Daten auf überzeugende Weise zu präsentieren.
Ich bin ein Inhaltsstratege, der es liebt, komplexe Themen zu vereinfachen. Ich habe Unternehmen wie Splunk, Hackernoon und Tiiny Host geholfen, ansprechende und informative Inhalte für ihr Publikum zu erstellen.
Häufig gestellte Fragen
Kann VLOOKUP() mehrere Kriterien verarbeiten?
VLOOKUP()
allein kann nicht mit mehreren Kriterien umgehen, aber du kannst sie innerhalb einer IF()
Anweisung verwenden oder sie mit anderen Funktionen wie AND
kombinieren, um dies zu erreichen.
Was ist der Unterschied zwischen TRUE und FALSE in der Funktion VLOOKUP()?
TRUE
(oder weggelassen) bedeutet eine ungefähre Übereinstimmung, während FALSE
eine exakte Übereinstimmung für den Nachschlagewert angibt.
Welche Rolle spielt die AND-Funktion bei der Kombination von VLOOKUP() mit IF()?
AND
Funktion prüft mehrere Bedingungen innerhalb einer IF()
Anweisung, um komplexere Kriterien in VLOOKUP()
Operationen zu ermöglichen.
Lerne Excel mit DataCamp
Kurs
Datenaufbereitung in Excel
Lernpfad