Excel HLOOKUP-Funktion mit Formelbeispielen (2024)

Wie Sie wahrscheinlich wissen, verfügt Microsoft Excel über drei Funktionen zum Nachschlagen eines Wertes - LOOKUP, VLOOKUP und HLOOKUP - und diese scheinen die Benutzer am meisten zu verwirren. In diesem Lernprogramm werden wir uns auf die Besonderheiten der Excel-Funktion HLOOKUP konzentrieren und einige Formelbeispiele besprechen, die Ihnen helfen werden, diese Funktion in Excel so effizient wie möglich zu nutzen.

Was ist HLOOKUP in Excel?

Die Excel-Funktion HLOOKUP ist gedacht für Horizontales Nachschlagen Genauer gesagt sucht es nach einem bestimmten Wert in der ersten Zeile der Tabelle und gibt einen anderen Wert in derselben Spalte aus einer von Ihnen angegebenen Zeile zurück.

Die Funktion HLOOKUP ist in allen Versionen von Microsoft Excel 2016, Excel 2013, Excel 2010, Excel 2007 und niedriger verfügbar.

Excel HLOOKUP-Syntax und Verwendung

Die HLOOKUP-Funktion in Excel hat die folgenden Argumente:

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• Nachschlagen_Wert (erforderlich) - der zu suchende Wert, der ein Zellbezug, ein numerischer Wert oder eine Textzeichenfolge sein kann.
• Tabelle_Array (erforderlich) - zwei oder mehr Datenzeilen, in denen der Nachschlagewert gesucht wird. Es kann sich um einen regulären Bereich, einen benannten Bereich oder eine Tabelle handeln. Nachschlagewerte sollten sich immer im Bereich erste Zeile von table_array .
• Zeile_index_num (erforderlich) - die Zeilennummer in table_array, aus der der Wert zurückgegeben werden soll. Um zum Beispiel den passenden Wert aus der zweiten Zeile zurückzugeben, setzen Sie row_index_num auf 2 usw.
• Bereich_lookup (optional) - ein logischer (boolescher) Wert, der HLOOKUP anweist, mit genauer oder ungefährer Übereinstimmung zu suchen.

  Wenn TRUE oder ausgelassen, wird ein ungefähre Das heißt, wenn keine exakte Übereinstimmung gefunden wird, führt Ihre Hlookup-Formel eine nicht-exakte Übereinstimmung durch und gibt den nächstgrößeren Wert zurück, der kleiner als lookup_value ist.

  Wenn FALSE, wird nur ein genaue Übereinstimmung Wenn kein Wert in einer angegebenen Zeile genau mit dem Nachschlagewert übereinstimmt, gibt HLOOKUP den Fehler #N/A aus.

Um das Verständnis zu erleichtern, können Sie die HLOOKUP-Syntax von Excel übersetzen:

HLOOKUP( lookup_value , table_array , row_index_num , [range_lookup])

ins normale Englisch:

HLOOKUP( Suche nach diesem Wert , in dieser Tabelle , einen Wert aus dieser Zeile zurückgeben , [ungefähre oder exakte Übereinstimmung zurückgeben])

Um zu sehen, wie es in der Praxis funktioniert, lassen Sie uns ein einfaches Hlookup-Beispiel machen. Angenommen, Sie haben eine Tabelle mit einigen grundlegenden Informationen über die Planeten unseres Sonnensystems (siehe Screenshot unten). Was Sie wollen, ist eine Formel, die den Durchmesser des Planeten zurückgibt, dessen Name in Zelle B5 eingegeben wurde.

In unserer Hlookup-Formel werden wir die folgenden Argumente verwenden:

• Nachschlagen_Wert ist B5 - die Zelle, die den gesuchten Planetennamen enthält.
• Tabelle_Array ist B2:I3 - die Tabelle, in der die Formel den Wert nachschlagen wird.
• Zeile_index_num ist 2, weil Diameter die 2. Zeile in der Tabelle ist.
• Bereich_lookup Da die erste Zeile unserer Tabelle nicht von A bis Z sortiert ist, können wir nur mit exakter Übereinstimmung nachschlagen, was in diesem Beispiel gut funktioniert.

Setzt man nun die Argumente zusammen, erhält man die folgende Formel:

=VLOOKUP(40, A2:B15,2)

3 Dinge, die Sie über die Excel-Funktion HLOOKUP wissen sollten

Wann immer Sie eine horizontale Suche in Excel durchführen, sollten Sie sich an die folgenden Fakten erinnern:

1. Die Funktion HLOOKUP kann nur in der Datei oberste Zeile von table_array Wenn Sie an anderer Stelle nachschlagen müssen, sollten Sie eine Index-/Match-Formel verwenden.
2. HLOOKUP in Excel ist Groß- und Kleinschreibung wird nicht berücksichtigt Es unterscheidet nicht zwischen Groß- und Kleinschreibung.
3. Wenn bereich_lookup auf TRUE gesetzt oder weggelassen wird ( ungefähre übereinstimmen), werden die Werte in der ersten Zeile von table_array müssen sortiert werden in aufsteigende Reihenfolge (A-Z) von links nach rechts.

Was ist der Unterschied zwischen VLOOKUP und HLOOKUP in Excel?

Wie Sie bereits wissen, suchen beide Funktionen, VLOOKUP und HLOOKUP, nach einem Nachschlagewert. Der Unterschied besteht darin, wie die Suche durchgeführt wird. Wie Sie wahrscheinlich bemerkt haben, unterscheiden sich die Namen der Funktionen nur durch den ersten Buchstaben - "H" steht für horizontal und "V" für vertikal.

Daher verwenden Sie die Funktion VLOOKUP für die Suche nach vertikal Listen, wenn sich Ihre Nachschlagewerte in einer Spalte links von den zu suchenden Daten befinden.

Die Funktion HLOOKUP führt eine Horizontales Nachschlagen - sucht es in der obersten Zeile der Tabelle nach einem Nachschlagewert und gibt einen Wert zurück, der sich eine bestimmte Anzahl von Zeilen tiefer in derselben Spalte befindet.

Die folgende Abbildung veranschaulicht den Unterschied zwischen Vlookup- und Hlookup-Formeln in Excel:

Wie verwendet man HLOOKUP in Excel - Formelbeispiele

Nachdem Ihnen die Funktion HLOOKUP nun etwas vertrauter geworden ist, wollen wir zur Festigung des Wissens einige weitere Formelbeispiele besprechen.

Horizontale Suche mit ungefährer und exakter Übereinstimmung

Wie Sie bereits wissen, kann die Funktion HLOOKUP in Excel eine Suche mit exakter und nicht exakter Übereinstimmung durchführen, je nachdem, welcher Wert an die Funktion bereich_lookup Argument:

• TRUE oder weggelassen - Ungefähr Spiel
• FALSCH - genau Spiel

Bitte denken Sie daran, dass jede Hlookup-Formel nach einer exakten Übereinstimmung sucht, auch wenn wir "ungefähre Übereinstimmung" sagen. Wenn Sie das letzte Argument auf FALSE setzen, kann die Formel eine ungefähre Übereinstimmung zurückgeben (den nächstgelegenen Wert, der kleiner ist als der Lookup-Wert), wenn keine exakte Übereinstimmung gefunden wird; TRUE oder weggelassen ergibt in diesem Fall den Fehler #N/A.

Die folgenden HLOOKUP-Beispiele sollen dies verdeutlichen.

HLOOKUP mit ungefährer Übereinstimmung

Angenommen, Sie haben eine Liste von Planeten in Zeile 2 (B2:I2) und ihre Temperaturen in Zeile 1 (B1:I1). Sie möchten herausfinden, welcher Planet eine bestimmte Temperatur hat, die in Zelle B4 eingegeben ist.

Sie können sich nicht darauf verlassen, dass Ihre Benutzer die Nachschlagetemperatur genau kennen, daher ist es sinnvoll, eine nächstgelegene Übereinstimmung wenn ein exakter Wert nicht gefunden wird.

Um z. B. den Planeten zu ermitteln, dessen Durchschnittstemperatur bei etwa -340 °F liegt, verwenden Sie die folgende Formel ( bereich_lookup auf TRUE gesetzt oder wie in diesem Beispiel weggelassen):

=HLOOKUP(B4, B1:I2, 2)

Bitte denken Sie daran, dass für eine ungefähre Übereinstimmung die Werte in der obersten Zeile vom kleinsten zum größten Wert oder von A nach Z sortiert werden müssen, da Ihre Hlookup-Formel sonst möglicherweise ein falsches Ergebnis liefert.

Wie Sie in der Abbildung unten sehen können, liefert unsere Formel Uranus einer der kältesten Planeten des Sonnensystems mit einer durchschnittlichen Temperatur von -346 Grad Fahrenheit.

HLOOKUP mit exakter Übereinstimmung

Wenn Sie den Nachschlagewert genau kennen, können Sie den letzten Parameter von HLOOKUP auf FALSE setzen:

=HLOOKUP(B4, B1:I2, 2, FALSE)

Einerseits ist eine Hlookup-Suche mit annähernder Übereinstimmung benutzerfreundlicher, weil die Daten in der ersten Zeile nicht sortiert werden müssen, andererseits wird ein #N/A-Fehler zurückgegeben, wenn die genaue Übereinstimmung nicht gefunden wird.

Tipp: Um Ihre Benutzer nicht durch N/A-Fehler zu erschrecken, können Sie Ihre Hlookup-Formel in IFERROR einbetten und z. B. Ihre eigene Meldung anzeigen:

=IFERROR(HLOOKUP(B4, B1:I2, 2, FALSE), "Sorry, es wurde nichts gefunden")

HLOOKUP aus einem anderen Arbeitsblatt oder einer Arbeitsmappe ausführen

Im Allgemeinen bedeutet h-lookup aus einem anderen Blatt oder einer anderen Arbeitsmappe nichts anderes als die Bereitstellung externer Referenzen für Ihre HLOOKUP-Formel.

Um passende Daten aus einer anderes Arbeitsblatt geben Sie den Namen des Blattes gefolgt von einem Ausrufezeichen an, zum Beispiel:

=HLOOKUP(B$1, Durchmesser!$B$1:$I$2,2,FALSE)

Wenn der Name des Arbeitsblatts Folgendes enthält Räume oder nicht-alphabetische Zeichen den Namen in einfache Anführungszeichen setzen, etwa so:

=HLOOKUP(B$1, 'Planetendurchmesser'!$B$1:$I$2,2,FALSE)

Wenn Sie auf Folgendes verweisen eine andere Arbeitsmappe geben Sie den Namen der Arbeitsmappe in eckigen Klammern an:

=HLOOKUP(B$1, [Book1.xlsx]Durchmesser!$B$1:$I$2, 2, FALSE)

Wenn Sie Daten aus einer geschlossenen Arbeitsmappe abrufen, sollte der gesamte Pfad angegeben werden:

=HLOOKUP(B$1, 'D:\Reports\[Book1.xlsx]Diameters'!$B$1:$I$2, 2, FALSE)

Tipp: Anstatt die Namen der Arbeitsmappe und des Arbeitsblatts manuell in die Formel einzugeben, können Sie die Zellen in einem anderen Blatt auswählen, und Excel fügt automatisch einen externen Verweis zu Ihrer Formel hinzu.

Excel HLOOKUP mit teilweiser Übereinstimmung (Platzhalterzeichen)

Wie VLOOKUP erlaubt auch die HLOOKUP-Funktion von Excel die Verwendung der folgenden Platzhalterzeichen in der lookup_value Argument:

• Fragezeichen (?), um ein beliebiges einzelnes Zeichen zu finden
• Sternchen (*) für eine beliebige Folge von Zeichen

Platzhalter sind praktisch, wenn Sie Informationen aus einer Datenbank auf der Grundlage eines Textes abrufen möchten, der Teil des Inhalts der Nachschlagezelle ist.

Sie haben z. B. eine Liste mit Kundennamen in Zeile 1 und Auftrags-IDs in Zeile 2. Sie möchten die Auftrags-ID für einen bestimmten Kunden finden, können sich aber nicht mehr genau an den Kundennamen erinnern, obwohl er mit "ace" beginnt.

Angenommen, Ihre Daten befinden sich in den Zellen B1:I2 ( table_array) und die Bestellnummern stehen in Zeile 2 ( zeilen_index_nummer ), lautet die Formel wie folgt:

=HLOOKUP("ace*", B1:I2, 2, FALSE)

Um die Formel flexibler zu gestalten, können Sie den Nachschlagewert in eine spezielle Zelle, z. B. B4, eingeben und diese Zelle mit dem Platzhalterzeichen verketten, etwa so:

=HLOOKUP(B4& "*", B1:I2, 2, FALSE)

Anmerkungen.

• Damit eine HLOOKUP-Formel mit Platzhaltern korrekt funktioniert, muss die bereich_lookup muss auf FALSE gesetzt werden.
• Wenn table_array mehr als einen Wert enthält, der die Platzhalterkriterien erfüllt, wird der erste gefundene Wert zurückgegeben.

Absolute und relative Zellbezüge in HLOOKUP-Formeln

Wenn Sie eine Formel für eine einzelne Zelle schreiben, brauchen Sie sich keine Gedanken über die korrekte Verwendung von relativen und absoluten Zellbezügen zu machen, beides ist ausreichend.

Das Kopieren einer Formel in mehrere Zellen ist eine andere Sache, im Wesentlichen:

• Sie sollten immer die table_array durch Verwendung absoluter Zellverweise mit dem Dollarzeichen ($) wie $B$1:$I$2.
• Typischerweise wird die lookup_value Referenz ist relativ oder gemischt, je nach Ihrer Geschäftslogik.

Um die Sache zu verdeutlichen, sehen wir uns die Formel, die Daten aus einem anderen Blatt bezieht, genauer an:

=HLOOKUP(B$1, Durchmesser!$B$1:$I$2,2,FALSE)

In der obigen Formel verwenden wir absolut Zellbezüge ($B$1:$I$2) in table_array weil sie konstant bleiben sollte, wenn die Formel in andere Zellen kopiert wird.

Für lookup_value (B$1), verwenden wir a gemischt Referenz, relative Spalte und absolute Zeile, da sich unsere Nachschlagewerte (Planetennamen) in derselben Zeile (Zeile 1), aber in verschiedenen Spalten (von B bis I) befinden und die Spaltenreferenz auf der Grundlage einer relativen Position einer Zelle, in die die Formel kopiert wurde, geändert werden sollte.

Dank der geschickten Verwendung von Zellbezügen funktioniert unsere Hlookup-Formel perfekt für mehrere Zellen:

INDEX/MATCH - eine leistungsfähigere Alternative zu Excel HLOOKUP

Wie Sie bereits wissen, hat die Funktion HLOOKUP in Excel eine Reihe von Einschränkungen. Die wichtigsten sind, dass sie nicht überall nachschlagen kann, außer in der obersten Zeile, und dass die Werte sortiert werden müssen, wenn eine Suche mit ungefährer Übereinstimmung durchgeführt wird.

Glücklicherweise gibt es eine leistungsfähigere und vielseitigere Alternative zu Vlookup und Hlookup in Excel - die Verbindung der Funktionen INDEX und MATCH, die auf diese allgemeine Formel hinausläuft:

INDEX ( wo man einen Wert von , MATCH ( Nachschlagewert , wo zu suchen ist , 0))

Angenommen, Ihr Suchwert befindet sich in Zelle B7, Sie suchen nach einer Übereinstimmung in Zeile 2 (B2:I2) und möchten einen Wert aus Zeile 1 (B1:I1) zurückgeben, so lautet die Formel wie folgt:

=INDEX(B1:I1,MATCH(B7,B2:I2,0))

Im folgenden Screenshot sehen Sie 2 Hlookup-Formeln, die in der ersten und zweiten Zeile suchen, und in beiden Fällen funktioniert INDEX MATCH gleich gut.

Eine detaillierte Erklärung der Logik der Formel und weitere Beispiele finden Sie unter INDEX MATCH als bessere Alternative zu VLOOKUP.

Groß- und Kleinschreibung beachtende h-Lookups in Excel

Wie zu Beginn dieses Tutorials erwähnt, ist die Excel-Funktion HLOOKUP unabhängig von der Groß- und Kleinschreibung. In Situationen, in denen die Groß- und Kleinschreibung eine Rolle spielt, können Sie die Funktion EXACT verwenden, die Zellen exakt vergleicht, und sie in die im vorherigen Beispiel besprochene INDEX MATCH-Formel einfügen:

INDEX ( Zeile, um einen Wert von , MATCH(TRUE, EXACT( Zeile, in der gesucht werden soll , Nachschlagewert) , 0))

Angenommen, Ihr Suchwert befindet sich in Zelle B4, der Suchbereich ist B1:I1, und der Rückgabebereich ist B2:I2, dann sieht die Formel folgendermaßen aus:

=INDEX(B2:I2, MATCH(TRUE, EXACT(B1:I1,B4),0))

Wichtiger Hinweis! Da es sich um eine Array-Formel handelt, sollten Sie Strg + Umschalt + Enter drücken, um sie zu vervollständigen.

Das obige Beispiel zeigt meine bevorzugte, aber nicht die einzige Möglichkeit, ein Hlookup unter Berücksichtigung der Groß-/Kleinschreibung in Excel durchzuführen. Wenn Sie andere Techniken kennenlernen möchten, lesen Sie bitte dieses Tutorial: 4 ways to do a case-sensitive Vlookup in Excel. Ich denke, Sie werden keine Probleme haben, die Vlookup-Formeln für ein horizontales Lookup umzubauen.

Top 10 Gründe, warum Excel HLOOKUP nicht funktioniert

Inzwischen wissen Sie bereits, dass Hlookup eine sehr nützliche und leistungsstarke Suchfunktion in Excel ist. Sie ist aber auch eine knifflige Funktion, und aufgrund ihrer zahlreichen Besonderheiten sind #N/A-, #VALUE- oder #REF-Fehler keine Seltenheit. Wenn Ihre HLOOKUP-Formel nicht richtig funktioniert, liegt das höchstwahrscheinlich an einem der folgenden Gründe.

1) HLOOKUP in Excel kann nicht über sich selbst hinausschauen.

Auch wenn Sie alle anderen Details über die horizontale Suche in Excel vergessen haben, erinnern Sie sich bitte an dieses wichtige Detail - Hlookup kann nur in der oberste Zeile Wenn Ihre Nachschlagewerte sich in einer anderen Zeile befinden, wird ein N/A-Fehler zurückgegeben. Um diese Einschränkung zu umgehen, verwenden Sie eine INDEX MATCH-Formel.

2. ungefähre Übereinstimmung vs. exakte Übereinstimmung

Wenn Sie eine Suche in Excel durchführen, entweder horizontal (Hlookup) oder vertikal (Vlookup), suchen Sie in den meisten Fällen nach einer bestimmten Sache und benötigen daher eine exakte Übereinstimmung. bereich_lookup auf TRUE gesetzt oder weggelassen), denken Sie daran, die Werte in der ersten Zeile in aufsteigender Reihenfolge zu sortieren.

Weitere Informationen und Formelbeispiele finden Sie unter Excel Hlookup mit ungefährer und exakter Übereinstimmung.

3. die Referenz des Tabellenfeldes ändert sich beim Kopieren der Formel

Wenn Sie mehrere HLOOKUPs verwenden, um Informationen über eine Zeile von Nachschlagewerten abzurufen, müssen Sie die table_array Referenz, wie in Absolute und relative Zellreferenzen in Hlookup-Formeln gezeigt.

4. eine neue Zeile einfügen oder löschen

Um zu verstehen, warum das Einfügen einer neuen Zeile eine Hlookup-Formel unterbrechen kann, sollten Sie sich daran erinnern, wie Excel HLOOKUP Informationen über den Nachschlagewert erhält - basierend auf der von Ihnen angegebenen Zeilenindexnummer.

Angenommen, Sie möchten Verkaufszahlen auf der Grundlage der Produkt-ID abrufen. Diese Zahlen stehen in Zeile 4, also geben Sie 4 in das Feld zeilen_index_nummer Aber nachdem eine neue Zeile eingefügt wurde, wird sie zu Zeile 5... und Ihr Hlookup funktioniert nicht mehr. Das gleiche Problem kann auftreten, wenn eine vorhandene Zeile aus der Tabelle gelöscht wird.

Die Lösung besteht entweder darin, die Tabelle zu sperren, um zu verhindern, dass die Benutzer neue Zeilen einfügen, oder INDEX & MATCH anstelle von Hlookup zu verwenden. In Index-/Match-Formeln geben Sie die Zeilen, in denen nachgeschaut werden soll und aus denen Werte zurückgegeben werden sollen, als Bereichsreferenzen und nicht als Indexnummern an, und Excel ist intelligent genug, um diese Referenzen im laufenden Betrieb anzupassen. Sie können also so viele Spalten und Zeilen löschen oder einfügen, wie Sie wollen, ohneSie müssen sich nicht mehr darum kümmern, jede Formel in Ihrem Arbeitsblatt zu aktualisieren.

5. doppelte Einträge in der Tabelle

Die Funktion HLOOKUP in Excel kann nur einen Wert zurückgeben, nämlich den ersten Wert in der Tabelle, der mit dem Nachschlagewert übereinstimmt.

Wenn es in Ihrer Tabelle einige identische Datensätze gibt, wählen Sie eine der folgenden Lösungen, die Ihren Anforderungen am besten entspricht:

• Entfernen Sie Duplikate mit den Mitteln von Excel oder mit unserem Duplikatentferner
• Wenn doppelte Datensätze im Datensatz beibehalten werden sollen, erstellen Sie eine PivotTable, um Ihre Daten nach Ihren Wünschen zu gruppieren und zu filtern.
• Verwenden Sie eine Array-Formel, um alle doppelten Werte im Nachschlagebereich zu extrahieren.

6. zusätzliche Räume

Wenn Ihre offensichtlich korrekte Hlookup-Formel eine Reihe von #N/A-Fehlern liefert, überprüfen Sie Ihre Tabelle und den Lookup-Wert auf zusätzliche Leerzeichen. Sie können führende, nachgestellte und überschüssige Zwischenräume schnell entfernen, indem Sie die Excel-Funktion TRIM oder unser Cell Cleaner-Tool verwenden.

7. als Text formatierte Zahlen

Textstrings, die wie Zahlen aussehen, sind ein weiterer Stolperstein für Excel-Formeln. Eine ausführliche Beschreibung dieses Problems und mögliche Lösungen finden Sie unter Warum Excel-Formeln nicht mehr funktionieren können.

8. der Suchwert überschreitet 255 Zeichen

Alle Nachschlagefunktionen in Excel funktionieren nur, solange ein Nachschlagewert weniger als 255 Zeichen lang ist. Ein längerer Nachschlagewert führt zu der Fehlermeldung #VALUE! Da die INDEX /MATCH-Formel von dieser Einschränkung befreit ist, können Sie sie verwenden, um diese Hürde zu überwinden.

9. der vollständige Pfad zur Nachschlage-Arbeitsmappe ist nicht angegeben

Wenn Sie h-lookup aus einer anderen Arbeitsmappe ausführen, denken Sie daran, den vollständigen Pfad dazu anzugeben. Einige Formelbeispiele finden Sie hier: How to do Hlookup from another worksheet or workbook.

10. falsche Argumente

Es wurde bereits mehrfach hervorgehoben, dass HLOOKUP eine anspruchsvolle Funktion ist, die mit großer Sorgfalt behandelt werden sollte. Nachfolgend sind einige der häufigsten Fehler aufgeführt, die durch die Angabe falscher Argumente verursacht werden:

• Wenn row_index_num kleiner als 1 ist, gibt die HLOOKUP-Funktion den Fehler #VALUE! zurück.
• Wenn row_index_num größer ist als die Anzahl der Zeilen in table_array, wird #REF! zurückgegeben.
• Wenn Sie mit approximate match suchen und Ihr lookup_value kleiner ist als der kleinste Wert in der ersten Zeile von table_array, wird der Fehler #N/A zurückgegeben.

Nun, das ist die Verwendung von HLOOKUP in Excel. Ich hoffe, Sie finden diese Informationen hilfreich. Ich danke Ihnen für die Lektüre und hoffe, Sie nächste Woche in unserem Blog zu sehen!

Download Arbeitsheft für die Praxis

Excel HLOOKUP-Formel Beispiele