Ungefähren Abgleich mit INDEX und VERGLEICH
Es gibt Situationen, in denen wir in Excel ungefähre Übereinstimmungen benötigen – etwa um die Leistung von Mitarbeitern zu bewerten, Schülerergebnisse zu benoten oder Versandkosten basierend auf dem Gewicht zu berechnen. In diesem Tutorial erfahren Sie, wie Sie mithilfe der INDEX- und VERGLEICH-Funktionen die gewünschten Ergebnisse abrufen können.
Wie finden Sie mit INDEX und VERGLEICH ungefähre Übereinstimmungen?
Um den Versandpreisentsprechend dem angegebenen Paketgewicht ()9, dem Wert in der Zelle F6) gemäß dem obigen Screenshot zu ermitteln, kombinieren Sie die Funktionen INDEX und VERGLEICH – und wenden dabei einen kleinen Trick an, damit die Formel den gewünschten ungefähren Treffer findet: Der Vergleichstyp -1 veranlasst VERGLEICH, die Position des kleinsten Werts zu finden, der größer als oder gleich 9 ist; anschließend ermittelt INDEX den entsprechenden Versandpreis in derselben Zeile.
Allgemeine Syntax
=INDEX()return_range,MATCH()lookup_value,lookup_array,[match_type]))
- Rückgabebereich: Der Bereich, aus dem die Kombinationsformel den Versandpreis zurückgeben soll – in diesem Fall der Bereich mit den Versandpreisen.
- Suchwert: Der Wert, den VERGLEICH verwendet, um die Position des zugehörigen Versandpreises zu ermitteln. In diesem Fall bezieht er sich auf das angegebene Gewicht.
- Suchmatrix: Der Zellbereich mit den Werten, die mit dem Suchwert verglichen werden sollen. Hier bezieht sich dies auf den Gewichtsbereich.
- Vergleichstyp: 1 oder -1.
1 oder weggelassen (Standard): VERGLEICH sucht den größten Wert, der kleiner als oder gleich dem Suchwert ist. Die Werte in der Suchmatrix müssen in aufsteigender Reihenfolge angeordnet sein.
-1: VERGLEICH sucht den kleinsten Wert, der größer als oder gleich dem Suchwert ist. Die Werte in der Suchmatrix müssen in absteigender Reihenfolge angeordnet sein.
Um den Versandpreis für ein Paket mit einem Gewicht von 9kg zu ermitteln, kopieren oder geben Sie die folgende Formel in die Zelle F7ein und drücken Sie EINGABETASTE, um das Ergebnis zu erhalten:
=INDEX()C6:C10;VERGLEICH()9;B6:B10;-[PH_23]))
Oder verwenden Sie Eine Zellreferenz, um die Formel dynamisch zu gestalten:
=INDEX()C6:C10;VERGLEICH()F6;B6:B10;-[PH_24]))
Erläuterung der Formel
=INDEX()C6:C10,MATCH(F6,B6:B10,-1))
- VERGLEICH(F6;B6:B10;-1): Die VERGLEICH-Funktion ermittelt die Position des angegebenen Gewichts 9(der Wert in der Zelle)F6) im Gewichtsbereich B6:B10. Der Vergleichstyp -1 veranlasst VERGLEICH, den kleinsten Wert zu finden, der größer oder gleich dem angegebenen Gewicht 9 ist – das ist hier 10. Die Funktion gibt daher 3 zurück, da es sich um den 3.Wert im Bereich handelt. (Hinweis: Der Vergleichstyp)-1 erfordert, dass die Werte im Bereich B6:B10 in absteigender Reihenfolge angeordnet sind.)
- INDEX()C6:C10,VERGLEICH(F6;B6:B10;-1)) = INDEX(C6:C10 Die INDEX-Funktion gibt den 3. Wert im Preistabelle-Bereich C6:C10 zurück – nämlich 50.
Verwandte Funktionen
Die Excel-Funktion INDEX gibt den Angezeigter Wert basierend auf einer vorgegebenen Position aus einem Bereich oder einem Array zurück.
Die Excel-Funktion VERGLEICH sucht in einem Zellbereich nach einem bestimmten Wert und gibt dessen relative Position zurück.
Verwandte Formeln
Exakter Abgleich mit INDEX und VERGLEICH
Wenn Sie in Excel gezielt Informationen zu einem bestimmten Produkt, Film oder einer Person suchen, nutzen Sie die Kombination aus INDEX und VERGLEICH optimal.
Nächsten Trefferwert mit mehreren Kriterien suchen
In einigen Fällen müssen Sie unter Berücksichtigung mehrerer Kriterien den nächstgelegenen oder ungefähren Trefferwert ermitteln. Mit der Kombination der Funktionen INDEX, VERGLEICH und WENN gelingt Ihnen das in Excel schnell und effizient.
Die besten Office-Produktivitätswerkzeuge
Kutools für Excel – Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen und stellt sicher, dass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab – Aktiviert tabbasiertes Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- Mit einem Klick zwischen Dutzenden geöffneter Dokumente wechseln!
- Reduziert täglich Hunderte Mausklicks für Sie – verabschieden Sie sich von der Maus-Hand.
- Steigert Ihre Produktivität um 50 %, wenn Sie mehrere Dokumente gleichzeitig anzeigen und bearbeiten.
- Bringt effiziente Tabs in Office (inklusive Excel) – genau wie bei Chrome, Edge und Firefox.