So spüren Sie Werte per Formel in unsortierten Listen auf
© sdecoret - Adobe Stock

So spüren Sie Werte per Formel in unsortierten Listen auf

In unsortierten Listen Daten über Tabellenfunktionen zu finden, ist ein Problem. Das liegt daran, dass viele Verweisfunktionen nur in sortierten Listen das gewünschte Ergebnis liefern, falls der Suchwert nicht exakt gefunden wird. Über Spezialformeln find
Veröffentlicht am | 
Zuletzt aktualisiert am

Diese Formeln sind beispielsweise hervorragend geeignet, um Produktionskapazitäten zuzuweisen oder auch um Rechnungs- oder Angebotsdaten zu finden. Mit dieser Formel finden Sie den nächstkleineren oder gleichen Wert: 

=KGRÖSSTE(DatBer;ZÄHLENWENN (DatBer;”>”&Suchwert)+1)

Setzen Sie diese Formel ein, um den nächstgrößeren oder gleichen Wert aufzuspüren:

=KKLEINSTE(DatBer;ZÄHLENWENN (DatBer;”<”&Suchwert)+1)

Sie übergeben den Formeln jeweils zwei Argumente: Mit DatBer übergeben Sie den Spaltenbereich, in dem Sie den nächstgrößeren oder -kleineren Wert suchen. Das Argument Suchwert legt den Wert fest, den Sie innerhalb von DatBer suchen.

Die Formeln liefern den nächstgrößeren bzw. -kleineren Wert zu dem Suchwert, sofern der Suchwert nicht exakt auftritt. Falls der Suchwert exakt auftritt, liefern die Formeln den Suchwert als Ergebnis.

In den Zellen F5 und F9 liefern die folgenden beiden Formeln in Abbildung [1] die Ergebnisse 31 und 17:

Zelle F5: =KKLEINSTE($C$4:$C$18; ZÄHLENWENN($C$4:$C$18;”<”&F3)+1)

Zelle F9: =KGRÖSSTE($C$4:$C$18; ZÄHLENWENN($C$4:$C$18;”>”&F3)+1)

Der Suchwert 25 ist im Bereich C4:C18 nicht enthalten. Der nächstgrößere Wert ist 31 und der nächstkleinere Wert 17. Die Formeln arbeiten nach folgendem Prinzip, um diese Werte zu ermitteln:

Über ZÄHLENWENN zählen Sie, wie viele Werte größer bzw. kleiner als der Suchwert sind. Indem Sie diese Anzahl plus 1 an KKLEINSTE bzw. KGRÖSSTE übergeben, wird der entsprechend kleinste bzw. größte Wert ausgegeben. Das ist dann genau der Wert, der in der Rangfolge direkt über bzw. unter dem Suchwert liegt.

 

Wie Sie die passenden Restdaten ermitteln

Um für die gefundenen Werte die dazugehörenden Daten zu ermitteln, setzen Sie in Zelle F6 die folgende Formel ein:

=INDEX($A$4:$A$18;VERGLEICH ($F$5;$C$4:$C$18;0))

Über VERGLEICH findet die Formel die Position des gefundenen Werts aus Zelle F5. Die INDEX-Funktion liefert dazu den passenden Wert aus dem Bereich A4:A18. [2] Für die Zellen F7, F10 und F11 passen Sie die Bezüge der Formel entsprechend an.