Anpassen der VLOOKUP-Funktion (Microsoft Excel)
John hat eine Nachschlagetabelle mit aufsteigenden numerischen Werten in Spalte A und entsprechenden Textwerten in Spalte B. Wenn er die VLOOKUP-Funktion verwendet, wird der Textwert für den numerischen Wert zurückgegeben, der gleich oder kleiner als der angegebene Nachschlagewert ist. John möchte wirklich, dass der erste Wert gleich oder größer als der Suchwert ist.
Es gibt keine Möglichkeit zu ändern, wie VLOOKUP seine Arbeit macht. Es wird immer mit dem Wert übereinstimmen, der gleich oder kleiner als der Suchwert ist. Eine Option besteht jedoch darin, die Formel zu ändern, die für die eigentliche Suche verwendet wird. Betrachten Sie die folgende Formel, bei der davon ausgegangen wird, dass sich der Wert, den Sie für Ihre Suche verwenden möchten, in Zelle D1 befindet:
=IF(VLOOKUP(D1,$A$1:$A$10,1)=D1,VLOOKUP(D1,$A$1:$B$10,2), INDEX($B$1:$B$10,1+MATCH(D1,$A$1:$A$10)))
Wenn der Wert in D1 genau mit einem Wert in Spalte A übereinstimmt, wird die reguläre VLOOKUP-Formel verwendet. Ist dies nicht der Fall, wird VLOOKUP zugunsten der INDEX-Funktion in Verbindung mit der MATCH-Funktion aufgegeben.
Wenn Sie Ihre Datentabelle in absteigender Reihenfolge sortieren können, können Sie eine kürzere Formel verwenden:
=INDEX($A$1:$B$10,MATCH(D1,$A$1:$A$10,-1),2)
Die MATCH-Funktion verwendet den D1-Wert, um nach dem kleinsten Wert zu suchen, der größer oder gleich diesem Wert ist. (Dies ist, was der Parameter -1 angibt.) Die MATCH-Funktion gibt die Zeilennummer der richtigen Zeile zurück und wird dann von INDEX verwendet, um den Wert tatsächlich abzurufen.
Ein weiterer interessanter Ansatz für das Problem besteht darin, die Umkehrung der Suchwerte als Kontrollspalte zu verwenden, die zum tatsächlichen Nachschlagen von Informationen aus der Datentabelle verwendet wird. Angenommen, Ihre Datentabelle befindet sich in A1: B10 mit den tatsächlichen numerischen Werten, die Sie für die Suche in Spalte A verwenden. Sie müssen eine Spalte links von Ihrer Datentabelle einfügen. Fügen Sie in die erste Zelle dieser neuen Spalte (jetzt Spalte A) die folgende Formel ein:
=1/B1
Dies liefert die Umkehrung des Werts in B1, und Sie können ihn in die Zellen in Spalte A kopieren. Ihre Datentabelle enthält jetzt drei Spalten, A1: C10.
Sortieren Sie anschließend Ihre Datentabelle anhand dieser neuen Spalte in aufsteigender Reihenfolge.
Jetzt nehmen Sie eine geringfügige Änderung an Ihrer Suchformel vor, sodass sie umgekehrt zu dem aussieht, was Sie möchten. Angenommen, der Wert, den Sie für Ihre Suche verwenden möchten, befindet sich in Zelle E1, würden Sie die folgende Formel verwenden:
=VLOOKUP(1/E1,$A$1:$C$10,3)
Am Ende erhalten Sie effektiv den gewünschten Wert aus Spalte C, der dem Wert in Spalte B zugeordnet ist, der gleich oder kleiner als der Wert in E1 ist.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (3090) gilt für Microsoft Excel 97, 2000, 2002 und 2003.