XL 2019 Fonction SI + CHERCHE avec une liste de mots

[RodMalvo_Ex]

Bonjour à tous,

Je suis dans une impasse depuis quelques heures, je souhaiterais utiliser une formule me renvoyant un mot trouvé dans la cellule contenant une phrase.
J'ai des cellules contenant du texte (une phrase) et systématiquement un nom prénom à la fin ou au milieu du texte. Dans une autre feuille j'ai 2 colonnes (une nom et une autre prénom).

Je souhaiterais qu'avec la liste des noms je puisse contrôler pour chaque cellule si un des noms de la liste est dans la cellule avec le texte et si oui que le nom soit extrait et soit le résultat de ma formule.

J'ai déjà essayé avec des imbrications de SI, SIERREUR et CHERCHE mais ayant une trentaine de noms la formule serait extrêmement longue.

Voici ma formule pour seulement 2 noms :
SI(SIERREUR(CHERCHE("CHRISTEL";$F4);-1)>0;STXT(F4;CHERCHE("CHRISTEL";$F4);8);SI(SIERREUR(CHERCHE("PRISCILLE";$F4);-1)>0;STXT(F4;CHERCHE("PRISCILLE";$F4);9);""))

J'ai aussi essayé SOMMEPROD qui me renvoi si un ou plusieurs noms sont trouvés dans la chaîne de caractère mais ne me renvoi pas les noms trouvés :
=SOMMEPROD((NB.SI(E32;"*"&$E$4:$E$28&"*"))*1)

Si vous avez une solution (hors VBA) pour ne pas avoir a faire une formule de 50 lignes je suis preneur !

Merci d'avance

 

[vgendron]

Bonjour

un fichier exemple serait le bienvenu

 

[RodMalvo_Ex]

Bonjour

un fichier exemple serait le bienvenu

Yes voici un fichier avec un exemple

 

[JHA]

Bonjour à tous,

Tu peux essayer cette formule matricielle

=INDEX(Liste;EQUIV(VRAI;ESTNUM(TROUVE(Liste;E4));0);1)

JHA

 

[RodMalvo_Ex]

Bonjour à tous,

Tu peux essayer cette formule matricielle

=INDEX(Liste;EQUIV(VRAI;ESTNUM(TROUVE(Liste;E4));0);1)

JHA

Yeeess ça marche ! Merci le King suprême !

 

[JHA]

Bonjour à tous,

Une formule pour trouver le nom par rapport au prénom de la phrase.

=SI(ESTNUM(EQUIV(VRAI;ESTNUM(TROUVE(INDEX(ListeBis;;2);$E4));0));INDEX(ListeBis;EQUIV(VRAI;ESTNUM(TROUVE(INDEX(ListeBis;;2);$E4));0);1);"")

Validation matricielle

JHA

 

[RodMalvo_Ex]

Merci tu régales vraiment

Bonjour à tous,

Une formule pour trouver le nom par rapport au prénom de la phrase.

=SI(ESTNUM(EQUIV(VRAI;ESTNUM(TROUVE(INDEX(ListeBis;;2);$E4));0));INDEX(ListeBis;EQUIV(VRAI;ESTNUM(TROUVE(INDEX(ListeBis;;2);$E4));0);1);"")

Validation matricielle

JHA

 

[RodMalvo_Ex]

Bonjour

Je reviens car la formule a atteint ses limites...
Ce qu'il se passe c'est que la formule s'arrête quand elle trouve un nom dans la liste mais il se peut que dans la liste j'ai 2 ou plusieurs personnes avec le même prénom... il y a-t-il un moyen de continuer à chercher dans la liste si ça renvoie une erreur avec l'index ?

N'hésitez pas si vous avez besoin d'un exemple...
Merci

 

[JHA]

Bonjour à tous,

A essayer

JHA

 

[RodMalvo_Ex]

Bonjour,

Merci pour votre fichier.
Je vous ai joint le fichier avec la formule que j'utilise, le problème est finalement le suivant avec ma formule :
Si un prénom est aussi en tant que nom chez une autre personne --> Mon recherchex me renvoi 2x le prénom et ne retrouve pas le bon nom de famille...

C'est difficile à expliquer, avec le fichier c'est peut être plus simple

 

[JHA]

Bonjour à tous,

A essayer

JHA