Kimm a une feuille de calcul qui contient trois colonnes. La colonne A contient le nom de famille d’une personne, la colonne B contient le prénom et la colonne C contient le numéro de téléphone de la personne. Si Kimm connaît le prénom et le nom de la personne (disons qu’ils sont dans les cellules F1 et F2, respectivement), elle se demande quel type de formule de recherche elle utiliserait pour renvoyer le numéro de téléphone de la première personne correspondant à ce prénom et ce nom.

Il existe en fait plusieurs formules différentes que vous pouvez utiliser pour déterminer le numéro de téléphone. La plupart des approches impliquent l’utilisation de formules matricielles, qui sont toujours entrées dans une cellule en utilisant Ctrl + Maj + Entrée. Pour faciliter la compréhension des formules, il est préférable de travailler avec des plages nommées. Par exemple, définissez les noms suivants:

  • Sélectionnez tous les noms, dans la colonne A, et donnez-leur un nom tel que LNames.

  • Sélectionnez tous les prénoms, dans la colonne B, et donnez-leur un nom tel que FNames.

  • Sélectionnez tous les numéros de téléphone, dans la colonne C, et donnez-leur un nom tel que Téléphones.

Voici une variété de formules matricielles que vous pouvez utiliser pour trouver le numéro de téléphone:

=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,),)

De plus, vous pouvez construire une formule matricielle qui repose sur la fonction ROW, comme indiqué ici:

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

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

Si vous utilisez cette approche (en vous appuyant sur la fonction ROW), vous devrez peut-être ajuster la partie «-1» des formules pour refléter le nombre de lignes qui apparaissent avant vos données réelles. Dans ce cas, la ligne est décrémentée de un car la première ligne de la table de données se compose des en-têtes de chaque colonne; les données elles-mêmes commencent à la ligne 2.

Notez que toutes les formules utilisées jusqu’à présent reposent sur la combinaison du prénom avec le nom de famille afin de faire une comparaison. Cela a le risque de rencontrer des «faux positifs» dans certains cas. Par exemple, disons que deux des noms que vous avez dans vos données sont Thom Astonfield et Thomas Tonfield. Étant donné que la casse des lettres dans les noms n’a pas d’importance dans ces formules, lorsque vous combinez le prénom et le nom de ces personnes, elles sont exactement les mêmes. Ainsi, si vous recherchez le numéro de téléphone de Thomas Tonfield et que son nom apparaît dans la liste après Thom Astonfield, vous obtiendrez toujours le numéro de téléphone de Thom au lieu de celui de Thomas.

Pour contourner ce problème potentiel, vous souhaiterez peut-être inclure une sorte de séparateur entre le prénom et le nom. En utilisant l’une des formules précédentes dans l’astuce, tout ce que vous auriez à faire est de faire un ajustement, comme indiqué ici:

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

L’ajout de deux points entre le prénom et le nom de famille agit comme un séparateur, éliminant le risque de faux positifs.

Vous devez également comprendre que si les cellules F1 et F2 contiennent un nom qui n’apparaît pas du tout dans les données, les informations que vous récupérez de la formule seront erronées. Selon la variation de la fonction utilisée, vous pouvez récupérer une condition d’erreur réelle (telle que # N / A ou #REF)

ou vous pouvez récupérer des données erronées. Dans les cas où une condition d’erreur est renvoyée, vous pouvez ajuster votre formule pour tenir compte de la possibilité de ne pas trouver de correspondance, de cette manière:

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

Il y a certaines choses que vous devez garder à l’esprit lorsque vous utilisez ces types de formules. D’abord et avant tout, la fiabilité des informations que vous récupérez dépendra en grande partie de la qualité des informations de votre liste de données. Si vos données contiennent des fautes d’orthographe, contiennent des blancs, sont triées dans un ordre étrange ou s’il y a plusieurs entrées pour la même personne, cela peut affecter ce que la formule renvoie.

Si vous n’êtes pas trop sûr de la qualité de vos données, vous voudrez peut-être simplement utiliser les capacités de filtrage d’Excel plutôt qu’une formule. Appliquez un filtre automatique et vous pouvez utiliser les deux premières colonnes de vos données pour choisir le prénom et le nom. Cela renverra alors tous les numéros de téléphone de la personne que vous sélectionnez. Il est très simple à faire et facilite la sélection des données dont vous avez besoin.

Enfin, vous devez comprendre qu’il existe d’autres approches que vous pouvez utiliser pour résoudre le problème. Par exemple, vous pouvez créer une formule qui utilise la fonction DGET, mais cela nécessiterait l’ajout d’un petit tableau de critères à votre feuille de calcul ou classeur. Comme Kimm a précisé qu’elle ne pouvait pas ajouter de résultats intermédiaires dans une feuille de calcul, une décision éditoriale a été prise de ne pas inclure la fonction DGET comme solution car cela nécessiterait l’ajout du tableau des critères.

En outre, si vous êtes à l’aise avec l’utilisation des macros, vous pouvez également créer une fonction définie par l’utilisateur qui examinerait les données et renverrait le numéro de téléphone demandé. L’avantage d’utiliser une telle approche est qu’elle vous offre une plus grande flexibilité dans le traitement des informations que la fonction renvoie réellement.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (10478) s’applique à Microsoft Excel 97, 2000, 2002 et 2003.

Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Pulling_a_Phone_Number_with_a_Known_First_and_Last_Name [Extraction d’un numéro de téléphone avec un prénom et un nom connus].