Direkt zum Inhalt

Wie finde ich den nächsten oder nächsten Wert (Zahl oder Textzeichenfolge) in Excel?

Angenommen, Sie haben eine Liste mit Zahlen in einer Spalte, und jetzt müssen Sie aus der Liste der Zahlen den Wert ermitteln, der einem bestimmten Wert am nächsten oder am nächsten liegt. Wie gehst du damit um? Tatsächlich können Sie mit den folgenden Schritten den nächstgelegenen oder nächstgelegenen Wert in Excel finden.

Finden Sie die nächstgelegene oder nächstgelegene Zahl mit der Array-Formel

Sie haben beispielsweise eine Liste mit Zahlen in Spalte A, und jetzt finden Sie den nächstgelegenen Wert oder den nächstgelegenen Wert von 18 in Spalte A. Sie können dies wie folgt tun:

Wählen Sie eine leere Zelle aus, geben Sie die folgende Formel ein und drücken Sie die Taste Strg + Umschalt + Enter Schlüssel zusammen.

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

Hinweis: In dieser Array-Formel von {=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))},

  • B3: B22 ist der Bereich, in dem Sie den spezifischen Wert ermitteln möchten
  • E2 ist der Suchwert, mit dem Sie verglichen werden möchten.
Band notieren Formel ist zu kompliziert, um sich zu erinnern? Speichern Sie die Formel als automatischen Texteintrag für die zukünftige Wiederverwendung mit nur einem Klick!
Lese mehr ...     Kostenlose Testversion

Wählen Sie mit Kutools for Excel ganz einfach alle nächstgelegenen Zahlen im Abweichungsbereich des angegebenen Werts aus

Manchmal möchten Sie möglicherweise alle Schrankwerte für den angegebenen Wert in einem Bereich herausfinden und auswählen. Tatsächlich können wir einen Abweichungswert definieren und dann Kutools für Excel anwenden Wählen Sie Spezialzellen Dienstprogramm zum einfachen Herausfinden und Auswählen aller nächstgelegenen Werte innerhalb des Divisionsbereichs von give value.

Kutools for Excel- Enthält mehr als 300 praktische Tools für Excel. Kostenlose 60-Tage-Testversion mit vollem Funktionsumfang, keine Kreditkarte erforderlich! Hol es dir jetzt

In unserem Beispiel definieren wir beispielsweise den Abweichungswert als 2 und der angegebene Wert ist 18. Daher müssen wir Werte zwischen diesen herausfinden und auswählen 16 (= 18-2) und 20 (= 18 + 2). Bitte beachten Sie folgende Schritte:

1. Wählen Sie den Bereich aus, in dem Sie nach Werten suchen, die dem angegebenen Wert am nächsten kommen, und klicken Sie dann auf Kutoolen > Auswählen > Wählen Sie Bestimmte Zellen aus.

2. Klicken Sie im sich öffnenden Dialogfeld Bestimmte Zellen auswählen auf
(1) Überprüfen Sie die Zelle Option in der Auswahltyp Sektion;
(2) In der Spezifischer Typ Klicken Sie im Abschnitt auf die erste Dropdown-Liste und wählen Sie Größer als oder gleich wie davon und tippe 16 in das folgende Feld und wählen Sie dann Gleich oder kleiner als aus der zweiten Dropdown-Liste und geben Sie ein 20 in folgende Box. Siehe linken Screenshot:

3. Drücke den Ok Schaltfläche, um dieses Dienstprogramm anzuwenden. Dann erscheint ein Dialogfeld, in dem angezeigt wird, wie viele Zellen ausgewählt wurden. Und Sie werden sehen, dass alle nächstgelegenen Werte innerhalb des Abweichungsbereichs des angegebenen Werts wie im folgenden Screenshot ausgewählt ausgewählt sind:


Suchen Sie mit einem praktischen Werkzeug die nächstgelegene oder nächstgelegene Textzeichenfolge

Wenn Sie Kutools für Excel installiert haben, können Sie die Fuzzy-Suchfunktion anwenden, um in Excel ganz einfach die nächsten Textzeichenfolgen aus einem bestimmten Bereich zu ermitteln. Bitte gehen Sie wie folgt vor:

Kutools for Excel- Enthält mehr als 300 praktische Tools für Excel. Kostenlose 60-Tage-Testversion mit vollem Funktionsumfang, keine Kreditkarte erforderlich! Hol es dir jetzt

1. Klicken Sie Kutoolen > Finden Sie > Unscharfe Suche um den Fuzzy-Lookup-Bereich in Ihrer Arbeitsmappe zu aktivieren.

2. Konfigurieren Sie im Bereich Fuzzy-Suche wie folgt: ;
(1) Überprüfen Sie die Spezifizierte Wählen Sie den Bereich aus, in dem Sie nach den nächsten Textzeichenfolgen suchen möchten.
(2) Überprüfen Sie die Suchen Sie nach angegebenem Text Option;
(3) Gehe zum Text Geben Sie den angegebenen Text ein, dessen nächste Textzeichenfolge Sie finden.
(4) In der Maximale Anzahl verschiedener Zeichen Box, bitte geben Sie eine Nummer ein. In meinem Fall gebe ich 1 ein;
(5) Die Länge der Zellenzeichenfolge beträgt mindestens Box, bitte geben Sie eine Nummer ein. In meinem Fall gebe ich 5 ein;

3. Drücke den Finden Sie .

Erweitern Sie nun die Suchergebnisse, und Sie werden sehen, dass alle nächstgelegenen Textzeichenfolgen in großen Mengen aufgelistet sind. Siehe Screenshot:


Demo: Wählen Sie alle nächstgelegenen Werte im Abweichungsbereich des angegebenen Werts aus

Kutools for Excel: Über 300 praktische Werkzeuge immer zur Hand! Starten Sie noch heute Ihre 30-tägige kostenlose Testversion ohne Funktionseinschränkungen. Jetzt herunterladen!

Beste Office-Produktivitätstools

🤖 Kutools KI-Assistent: Revolutionieren Sie die Datenanalyse basierend auf: Intelligente Ausführung   |  Code generieren  |  Erstellen Sie benutzerdefinierte Formeln  |  Analysieren Sie Daten und erstellen Sie Diagramme  |  Rufen Sie Kutools-Funktionen auf...
Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate   |  Leere Zeilen löschen   |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren   |   Runde ohne Formel ...
Super-Lookup: VLookup mit mehreren Kriterien    VLookup mit mehreren Werten  |   VLookup über mehrere Blätter hinweg   |   Unscharfe Suche ....
Erweiterte Dropdown-Liste: Erstellen Sie schnell eine Dropdown-Liste   |  Abhängige Dropdown-Liste   |  Mehrfachauswahl Dropdown-Liste ....
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  |  Vergleichen Sie Bereiche und Spalten ...
Ausgewählte Funktionen: Rasterfokus   |  Designansicht   |   Große Formelleiste    Arbeitsmappen- und Blattmanager   |  Ressourcen (Autotext)   |  Datumsauswahl   |  Arbeitsblätter kombinieren   |  Zellen verschlüsseln/entschlüsseln    Senden Sie E-Mails nach Liste   |  Superfilter   |   Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets12 Text Tools (Text hinzufügen, Zeichen entfernen, ...)   |   50+ Chart Typen (Gantt-Diagramm, ...)   |   40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag, ...)   |   19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen, ...)   |   12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion, ...)   |   7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Zellen teilen, ...)   |   ... und mehr

Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über 300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen.  Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...

Beschreibung


Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
Comments (42)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How do we do this if our data is filtered?
This comment was minimized by the moderator on the site
copy the filtered data to a new sheet
This comment was minimized by the moderator on the site
Using the formula how would you return the value next to 17 if there was another column next to number like names. So if 17 is the closest in rang the name next to 17 (John) would be returned?


Example: 18 is nearest to 17 so the return value would be John


Numbers Names
38 Tammy
17 John
20 Amy
This comment was minimized by the moderator on the site
You can use the Approximate match of VLOOKUP function to solve this problem.
=VLOOKUP(E2,A1:B15,2,TRUE)
This comment was minimized by the moderator on the site
I used this formula =INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0)) and it works great. However i have found that where the source number is exactly between two numbers in the range, the lower range number is selected to be the closest.

eg: Searching for the closest number to 9 in the range: 6, 8, 10, 12. It will chose 8 instead of 10. Rounding convention is to round up if exactly half way between. Is there a workaround? Thanks.
This comment was minimized by the moderator on the site
ITS NOT WORKING its #N/A somehow
This comment was minimized by the moderator on the site
Hi DAKOT,
=INDEX(A1:A20,MATCH(MIN(ABS(A1:A20-D1)),ABS(A1:A20-D1),0)) is an array formula, after entering it, please remember to press the Ctrl + Shift + Enter keys together.
This comment was minimized by the moderator on the site
Hi,
im using that formula in finding closest date, it is working.but i want to add condition: closest date that is less than 30 days of the current date (today).it is possible?
Anyone can help please?thank you
This comment was minimized by the moderator on the site
Hi farolito,
How about changing the value you will compare with to =TODAY() in Cell D1?
This comment was minimized by the moderator on the site
Hi, I can get it to work, amazing, BUT not when I input '1' as my 'match_type', instead of the '0' that you used. I want to return values less than or equal to, not just closest to +/-. If I enter 1 instead of 0, it doesn't work. Thoughts on why this might be?
This comment was minimized by the moderator on the site
Great formula -thank you-just a quick question. Anyone know how to highlight the cell that is closest in the match so in long lists it is easy to find??
This comment was minimized by the moderator on the site
If you have the row number from the formula above then you could set a conditional formatting rule on the search array to highlight a cell if it lies on that row.
This comment was minimized by the moderator on the site
It does not work for me! Excel says that there is an error :(
This comment was minimized by the moderator on the site
My bad This will look for all nearest date to today in column d =LARGE(D:D,COUNTIF(d:D,">="&TODAY())) http://WWW.excelireland.com
This comment was minimized by the moderator on the site
Hi, I tried the above formula. however, it is giving me a #N/A. 1. Copy pasted the range from A2 to A43. 2. Formula given was : =INDEX(A2:A43,MATCH(MIN(ABS(A2:A43-H1)),ABS(A2:A43-H1),0)) 3. Press control +Shift +enter Can anybody help me as to what wrong I am doing. :(
This comment was minimized by the moderator on the site
Replace the " , " separator for " ; "
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations