Zum Hauptinhalt springen

Nächstgelegenes Ergebnis suchen

Author: Amanda Li Last Modified: 2025-05-28

Um das nächstgelegene Ergebnis eines Suchwerts in einem numerischen Datensatz in Excel zu finden, können Sie die Funktionen INDEX, VERGLEICH, ABS, und MIN zusammen verwenden.

lookup closest match 1

Wie finde ich das nächstgelegene Ergebnis in Excel?

Um herauszufinden, welcher Verkäufer den Umsatz am nächsten an dem Ziel von 20.000 USD gebracht hat, wie oben gezeigt, hilft eine Formel, die die Funktionen INDEX, VERGLEICH, ABS und MIN kombiniert: Die ABS-Funktion wandelt alle Differenzwerte zwischen dem Umsatz jedes Verkäufers und dem Umsatzziel in positive Werte um, dann findet MIN den kleinsten Unterschied, was dem nächstgelegenen Treffer entspricht. Nun können wir die VERGLEICH-Funktion verwenden, um die Position des nächstgelegenen Treffers zu lokalisieren und mit INDEX den Wert an der entsprechenden Position abzurufen.

Allgemeine Syntax

=INDEX(Rückgabebereich;VERGLEICH(MIN(ABS(Suchbereich-Suchwert));ABS(Suchbereich-Suchwert);0))

√ Hinweis: Dies ist eine Array-Formel, die Sie mit Strg + Umschalt + Enter eingeben müssen.

  • Rückgabebereich: Der Bereich, aus dem die Kombinationsformel den Verkäufer zurückgeben soll. Hier bezieht es sich auf den Namenbereich.
  • Suchbereich: Der Zellbereich mit den Werten, die mit dem Suchwert verglichen werden sollen. Hier bezieht es sich auf den Umsatzbereich.
  • Suchwert: Der Wert, mit dem verglichen wird, um sein nächstgelegenes Gegenstück zu finden. Hier bezieht es sich auf das Umsatzziel.

Um herauszufinden, welcher Verkäufer den Umsatz am nächsten an dem Ziel von 20.000 USD gebracht hat, kopieren Sie bitte die folgende Formel in die Zelle F5 oder geben Sie sie ein, und drücken Sie Strg + Umschalt + Enter, um das Ergebnis zu erhalten:

=INDEX(B5:B10;VERGLEICH(MIN(ABS(C5:C10-20000));ABS(C5:C10-20000);0))

Oder verwenden Sie einen Zellbezug, um die Formel dynamisch zu gestalten:

=INDEX(B5:B10;VERGLEICH(MIN(ABS(C5:C10-F4));ABS(C5:C10-F4);0))

lookup closest match 2

Erklärung der Formel

=INDEX(B5:B10;VERGLEICH(MIN(ABS(C5:C10-F4));ABS(C5:C10-F4);0))

  • ABS(C5:C10-F4): Der Teil C5:C10-F4 erhält alle Differenzwerte zwischen jedem Umsatz im Bereich C5:C10 und dem Zielumsatz von 20.000 USD in Zelle F4 in einem Array wie diesem: {-4322;2451;6931;-1113;6591;-4782}. Die ABS-Funktion wandelt alle negativen Zahlen in positive um, sodass das Array so aussieht: {4322;2451;6931;1113;6591;4782}.
  • MIN(MIN(ABS(C5:C10-F4))) = MIN(MIN({4322;2451;6931;1113;6591;4782})): Die MIN-Funktion findet die kleinste Zahl aus dem Array {4322;2451;6931;1113;6591;4782}, was dem kleinsten Unterschied oder dem nächstgelegenen Treffer entspricht. Daher gibt die Funktion 1113 zurück.
  • VERGLEICH(MIN(ABS(C5:C10-F4));ABS(C5:C10-F4);0) = VERGLEICH(1113;{4322;2451;6931;1113;6591;4782};0): Der Übereinstimmungstyp 0 zwingt die VERGLEICH-Funktion, die Position der exakten Zahl 1113 im Array {4322;2451;6931;1113;6591;4782} zu finden. Die Funktion gibt 4 zurück, da die Zahl an der 4. Position steht.
  • INDEX(B5:B10B5:B10,,,VERGLEICH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4))),,,ABS(C5:C10-F4)ABS(C5:C10-F4);0))) = INDEX(B5:B10B5:B10,,,4): Die INDEX-Funktion gibt den 4. Wert im Namenbereich B5:B10 zurück, welcher Bale ist.

Zugehörige Funktionen

Excel INDEX-Funktion

Die Excel INDEX-Funktion gibt den angezeigten Wert basierend auf einer bestimmten Position aus einem Bereich oder einem Array zurück.

Excel VERGLEICH-Funktion

Die Excel VERGLEICH-Funktion sucht nach einem bestimmten Wert in einem Zellbereich und gibt die relative Position des Werts zurück.

Excel ABS-Funktion

Die ABS-Funktion gibt den absoluten Wert einer Zahl zurück. Negative Zahlen werden mit dieser Funktion in positive Zahlen umgewandelt, während positive Zahlen und Null unverändert bleiben.


Zugehörige Formeln

Nächstgelegenes Ergebnis mit mehreren Kriterien suchen

In einigen Fällen müssen Sie möglicherweise das nächstgelegene oder ungefähre Ergebnis basierend auf mehr als einem Kriterium suchen. Mit der Kombination der Funktionen INDEX, VERGLEICH und WENN können Sie dies schnell in Excel erledigen.

Ungefähre Übereinstimmung mit INDEX und VERGLEICH

Es gibt Zeiten, in denen wir in Excel ungefähre Übereinstimmungen finden müssen, um die Leistung von Mitarbeitern zu bewerten, die Noten von Schülern zu berechnen, die Portokosten basierend auf dem Gewicht zu berechnen usw. In diesem Tutorial sprechen wir darüber, wie man die Funktionen INDEX und VERGLEICH verwendet, um die benötigten Ergebnisse abzurufen.

Nächstgelegenes Ergebnis mit mehreren Kriterien suchen

In einigen Fällen müssen Sie möglicherweise das nächstgelegene oder ungefähre Ergebnis basierend auf mehr als einem Kriterium suchen. Mit der Kombination der Funktionen INDEX, VERGLEICH und WENN können Sie dies schnell in Excel erledigen.


Die besten Tools zur Büroproduktivität

Kutools für Excel - Hilft Ihnen, aus der Menge hervorzustechen

🤖 Kutools AI-Assistent: Revolutionieren Sie die Datenanalyse basierend auf: Intelligente Ausführung   |  Code generieren  |  Benutzerdefinierte Formeln erstellen  |  Daten analysieren und Diagramme generieren  |  Kutools-Funktionen aufrufen
Beliebte Funktionen: Doppelte finden, hervorheben oder identifizieren  |  Leere Zeilen löschen  |  Spalten oder Zellen kombinieren ohne Datenverlust  |  Runden ohne Formel ...
Super VLookup: Mehrere Kriterien  |  Mehrere Werte  |  Über mehrere Blätter hinweg  |  Fuzzy Match...
Erweiterte Dropdown-Liste: Einfache Dropdown-Liste  |  Abhängige Dropdown-Liste  |  Mehrfachauswahl-Dropdown-Liste...
Spaltenmanager: Eine bestimmte Anzahl von Spalten hinzufügen  |  Spalten verschieben  |  Sichtbarkeitsstatus ausgeblendeter Spalten umschalten  Spalten vergleichen, um gleiche und unterschiedliche Zellen auszuwählen ...
Herausragende Funktionen: Gitterfokus  |  Entwurfsansicht  |  Erweiterte Formelleiste  |  Arbeitsmappen- und Blattmanager | AutoText-Bibliothek (Auto Text)  |  Datumsauswahl  |  Daten zusammenführen  |  Zellen verschlüsseln/entschlüsseln  |  E-Mails per Liste senden  |  Superfilter  |  Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Werkzeugsets12 Texttools (Text hinzufügen, Bestimmte Zeichen löschen ...)  |  50+ Diagramm typen (Gantt-Diagramm ...)  |  40+ Praktische Formeln (Alter basierend auf dem Geburtsdatum berechnen ...)  |  19 Einfügetools (QR-Code einfügen, Bild aus Pfad einfügen ...)  |  12 Konvertierungstools (In Wörter umwandeln, Währungsumrechnung ...)  |  7 Zusammenführen- und Aufteilungstools (Erweiterte Zeilen zusammenführen, Excel-Zellen aufteilen ...)  |  ... und mehr

Kutools für Excel bietet über 300 Funktionen, sodass das, was Sie benötigen, nur einen Klick entfernt ist...


Office Tab - Aktiviert tabbed Lesen und Bearbeiten in Microsoft Office (inklusive Excel)

  • In einer Sekunde zwischen Dutzenden offener Dokumente wechseln!
  • Reduziert Hunderte von Mausklicks für Sie jeden Tag, verabschieden Sie sich von der Maushand.
  • Steigert Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
  • Bringt effiziente Tabs ins Office (inklusive Excel), genau wie bei Chrome, Edge und Firefox.