Laden Sie die Beispielarbeitsmappe herunter
Dieses Tutorial zeigt, wie Sie das Excel HLOOKUP-Funktion in Excel, um einen Wert nachzuschlagen.
Übersicht über die HLOOKUP-Funktion
Die HLOOKUP-Funktion Hlookup steht für horizontales Lookup. Es sucht nach einem Wert in der obersten Zeile einer Tabelle. Gibt dann einen Wert um eine angegebene Anzahl von Zeilen unterhalb des gefundenen Werts zurück. Es ist dasselbe wie ein vlookup, außer dass Werte horizontal statt vertikal gesucht werden.
(Beachten Sie, wie die Formeleingaben angezeigt werden)
Syntax und Eingabe der HVERWEIS-Funktion:
| 1 | =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) |
Lookup-Wert - Der Wert, nach dem Sie suchen möchten.
table_array -Die Tabelle, aus der Daten abgerufen werden sollen.
row_index_num - Die Zeilennummer, aus der Daten abgerufen werden sollen.
range_lookup -[optional] Ein boolescher Wert, um eine genaue Übereinstimmung oder eine ungefähre Übereinstimmung anzugeben. Standard = WAHR = ungefähre Übereinstimmung.
Was ist die HLOOKUP-Funktion?
Als eine der älteren Funktionen in der Welt der Tabellenkalkulationen wird die HLOOKUP-Funktion verwendet, um horizontal Nachschlagen. Es hat einige Einschränkungen, die oft mit anderen Funktionen wie INDEX/MATCH überwunden werden. Außerdem sind die meisten Tabellen vertikal aufgebaut, aber manchmal ist es hilfreich, horizontal zu suchen.
Einfaches Beispiel
Schauen wir uns ein Beispiel von Daten aus einem Notenbuch an. Wir werden mehrere Beispiele zum Extrahieren von Informationen für bestimmte Schüler angehen.
Wenn wir herausfinden möchten, in welcher Klasse Bob sich befindet, würden wir die Formel schreiben:
| 1 | =HVERWEIS("Bob", A1:E3, 2, FALSCH) |
Wichtig ist, dass sich der gesuchte Artikel (Bob) in der ersten Zeile unseres Suchbereichs (A1:E3) befinden muss. Wir haben der Funktion mitgeteilt, dass wir einen Wert aus der 2 . zurückgeben wollennd Zeile des Suchbereichs, in diesem Fall Zeile 2. Schließlich haben wir angegeben, dass wir eine genaue Übereinstimmung indem Sie False als letztes Argument platzieren. Hier lautet die Antwort „Lesen“.
Seitentipp: Sie können als letztes Argument auch die Zahl 0 anstelle von False verwenden, da sie denselben Wert haben. Manche Leute bevorzugen dies, da es schneller zu schreiben ist. Wissen Sie nur, dass beides akzeptabel ist.
Verlagerte Daten
Um unser erstes Beispiel zu verdeutlichen, muss sich das Nachschlageelement nicht in Zeile 1 Ihrer Tabelle befinden, sondern nur in der ersten Zeile Ihres Suchbereichs. Verwenden wir denselben Datensatz:
Lassen Sie uns nun die Note für die Klasse der Naturwissenschaften finden. Unsere Formel wäre
| 1 | =HVERWEIS("Wissenschaft", A2:E3, 2, FALSCH) |
Dies ist immer noch eine gültige Formel, da die erste Zeile unseres Suchbereichs Zeile 2 ist, in der unser Suchbegriff „Wissenschaft“ zu finden ist. Wir geben einen Wert von 2 . zurücknd Zeile des Suchbereichs, in diesem Fall Zeile 3. Die Antwort lautet dann „A-“.
Wildcard-Nutzung
Die HLOOKUP-Funktion unterstützt die Verwendung der Wildcards „*“ und „?“ bei der Suche. Nehmen wir zum Beispiel an, wir hätten vergessen, wie man Franks Namen schreibt, und wollten nur nach einem Namen suchen, der mit „F“ beginnt. Wir könnten die Formel schreiben
| 1 | =HVERWEIS("F*", A1:E3, 2, FALSCH) |
Dies wäre in der Lage, den Namen Frank in Spalte E zu finden und dann den Wert von 2 . zurückzugebennd relative Reihe. In diesem Fall lautet die Antwort „Wissenschaft“.
Ungenaue Übereinstimmung
Meistens möchten Sie sicherstellen, dass das letzte Argument in HLOOKUP False (oder 0) ist, damit Sie eine genaue Übereinstimmung erhalten. Es kann jedoch vorkommen, dass Sie nach einer nicht genauen Übereinstimmung suchen. Wenn Sie über eine Liste mit sortierten Daten verfügen, können Sie auch HVERWEIS verwenden, um das Ergebnis für das gleiche oder das nächstkleinere Element zurückzugeben. Dies wird häufig verwendet, wenn es sich um zunehmende Zahlenbereiche handelt, beispielsweise in einer Steuertabelle oder bei Provisionsprämien.
Angenommen, Sie möchten den Steuersatz für ein in Zelle H2 eingegebenes Einkommen ermitteln. Die Formel in H4 kann lauten:
| 1 | =HVERWEIS(H2, B1:F2, 2, WAHR) |
Der Unterschied in dieser Formel besteht darin, dass unser letztes Argument „Wahr“ ist. In unserem konkreten Beispiel können wir sehen, dass unsere Person, wenn sie ein Einkommen von 45.000 US-Dollar einbringt, einen Steuersatz von 15 % hat.
Notiz: Obwohl wir normalerweise eine genaue Übereinstimmung mit False als Argument wünschen, vergessen Sie, die 4 . anzugebenNS -Argument in einem HVERWEIS ist der Standardwert True. Dies kann zu unerwarteten Ergebnissen führen, insbesondere beim Umgang mit Textwerten.
Dynamische Reihe
HLOOKUP erfordert, dass Sie ein Argument angeben, das angibt, aus welcher Zeile Sie einen Wert zurückgeben möchten, aber es kann vorkommen, dass Sie nicht wissen, wo sich die Zeile befindet, oder Sie möchten, dass Ihr Benutzer die Zeile ändert, aus der zurückgegeben werden soll. In diesen Fällen kann es hilfreich sein, die Funktion MATCH zu verwenden, um die Zeilennummer zu ermitteln.
Betrachten wir noch einmal unser Notenbuch-Beispiel mit einigen Eingaben in G2 und G4. Um die Spaltennummer zu erhalten, könnten wir eine Formel schreiben von
| 1 | =VERGLEICH(G2, A1:A3, 0) |
Dadurch wird versucht, die genaue Position von „Grade“ im Bereich A1:A3 zu finden. Die Antwort lautet 3. Wenn wir dies wissen, können wir es in eine HVERWEIS-Funktion einbinden und eine Formel in G6 wie folgt schreiben:
| 1 | =HVERWEIS(G4, A1:E3, VERGLEICH(G2, A1:A3, 0), 0) |
Die MATCH-Funktion wird also zu 3 ausgewertet, und das weist HLOOKUP an, ein Ergebnis aus der 3 . zurückzugebenrd Reihe im Bereich A1:E3. Insgesamt erhalten wir dann unser gewünschtes Ergebnis von „C“. Unsere Formel ist jetzt dynamisch, da wir entweder die anzuzeigende Zeile oder den zu suchenden Namen ändern können.
HVERWEIS-Einschränkungen
Wie am Anfang des Artikels erwähnt, besteht der größte Nachteil von HLOOKUP darin, dass der Suchbegriff in der Spalte ganz links des Suchbereichs gefunden werden muss. Es gibt zwar einige ausgefallene Tricks, die Sie tun können, um dies zu überwinden, aber die übliche Alternative ist die Verwendung von INDEX und MATCH. Diese Kombination gibt Ihnen mehr Flexibilität und kann manchmal sogar eine schnellere Berechnung sein.
HVERWEIS in Google Tabellen
Die HLOOKUP-Funktion funktioniert in Google Sheets genauso wie in Excel:
Zusätzliche Bemerkungen
Verwenden Sie die HLOOKUP-Funktion, um eine horizontale Suche durchzuführen. Wenn Sie bereits mit der SVERWEIS-Funktion vertraut sind, funktioniert ein HVERWEIS genauso, außer dass die Suche horizontal statt vertikal ausgeführt wird. Das HVERWEIS sucht nach einer genauen Übereinstimmung (range_lookup = FALSE) oder die nächste Übereinstimmung, die kleiner oder gleich dem lookup_value (range_lookup = TRUE, nur numerische Werte) in der ersten Zeile des table_array. Es gibt dann einen entsprechenden Wert zurück, n Zeilen unter der Übereinstimmung.
Wenn Sie ein HVERWEIS verwenden, um eine genaue Übereinstimmung zu finden, definieren Sie zuerst einen identifizierenden Wert, nach dem Sie suchen möchten, als Lookup-Wert. Dieser identifizierende Wert kann eine SSN, eine Mitarbeiter-ID, ein Name oder eine andere eindeutige Kennung sein.
Als nächstes definieren Sie den Bereich (genannt table_array), das die Bezeichner in der obersten Zeile enthält und alle Werte, nach denen Sie letztendlich in den Zeilen darunter suchen möchten. WICHTIG: Die eindeutigen Kennungen müssen sich in der obersten Zeile befinden. Ist dies nicht der Fall, müssen Sie entweder die Zeile nach oben verschieben oder MATCH / INDEX anstelle von HLOOKUP verwenden.
Drittens, definieren Sie die Zeilennummer (Zeilenindex) des table_array die Sie zurückgeben möchten. Denken Sie daran, dass die erste Zeile mit den eindeutigen Bezeichnern Zeile 1 ist. Die zweite Zeile ist Zeile 2 usw.
Zuletzt müssen Sie angeben, ob nach einer genauen Übereinstimmung (FALSE) oder nach der nächsten Übereinstimmung (TRUE) im gesucht werden soll range_lookup. Wenn die Option für exakte Übereinstimmung ausgewählt ist und keine genaue Übereinstimmung gefunden wird, wird ein Fehler zurückgegeben (#N/A). Damit die Formel leer oder „nicht gefunden“ oder einen anderen Wert anstelle des Fehlerwerts (#N/A) zurückgibt, verwenden Sie die IFERROR-Funktion mit HVERWEIS.
So verwenden Sie die HLOOKUP-Funktion, um einen ungefähren Übereinstimmungssatz zurückzugeben: range_lookup = WAHR. Diese Option ist nur für numerische Werte verfügbar. Die Werte müssen aufsteigend sortiert sein.
HLOOKUP-Beispiele in VBA
Sie können auch die HLOOKUP-Funktion in VBA verwenden. Typ:application.worksheetfunction.hlookup(lookup_value,table_array,row_index_num,range_lookup)
Ausführen der folgenden VBA-Anweisungen
| 123456 | Range("G2")=Application.WorksheetFunction.HLookup(Range("C1"),Range("A1:E3"),1)Range("H2")=Application.WorksheetFunction.HLookup(Range("C1"),Range("A1:E3"),2)Range("I2")=Application.WorksheetFunction.HLookup(Range("C1"),Range("A1:E3"),3)Range("G3")=Application.WorksheetFunction.HLookup(Range("D1"),Range("A1:E3"),1)Range("H3")=Application.WorksheetFunction.HLookup(Range("D1"),Range("A1:E3"),2)Range("I3")=Application.WorksheetFunction.HLookup(Range("D1"),Range("A1:E3"),3) |
führt zu folgenden Ergebnissen
Für die Funktionsargumente (lookup_value usw.) können Sie diese entweder direkt in die Funktion eingeben oder stattdessen Variablen definieren.
Zurück zur Liste aller Funktionen in Excel
