Nachschlagetabellen in Excel sind benannte Tabellen, die mit der vlookup-Funktion verwendet werden, um Daten zu finden. Wenn wir eine große Datenmenge haben und nicht wissen, wo wir suchen sollen, können wir die Tabelle auswählen und ihr einen Namen geben und während wir das vlookup verwenden Funktion Anstatt die Referenz anzugeben, können wir den Namen der Tabelle als Referenz eingeben, um den Wert nachzuschlagen. Diese Tabelle wird in Excel als Nachschlagetabelle bezeichnet.
Wie erstelle ich eine Nachschlagetabelle in Excel?
Suchfunktionen sind Lebensretter in Excel. Basierend auf dem verfügbaren Wert oder dem Suchwert können wir die anderen damit verbundenen Daten in den verschiedenen Datentabellen abrufen. In Excel ist VLOOKUP die am häufigsten verwendete Suchfunktion.
In diesem Artikel werden einige wichtige Suchfunktionen in Excel sowie das Erstellen einer Nachschlagetabelle in Excel erläutert. Wichtige Suchfunktionen sind VLOOKUP & HLOOKUP, V steht für Vertical Lookup und H steht für Horizontal Lookup. Wir haben auch die Funktion LOOKUP, um nach den Daten in der Tabelle zu suchen.
Mit diesen Suchfunktionen können wir die anderen Informationen der verfügbaren Daten aus verschiedenen Arbeitsblättern sowie aus verschiedenen Arbeitsmappen abrufen.
Sie können diese LOOKUP-Tabellen-Excel-Vorlage hier herunterladen - LOOKUP-Tabellen-Excel-Vorlage erstellen# 1 - Erstellen Sie eine Nachschlagetabelle mit der VLOOKUP-Funktion
Wie ich bereits sagte, ist VLOOKUP die traditionelle Suchfunktion, die fast alle Benutzer regelmäßig verwenden. Wir zeigen Ihnen, wie Sie mit dieser Suchfunktion nach Werten suchen.
- Der Suchwert ist nichts anderes als der verfügbare Wert. Basierend auf diesem Wert versuchen wir, die Daten aus der anderen Tabelle abzurufen.
- Das Tabellenarray ist einfach die Haupttabelle, in der sich alle Informationen befinden.
- Col Index Num ist nichts anderes als die Spalte des Tabellenarrays , aus der die Daten stammen sollen. Wir müssen hier die Spaltennummer erwähnen.
- Range Lookup ist nichts anderes als die Frage, ob Sie nach einer genauen Übereinstimmung oder einer ungefähren Übereinstimmung suchen. Wenn Sie nach der genauen Übereinstimmung suchen, ist FALSE oder 0 das Argument. Wenn Sie nach der ungefähren Übereinstimmung suchen, ist TRUE oder 1 das Argument.
Beispiel für eine VLOOKUP-Funktion: Angenommen, unten sind die Daten aufgeführt, die Sie über Produktverkäufe und deren Verkaufsmenge haben.
Jetzt haben Sie in der Zelle D2 eine Produkt-ID und mit dieser Produkt-ID müssen Sie den Verkaufswert mit VLOOKUP abrufen.
Schritt 1: Wenden Sie die VLOOKUP-Funktion an und öffnen Sie zuerst die Formel.
Schritt 2: Das erste Argument ist der LOOKUP-Wert. Der Suchwert ist unser Basiswert oder verfügbarer Wert. Wählen Sie also die Zelle D2 als Referenz.
Schritt 3: Als nächstes folgt das Tabellenarray. Dies ist nichts anderes als unsere Haupttabelle, in der sich alle Daten befinden. Wählen Sie also das Tabellenarray als A2 bis B11.
Schritt 4: Drücken Sie nun die Funktionstaste F4, um eine absolute Excel-Referenz zu erhalten. Das Dollarsymbol wird in die ausgewählte Zelle eingefügt.
Schritt 5: Das nächste Argument ist die Spaltenindexnummer aus der ausgewählten Tabelle, aus welcher Spalte Sie tatsächlich nach den Daten suchen. In diesem Fall haben wir zwei Spalten ausgewählt und benötigen die Daten aus der zweiten Spalte. Geben Sie daher 2 als Argument an.
Schritt 6: Jetzt ist das letzte Argument die Bereichssuche, dh die Art der Suche. Da es sich um eine genaue Übereinstimmung handelt, wählen Sie FALSE oder geben Sie als Argument Null ein.
Schritt 7: Schließen Sie die Halterung und drücken Sie die Eingabetaste. Wir sollten den Verkaufswert für die Produkt-ID Prd 5 haben.
Schritt 8: Was ist, wenn wir die Verkaufsdaten für das Produkt wollen, wenn Prd6. Natürlich können wir direkt eintreten, aber dies ist nicht der richtige Ansatz. Vielmehr können wir die Dropdown-Liste in Excel erstellen und dem Benutzer die Auswahl aus der Dropdown-Liste ermöglichen. Drücken Sie ALT + A + V + V in der Zelle D2. Dies ist die Tastenkombination, mit der die Datenüberprüfung in Excel erstellt wird.
Schritt 9: Wählen Sie die Liste aus Zulassen: Dropdown.
Schritt 10: Wählen Sie in der QUELLE die Produkt-ID-Liste von A2 bis A11 aus.
Schritt 11: Klicken Sie auf OK. Wir haben jetzt alle Produktlisten in der Zelle D2.
# 2 - Verwenden Sie die LOOKUP-Funktion, um eine LOOKUP-Tabelle in Excel zu erstellen
Anstelle von VLOOKUP können wir alternativ auch die LOOKUP-Funktion in Excel verwenden. Schauen wir uns die Formel der LOOKUP-Funktion an.
- Der Suchwert ist der Basiswert oder der verfügbare Wert.
- Der Lookup-Vektor ist nichts anderes als die Lookup-Wertespalte in der Haupttabelle.
- Ergebnis Vektor ist nichts anderes als eine Spalte in der Haupttabelle.
Wenden wir die Formel an, um die Logik der LOOKUP-Funktion zu verstehen.
Schritt 1: Öffnen Sie jetzt die Suchfunktion.
Schritt 2: Der Suchwert ist die Produkt-ID. Wählen Sie daher die Zelle D2 aus.
Schritt 3: Der Suchvektor ist nichts anderes als die ProductId-Spalte in der Haupttabelle. Wählen Sie also A1 bis A11 als Bereich.
Schritt 4: Als nächstes wird der Ergebnisvektor angezeigt. Dies ist nichts anderes als die Spalte, aus der die Daten abgerufen werden sollen. In diesem Fall von B1 bis B11 möchten wir, dass die Daten abgerufen werden.
Schritt 5: Schließen Sie die Klammer und drücken Sie die Eingabetaste, um die Formel zu schließen. Wir sollten einen Verkaufswert für die ausgewählte Produkt-ID haben.
Schritt 6: Ändern Sie die Produkt-ID, um ein anderes Ergebnis anzuzeigen.
# 3 - Verwenden Sie die INDEX + MATCH-Funktion
Die VLOOKUP-Funktion kann die Daten von links nach rechts abrufen. Mithilfe der INDEX-Funktion und der MATCH-Formel in Excel können wir Daten von überall abrufen, um eine LOOKUP-Excel-Tabelle zu erstellen
Schritt 1: Öffnen Sie zuerst die INDEX-Formel Excel.
Schritt 2: Wählen Sie für das erste Argument die Ergebnisspalte in der Haupttabelle aus.
Schritt 3: Um die Zeilennummer zu erhalten, müssen wir die MATCH-Funktion anwenden. Siehe Abbildung unten für die MATCH-Funktion.
Schritt 4: Schließen Sie die Klammer und schließen Sie die Formel. Wir werden Ergebnisse haben.
Dinge, an die man sich erinnern sollte
- Die Suche sollte dieselbe sein wie in der Haupttabelle in Excel.
- VLOOKUP funktioniert von links nach rechts, nicht von rechts nach links.
- In der LOOKUP-Funktion müssen wir nur die Ergebnisspalte auswählen und die Spaltenindexnummer im Gegensatz zu VLOOKUP nicht erwähnen.
