Suchen, um einen Einzelwert mit VLOOKUP und HLOOKUP in Excel

Die beliebtesten der Lookup-Funktionen in Excel 2013 sind HLOOKUP (für horizontale Lookup) und VLOOKUP (für vertikale Lookup) Funktionen. Diese Funktionen werden auf der Lookup befindet Referenz Dropdown-Menü auf der Registerkarte Formeln der Band sowie in der Lookup Referenzkategorie im Dialogfeld Funktion einfügen.

Sie sind Teil einer starken Gruppe von Funktionen, die Werte, indem Sie sie nach oben in Datentabellen zurückgeben kann.

Die Funktion SVERWEIS sucht vertikal (von oben nach unten), um die Spalte ganz links einer Lookup-Tabelle, bis das Programm einen Wert lokalisiert das entspricht oder übersteigt die, die Sie suchen nach oben. Die HLOOKUP Funktion sucht horizontal (von links nach rechts) die oberste Zeile einer Lookup-Tabelle, bis er einen Wert lokalisiert das entspricht oder übersteigt die, die Sie suchen nach oben.

Die VLOOKUP-Funktion verwendet die folgende Syntax:

VLOOKUP (Suchkriterium, Matrix, Spaltenindex, [Bereich_Verweis])

Die Funktion WVERWEIS folgt die nahezu identische Syntax:

HLOOKUP (Suchkriterium, Matrix, Zeilenindex, [Bereich_Verweis])

In beiden Funktionen der Lookup-Wert Argument ist der Wert, den Sie in der Lookup-Tabelle nachschlagen möchten, und table_array ist der Zellbereich oder den Namen des Lookup-Tabelle, die sowohl den Wert nachschlagen und den damit verbundenen Mehrwert zurückzukehren enthält.

Das Spaltenindex Argument in der VLOOKUP Funktion ist die Nummer der Spalte, deren Werte im Vergleich zu den Lookup-Wert Argument in einer vertikalen Tabelle. Das Zeilenindex Argument in der HLOOKUP Funktion ist die Nummer der Zeile, deren Werte im Vergleich zu den Lookup-Wert in einem horizontalen Tisch.

Bei der Eingabe der Spaltenindex oder Zeilenindex Argumente in den Funktionen SVERWEIS und WVERWEIS, müssen Sie einen Wert größer als Null eingeben, die nicht die Gesamtzahl der Spalten oder Zeilen in der Lookup-Tabelle nicht übersteigt.

Die Wahl Bereich_Verweis Argument sowohl in der VLOOKUP und den HLOOKUP Funktionen ist die logische TRUE oder FALSE an, ob Sie Excel finden möchten, eine genaue oder ungefähre Übereinstimmung für die angibt, Lookup-Wert im table_array.

Wenn Sie die angeben TRUE oder weglassen Bereich_Verweis Argument in der VLOOKUP oder HLOOKUP Funktion, findet Excel eine ungefähre Übereinstimmung. Wenn Sie falsch wie die angeben Bereich_Verweis Argument, findet Excel nur genaue Treffer.

nur annähernd, zu finden, bezieht sich, wenn Sie numerische Eingaben (und nicht als Text) in der ersten Spalte oder Zeile der vertikalen oder horizontalen Lookup-Tabelle sind nach oben. Wenn Excel nicht eine genaue Übereinstimmung in dieser Lookup Spalte oder Zeile zu finden, sucht er den nächsthöchsten Wert, der nicht nicht überschreitet Lookup-Wert Argument und gibt dann den Wert in der Spalte oder Zeile definiert durch die Spaltenindex oder Zeilenindex Argumente.

Wenn die Funktionen SVERWEIS und WVERWEIS verwenden, der Text oder numerische Eingaben in der Lookup Spalte oder Zeile (dh die linke Spalte einer vertikalen Lookup-Tabelle oder die oberste Zeile eines horizontalen Lookup-Tabelle) muss eindeutig sein. Diese Einträge müssen auch in aufsteigend auftrags- angeordnet oder sortiert werden, die in alphabetischer Reihenfolge bei Texteingaben ist, und den niedrigsten zu höchsten Ordnung für numerische Eingaben.

Die Abbildung zeigt ein Beispiel für die Funktion SVERWEIS entweder unter Verwendung eines 15% oder 20% Spitze von einem Spitzentisch zurückzukehren, auf der Vorsteuer-Gesamt der Prüfung abhängig. Zelle F3 enthält die Funktion SVERWEIS:

= VLOOKUP (Pretax_Total, Tip_Table, IF (Tip_Percentage = 0.15,2,3)) 

Diese Formel gibt die Menge der auf der Spitze Prozentsatz in Zelle F1 und der Vorsteuer-Menge der Prüfung in Zelle F2 basierend Spitze.

Um diesen Tipp-Tabelle verwenden, geben Sie den Prozentsatz der Spitze (15% bzw. 20%) in Zelle F1 (benannt Tip_Percentage) und die Höhe des Schecks vor Steuern in Zelle F2 (benannt Pretax_Total). Excel sucht dann den Wert, die Sie in der Pretax_Total Zelle in der ersten Spalte der Lookup-Tabelle eingeben, die den Zellbereich A2 umfasst: C101 und Tip_Table benannt.

Excel bewegt sich dann nach unten in den Werten der ersten Spalte der Tip_Table, bis eine Übereinstimmung gefunden wird, woraufhin das Programm verwendet das Spaltenindex Argument in der VLOOKUP Funktion, die Spitze Menge aus dieser Zeile der Tabelle zurück zu Zelle F3 zu bestimmen.

Wenn Excel feststellt, dass der Wert in der Pretax_Total Zelle ($ 16,50 in diesem Beispiel) eingegeben genau in der ersten Spalte der Tip_Table einer der Werte nicht überein, setzt das Programm den Vergleich Bereich zu suchen, bis sie den ersten Wert stößt, überschreitet das Ergebnis vor Steuern insgesamt (17.00 in Zelle A19 in diesem Beispiel).

Excel bewegt sich dann in der Tabelle nach oben zu der vorherigen Zeile zurück und gibt den Wert in der Spalte, die die Spiele Spaltenindex Argument der Funktion SVERWEIS. (Dies ist, weil die optionale Bereich_Verweis Argument wurde von der Funktion weggelassen.)

Beachten Sie, dass die Spitze Tabellenbeispiel verwendet eine IF-Funktion die zu bestimmen Spaltenindex Argument für die Funktion SVERWEIS in Zelle F3.

Die IF-Funktion bestimmt die Spaltenzahl durch Anpassung der Prozentsatz in Tip_Percentage (Zelle F1) mit 0,15 eingegeben in der Spitze-Tabelle verwendet werden. Wenn sie übereinstimmen, gibt die Funktion 2 als Spaltenindex Argument und die VLOOKUP Funktion gibt einen Wert aus der zweiten Spalte (15% Spalte B) im Tip_Table Bereich.

Andernfalls gibt die IF-Funktion 3 als Spaltenindex Argument und die VLOOKUP Funktion gibt einen Wert aus der dritten Spalte (die 20% Spalte C) im Tip_Table Bereich.

Die folgende Abbildung zeigt ein Beispiel, das die Funktion WVERWEIS verwendet den Preis für jede Bäckerei Artikel in einer separaten Preis Lookup-Tabelle gespeichert nachschlagen und dann, dass der Preis auf den Preis / Doz Spalte der täglichen Verkaufsliste zurückzukehren. Zelle F3 enthält die ursprüngliche Formel mit der Funktion WVERWEIS, die dann nach unten Spalte F kopiert wird:

= HLOOKUP (item, Price_table, 2, FALSE)

In dieser Funktion WVERWEIS Punkt Bereich Name, der auf die Spalte Position im Bereich von C3 gegeben hat: C62 ist als die definierte Lookup-Wert Argument und der Name Preistabelle Zellbereich, auf den Zellbereich I1 gegeben hat: M2 ist die table_array Streit.

Das Zeilenindex Argument 2, weil Sie möchten, dass Excel die Preise in der zweiten Reihe der Preise Lookup-Tabelle zurück, und die optionale Bereich_Verweis Argument ist falsch, weil der Elementname in der Tagesliste Verkäufe müssen in der Preise Lookup-Tabelle genau den Artikelnamen übereinstimmen.

Indem der Preistabelle Bereich zur Eingabe der Preis pro Dutzend der HLOOKUP Funktion für jeden Backwaren Artikel verwenden in der Daily Verkaufsliste, machen Sie es eine sehr einfache Angelegenheit eine der Verkäufe in der Liste zu aktualisieren.

Alles, was Sie tun müssen, ist sein Preis / Doz Kosten in diesem Bereich zu ändern, und die HLOOKUP Funktion aktualisiert sofort den neuen Preis in der Liste Täglich Vertrieb, wo der Artikel verkauft wird.