Kimm hat ein Arbeitsblatt mit drei Spalten. Spalte A enthält den Nachnamen einer Person, Spalte B enthält den Vornamen und Spalte C enthält die Telefonnummer der Person. Wenn Kimm den Vor- und Nachnamen der Person kennt (sagen wir, sie befinden sich in den Zellen F1 bzw. F2), fragt sie sich, welche Art von Suchformel sie verwenden würde, um die Telefonnummer für die erste Person zurückzugeben, die mit diesem Vor- und Nachnamen übereinstimmt.

Es gibt tatsächlich verschiedene Formeln, mit denen Sie die Telefonnummer ermitteln können. Die meisten Ansätze umfassen die Verwendung von Array-Formeln, die immer mit Strg + Umschalt + Eingabetaste in eine Zelle eingegeben werden. Um das Verständnis der Formeln zu erleichtern, ist es am besten, mit benannten Bereichen zu arbeiten. Richten Sie beispielsweise die folgenden Namen ein:

  • Wählen Sie in Spalte A alle Nachnamen aus und geben Sie ihnen einen Namen wie LNames.

  • Wählen Sie in Spalte B alle Vornamen aus und geben Sie ihnen einen Namen wie FNames.

  • Wählen Sie in Spalte C alle Telefonnummern aus und geben Sie ihnen einen Namen, z. B. Telefone.

Hier finden Sie eine Vielzahl von Array-Formeln, mit denen Sie die Telefonnummer ermitteln können:

=SUMPRODUCT(--(LNames=F2),--(FNames=F1),Phones)

=INDEX(Phones,MATCH(F1&F2,FNames&LNames,))

=INDEX(Phones,INDEX(MATCH(F1&F2,FNames&LNames,0),))

=OFFSET(C1,MATCH(F1&F2,FNames&LNames,),)

Darüber hinaus können Sie eine Array-Formel erstellen, die auf der ROW-Funktion basiert, wie hier gezeigt:

=INDEX(Phones,SUMPRODUCT((F1&F2=FNames&LNames)*(ROW(FNames)-1)))

=INDEX(Phones,MIN(IF((FNames=F1)*(LNames=F2),(ROW(Phones)-1))))

Wenn Sie diesen Ansatz verwenden (abhängig von der ROW-Funktion), müssen Sie möglicherweise den Teil „-1“ der Formeln anpassen, um die Anzahl der Zeilen widerzuspiegeln, die vor Ihren tatsächlichen Daten angezeigt werden. In diesem Fall wird die Zeile um eins dekrementiert, da die erste Zeile der Datentabelle aus den Überschriften für jede Spalte besteht. Die Daten selbst beginnen in Zeile 2.

Beachten Sie, dass alle bisher verwendeten Formeln darauf beruhen, den Vornamen mit dem Nachnamen zu kombinieren, um einen Vergleich durchzuführen. Dies kann in einigen Fällen zu „False Positives“ führen. Angenommen, zwei der Namen, die Sie in Ihren Daten haben, sind Thom Astonfield und Thomas Tonfield. Da der Fall der Buchstaben in den Namen in diesen Formeln keine Rolle spielt, sind sie beim Kombinieren von Vor- und Nachnamen dieser Personen genau gleich. Wenn Sie also nach der Telefonnummer für Thomas Tonfield suchen und sein Name in der Liste nach Thom Astonfield erscheint, erhalten Sie immer die Telefonnummer von Thom anstelle der von Thomas.

Um dieses potenzielle Problem zu umgehen, möchten Sie möglicherweise eine Art Trennzeichen zwischen dem Vor- und Nachnamen einfügen. Wenn Sie eine der Formeln von früher in der Spitze verwenden, müssen Sie lediglich eine Anpassung vornehmen, wie hier gezeigt:

=INDEX(Phones,SUMPRODUCT((F1&":"&F2=FNames&":"&LNames)*(ROW(FNames)-1)))

Das Hinzufügen des Doppelpunkts zwischen dem Vor- und Nachnamen dient als Trennzeichen und eliminiert die Wahrscheinlichkeit von Fehlalarmen.

Sie sollten auch beachten, dass die Informationen, die Sie aus der Formel zurückerhalten, fehlerhaft sind, wenn die Zellen F1 und F2 einen Namen enthalten, der überhaupt nicht in den Daten enthalten ist. Abhängig von der Variation der verwendeten Funktion erhalten Sie möglicherweise eine tatsächliche Fehlerbedingung zurück (z. B. # N / A oder #REF)

oder Sie erhalten einfach falsche Daten zurück. In den Fällen, in denen eine Fehlerbedingung zurückgegeben wird, möchten Sie möglicherweise Ihre Formel anpassen, um die Möglichkeit zu berücksichtigen, dass keine Übereinstimmung gefunden wird, und zwar auf folgende Weise:

=IF(ISERROR(INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0))), "no phone",INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0)))

Es gibt einige Dinge, die Sie bei der Verwendung dieser Formeltypen beachten sollten. In erster Linie hängt die Zuverlässigkeit der Informationen, die Sie zurückerhalten, weitgehend von der Qualität der Informationen in Ihrer Datenliste ab. Wenn Ihre Daten Rechtschreibfehler enthalten, Leerzeichen enthalten, in einer seltsamen Reihenfolge sortiert sind oder mehrere Einträge für dieselbe Person vorhanden sind, kann dies Auswirkungen auf die Rückgabe der Formel haben.

Wenn Sie sich über die Qualität Ihrer Daten nicht sicher sind, können Sie einfach die Filterfunktionen von Excel anstelle einer Formel verwenden. Wenden Sie einen AutoFilter an, und Sie können die ersten beiden Spalten Ihrer Daten verwenden, um Vor- und Nachnamen auszuwählen. Dadurch werden alle Telefonnummern der von Ihnen ausgewählten Person zurückgegeben. Dies ist sehr einfach und erleichtert die Auswahl der benötigten Daten.

Schließlich sollten Sie sich darüber im Klaren sein, dass es andere Ansätze gibt, mit denen Sie das Problem angehen können. Sie könnten beispielsweise eine Formel erstellen, die die DGET-Funktion verwendet. Dazu müsste jedoch eine kleine Kriterientabelle zu Ihrem Arbeitsblatt oder Ihrer Arbeitsmappe hinzugefügt werden. Da Kimm spezifizierte, dass sie keine Zwischenergebnisse in ein Arbeitsblatt einfügen konnte, wurde eine redaktionelle Entscheidung getroffen, die DGET-Funktion nicht als Lösung aufzunehmen, da die Kriterientabelle hinzugefügt werden müsste.

Wenn Sie mit der Verwendung von Makros vertraut sind, können Sie außerdem eine benutzerdefinierte Funktion erstellen, mit der die Daten überprüft und die angeforderte Telefonnummer zurückgegeben werden. Der Vorteil eines solchen Ansatzes besteht darin, dass Sie die Informationen, die die Funktion tatsächlich zurückgibt, flexibler verarbeiten können.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (10479) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tippes für die ältere Menüoberfläche von Excel finden Sie hier:

Abrufen einer Telefonnummer mit einem bekannten Vor- und Nachnamen.