Formule générique
=MATCH(1,EXACT(LEFT(A1,255),LEFT(rng,255))*EXACT(MID(A1,256,255),MID(rng,256,255)),0)
Sommaire
Pour faire correspondre du texte de plus de 255 caractères avec la fonction MATCH, vous pouvez utiliser les fonctions LEFT, MID et EXACT pour analyser et comparer le texte, comme expliqué ci-dessous. Dans l'exemple illustré, la formule en G5 est:
=MATCH(1,EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255)),0)
où data est la plage nommée B5: B15.
Remarque: cette formule effectue une comparaison sensible à la casse.
Explication
La fonction MATCH a une limite de 255 caractères pour la valeur de recherche. Si vous essayez d'utiliser du texte plus long, MATCH renverra une erreur #VALUE.
Pour contourner cette limite, vous pouvez utiliser la logique booléenne et les fonctions LEFT, MID et EXACT pour analyser et comparer du texte.
La chaîne que nous testons dans la cellule E5 contient 373 caractères comme suit:
Lorem ipsum dolor amet a mis un oiseau sur le fonds de fiducie listicle, licorne vaporware droits de vélo vous n'avez probablement pas entendu parler de leur moustache. Forage helvetica sémiotique croustillante en fait héritage. Tumblr poutine licorne godard essaye-dur avant de se vendre narval méditation kitsch gilet fixie twee littéralement à capuche rétro. Sac messager enfer d'artisan croustillant de jus vert
Au fond, il ne s'agit que d'une formule MATCH, configurée pour rechercher 1 en mode de correspondance exacte:
=MATCH(1,array,0)
Le tableau dans la formule ci-dessus contient uniquement des 1 et des 0, et les 1 représentent le texte correspondant. Ce tableau est construit par l'expression suivante:
EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255))
Cette expression elle-même comporte deux parties. Sur la gauche, nous avons:
EXACT(LEFT(E5,255),LEFT(data,255)) // compare first 255 chars
Ici, la fonction GAUCHE extrait les 255 premiers caractères de E5 et de toutes les cellules des données de plage nommées (B5: B15). Étant donné que les données contiennent 11 chaînes de texte, LEFT générera 11 résultats.
La fonction EXACT compare ensuite la chaîne unique de E5 à toutes les 11 chaînes renvoyées par LEFT. EXACT renvoie 11 résultats dans un tableau comme celui-ci:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
À droite, nous avons une autre expression:
EXACT(MID(E5,256,255),MID(data,256,255) // compare next 255 chars
C'est exactement la même approche que celle utilisée avec LEFT, mais ici nous utilisons la fonction MID pour extraire les 255 caractères suivants du texte. La fonction EXACT renvoie à nouveau 11 résultats:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Lorsque les deux tableaux ci-dessus sont multipliés l'un par l'autre, l'opération mathématique contraint les valeurs TRUE FALSE en 1 et 0. En suivant les règles de l'arithmétique booléenne, le résultat est un tableau comme celui-ci:
(0;0;0;0;0;0;0;0;0;1;0)
qui est renvoyé directement à MATCH en tant que tableau de recherche. La formule peut maintenant être résolue en:
=MATCH(1,(0;0;0;0;0;0;0;0;0;1;0),0)
La fonction MATCH effectue une correspondance exacte et renvoie un résultat final de 10, qui représente la dixième chaîne de texte dans B5: B15.
Remarque: la longueur du texte indiquée dans l'exemple est calculée avec la fonction LEN. Il apparaît à titre indicatif seulement.
Option insensible à la casse
La fonction EXACT est sensible à la casse, donc la formule ci-dessus respectera la casse.
Pour effectuer une correspondance insensible à la casse avec du texte long, vous utilisez les fonctions ISNUMBER et SEARCH comme suit:
=MATCH(1,ISNUMBER(SEARCH(LEFT(E5,255),LEFT(data,255)))*ISNUMBER(SEARCH(MID(E5,256,255),MID(data,256,255))),0)
La structure globale de cette formule est identique à l'exemple ci-dessus, mais la fonction SEARCH est utilisée à la place de EXACT pour comparer le texte (expliqué en détail ici).
Contrairement à EXACT, la fonction SEARCH prend également en charge les caractères génériques.
Avec XMATCH
La fonction XMATCH n'a pas la même limite de 255 caractères que MATCH. Pour effectuer une correspondance similaire sur du texte long avec XMATCH, vous pouvez utiliser la formule beaucoup plus simple ci-dessous:
=XMATCH(E5,data)
Remarque: XMATCH prend en charge les caractères génériques, mais n'est pas sensible à la casse.
