Nächstgelegenen übereinstimmenden Wert mit mehreren Kriterien suchen

In einigen Fällen müssen Sie möglicherweise den nächstgelegenen oder ungefähren übereinstimmenden Wert auf der Grundlage von mehr als einem Kriterium finden. Mit der Kombination der Funktionen INDEX, VERGLEICH und WENN können Sie dies schnell in Excel erledigen.

Wie sucht man den nächstgelegenen übereinstimmenden Wert mit mehr als einem Kriterium?

Wie im folgenden Screenshot gezeigt, müssen Sie die richtige Person für den Job basierend auf zwei Kriterien „das Hauptfach ist Informatik“ und „die Berufserfahrung beträgt 15 Jahre“ finden.

Hinweis: Damit dies korrekt funktioniert, sollten die Berufserfahrungen dieser doppelten Hauptfächer in aufsteigender Reihenfolge sortiert werden, wenn es doppelte Hauptfächer gibt.

1. Wählen Sie eine leere Zelle aus, um das Ergebnis auszugeben, kopieren Sie dann die folgende Formel hinein und drücken Sie die Tasten Strg + Umschalt + Eingabe, um das Ergebnis zu erhalten.

=INDEX(D3:D8;VERGLEICH(G5;WENN(B3:B8=G4;C3:C8);1))

Hinweise: In dieser Formel:

• D3:D8 ist der Spaltenbereich, der das gesuchte Ergebnis enthält;
• G5 enthält das zweite Kriterium (Erfahrungsanzahl 15), auf dessen Basis Sie den Wert suchen;
• G4 enthält das erste Kriterium (Informatik), auf dessen Basis Sie den Wert suchen;
• B3:B8 ist der Zellbereich, der zum ersten Kriterium passt;
• C3:C8 ist der Zellbereich, der zum zweiten Kriterium passt;
• Die Zahl 1 steht für eine ungefähre Suche, was bedeutet, dass, wenn der genaue Wert nicht gefunden werden kann, der größte Wert, der kleiner als der Suchwert ist, gefunden wird;
• Diese Formel muss als Array-Formel mit den Tasten Strg + Umschalt + Eingabe eingegeben werden.

Wie diese Formel funktioniert

Diese Formel kann in mehrere Komponenten unterteilt werden:

• WENN(B3:B8=G4;C3:C8): Die WENN-Funktion gibt hier das Ergebnis als {9;13;FALSCH;FALSCH;FALSCH;FALSCH} zurück, das aus dem Testen der Werte in B3:B8 stammt, um zu sehen, ob sie mit dem Wert in G4 übereinstimmen. Wenn es eine Übereinstimmung gibt, wird der entsprechende Wert zurückgegeben, andernfalls wird FALSCH zurückgegeben. Hier werden zwei Übereinstimmungen und vier Nichtübereinstimmungen gefunden.
• Eine Array-Formel =VERGLEICH(G5;{9;13;FALSCH;FALSCH;FALSCH;FALSCH};1): Die VERGLEICH-Funktion findet die Position der Zahl 15 (der Wert in G5) im Bereich C3:C8. Da die Zahl 15 nicht gefunden werden kann, stimmt sie mit dem nächstkleineren Wert 13 überein. Das Ergebnis hier ist also 2.
• Und =INDEX(D3:D8;2): Die INDEX-Funktion gibt den Wert der zweiten Zelle im Bereich D3:D8 zurück. Das Endergebnis ist also Amy.

Verwandte Funktionen

Verwandte Artikel