Näherungsweise Übereinstimmung mit mehreren Kriterien suchen

In einigen Fällen müssen Sie möglicherweise den nächstgelegenen oder ungefähren Wert anhand mehrerer Kriterien suchen. Mit der Kombination der Funktionen INDEX,VERGLEICHund WENNkönnen Sie das in Excel schnell umsetzen.

Wie findet man den nächstgelegenen Wert anhand mehrerer Kriterien?

Wie im folgenden Screenshot gezeigt, müssen Sie die passende Person für die Stelle anhand zweier Kriterien finden: „das Studienfach ist Computer“ und „die Berufserfahrung beträgt 15 Jahre“.

Hinweis: Damit dies korrekt funktioniert, müssen die zugehörigen Berufserfahrungen bei doppelten Studienfächern in aufsteigender Reihenfolge sortiert sein.

1. Wählen Sie eine leere Zelle zur Ausgabe des Ergebnisses aus, kopieren Sie die folgende Formel hinein und drücken Sie Strg+Umschalttaste+Eingabetaste, um das Ergebnis zu erhalten.

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

Hinweise:zu dieser Formel:

• D3:D8ist der Spaltenbereich, der das gesuchte Ergebnis enthält;
• G5enthält das zweite Kriterium (Erfahrungsstufe 15), nach dem Sie den Wert suchen werden;
• G4enthält das erste Kriterium (Computer), nach dem Sie den Wert suchen werden;
• B3:B8ist der Zellbereich, der dem ersten Kriterium entspricht;
• C3:C8ist der Zellbereich, der dem zweiten Kriterium entspricht;
• Zahl 1ist eine ungefähre Suche, was bedeutet, dass, falls der exakte Wert nicht gefunden werden kann, der größte Wert zurückgegeben wird, der kleiner als der Suchwert ist;
• Diese Formel muss als Matrixformel mit den Tasten Strg+Umschalttaste+Eingabetaste eingegeben werden.

Funktionsweise dieser Formel

Diese Formel lässt sich in mehrere Bestandteile zerlegen:

• IF(B3:B8=G4,C3:C8): Die WENN-Funktion liefert hier das Ergebnis {9;13;FALSE;FALSE;FALSE;FALSE}, das sich aus dem Vergleich der Werte in B3:B8 mit dem Wert in G4 ergibt. Stimmt ein Wert überein, wird der entsprechende Eintrag aus C3:C8 zurückgegeben – andernfalls FALSCH. In diesem Fall gibt es zwei Übereinstimmungen und vier Nichtübereinstimmungen.
• Eine Matrixformel =VERGLEICH(G5;{9;13;FALSCH;FALSCH;FALSCH;FALSCH};1): Die VERGLEICH-Funktion ermittelt die Position der Zahl 15 (dem Wert in G5) im Bereich C3:C8. Da die Zahl 15 nicht gefunden wird, greift die Funktion auf den nächstkleineren Wert – also 13 – zurück. Das Ergebnis lautet daher 2.
• Und =INDEX(D3:D8,2): Die INDEX-Funktion gibt den Wert der zweiten Zelle im Bereich D3:D8 zurück – das Endergebnis lautet daher Amy.

Verwandte Funktionen

Verwandte Artikel