La fonction de CORRESPONDANCE Excel semble être brisée. Description du problème et détails de l'enquête. Une explication ou une solution à cela?

Je suis un utilisateur Excel assez intensif et expérimenté, habitué à travailler avec des fonctions d'INDEX et de CORRESPONDANCE et avec des chaînes, donc je suis également habitué aux problèmes typiques avec les erreurs avec la fonction de CORRESPONDANCE en raison de la mise en forme différente des cellules, des espaces inaperçus et autres. Je connais également plusieurs solutions à ce genre de problèmes, telles que la conversion de texte en nombres et inversement en utilisant des fonctions TEXT() ou VALUE(), ou des approches plus simples telles que l'ajout de --, + 0, * 1, "" & ou d'autres opérations similaires pour convertir entre les formats. Ce que j'ai trouvé cette fois était un type d'erreur complètement différent qui dépend de la valeur sans changer le format.

Dans ma feuille de calcul, j'essayais juste de trouver quelle ligne d'une liste de valeurs dans 'cm' correspondait à une valeur obtenue ailleurs qui était en 'mm'. Par conséquent, je devais prétendument juste faire correspondre la valeur d'entrée divisée par 10. Cela a fonctionné au départ, jusqu'à ce que je réalise que cela ne fonctionnait pas pour certaines valeurs. Dans le premier image ci-dessous, on peut voir que match n'est pas en mesure de trouver la valeur '55.025' mais n'a aucun problème à trouver '64.025', même si les cellules suivantes de la liste contiennent simplement la valeur de B4 moins la valeur à côté d'elles sur la colonne A.

Après ne pas avoir pu tracer l'erreur, j'ai créé une feuille de calcul pour tester plusieurs combinaisons différentes et essayer d'identifier un modèle pour le comportement de la fonction de CORRESPONDANCE. Mais si quoi que ce soit, cela me confond davantage, montrant que ce n'est pas seulement, il se comporte différemment avec des valeurs différentes, mais que les différentes façons de transformer du texte en nombres ne se comportent pas non plus de manière cohérente. J'ai en fait identifié 6 scénarios possibles différents en fonction des valeurs réellement entrées.

Il est difficile de résumer les résultats, vous devez donc simplement examiner la feuille de calcul et y faire vos propres tests. Je vais juste expliquer sa structure montrée dans l'image ci-dessous.

La valeur de référence est celle de cellule D3 (grise), je viens d'y ajouter les décimales pour simplifier l'entrée des différentes valeurs entrées en B3 (bleu). Les cellules en orange et en jaune sur la colonne D sont différentes façons de formater la valeur du D3 (gris). C'est-à-dire :

D7   ="" & D3/10
D11  =--D7
D15  =D3/10

Correspondant au format texte, au texte converti en nombre et à un nombre brut, respectivement.

Les cellules vertes en bas de la colonne D ne sont que la liste des valeurs dans lesquelles la fonction de CORRESPONDANCE recherche, obtenue en soustrayant les valeurs de la colonne C à la valeur en D18.

Plus à droite (colonnes G: Q), la ligne 3 contient la CORRESPONDANCE de lignes devrait trouver la valeur recherchée dans le tableau de couleur verte de la colonne D. La ligne 5 répertorie le type de modification implémentée pour les valeurs de la colonne D. Au-dessus d'elles (lignes 6, 10, 14), il y a une vérification si ces valeurs sont égales à la cellule correspondante dans le tableau vert de la colonne D. Et au-dessous (lignes 8, 12, 16) sont les formules de CORRESPONDANCE réelles pour ces valeurs.

En dessous de tout cela, il y a un liste des différentes valeurs qui montrent chaque type de comportement, allant du fait d'être trouvé dans le tableau quelles que soient les modifications (tant qu'il s'agit de nombres), au fait de ne pas être trouvé du tout, avec plusieurs cas différents entre les deux.

L'onglet "Testing_b" est exactement le même que l'onglet "Testing", mais la valeur de la cellule D18, au lieu d'être collée à partir de ma feuille de calcul initiale, est saisie manuellement. Cela se traduit par un ensemble différent de comportements étranges, même si le collé à l'origine la valeur est censée être identique à celle de l'ancien onglet.

Vous pouvez télécharger la feuille de calcul ci-dessous et vérifier le comportement des différentes valeurs répertoriées lorsqu'elles sont saisies sur la cellule B3 (bleue). La plage valide est actuellement de 44 à 64.

Feuille de calcul MATCH_ERRORS.xlsx

J'espère que quelqu'un pourra trouver une sorte d'explication de tout cela.

* Modifier (07/05/2020):

Problèmes D'arrondissement Possibles

Concernant l'arrondissement problèmes en tant que cause sous-jacente possible soulignée par Justin Doward, j'ai remarqué qu'Excel semble utiliser un niveau de précision différent lors de la vérification si deux cellules sont égales que lors de la vérification si deux cellules correspondent (fonction de CORRESPONDANCE). Je viens de faire un test simple, et cette différence est d'un ou deux ordres de grandeur en fonction des valeurs comparées (image ci-dessous). Cela n'explique toujours pas pourquoi Excel modifie en quelque sorte la valeur réelle d'une cellule avec une approximation qui ne correspond alors pas à la valeur supposée de cette cellule. Les valeurs utilisées dans le problème initial ne sont même pas proches d'un niveau de précision auquel on pourrait s'attendre à des erreurs d'arrondi potentielles. Cela semble indiquer peut-être un problème sous-jacent encore plus grave.