Formule générique
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Sommaire
Pour trouver la correspondance la plus proche dans les données numériques, vous pouvez utiliser INDEX et MATCH, à l'aide des fonctions ABS et MIN. Dans l'exemple illustré, la formule en F5, copiée vers le bas, est:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
où voyage (B5: B14) et coût (C5: C14) sont des plages nommées.
Dans F5, F6 et F7, la formule renvoie le trajet le plus proche en coût à 500, 1000 et 1500, respectivement.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée, sauf dans Excel 365.
Explication
Au fond, il s'agit d'une formule INDEX et MATCH: MATCH localise la position de la correspondance la plus proche, alimente la position à INDEX et INDEX renvoie la valeur à cette position dans la colonne Trip. Le travail acharné se fait avec la fonction MATCH, qui est soigneusement configurée pour correspondre à la "différence minimale" comme ceci:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
En prenant les choses étape par étape, la valeur de recherche est calculée avec MIN et ABS comme ceci:
MIN(ABS(cost-E5)
Tout d'abord, la valeur dans E5 est soustraite du coût de la plage nommée (C5: C14). Il s'agit d'une opération de tableau, et comme il y a 10 valeurs dans la plage, le résultat est un tableau avec 10 valeurs comme ceci:
(899;199;250;-201;495;1000;450;-101;500;795)
Ces nombres représentent la différence entre chaque coût dans C5: C15 et le coût dans la cellule E5, 700. Certaines valeurs sont négatives car un coût est inférieur au nombre dans E5. Pour convertir des valeurs négatives en valeurs positives, nous utilisons la fonction ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
qui renvoie:
(899;199;250;201;495;1000;450;101;500;795)
Nous recherchons la correspondance la plus proche, nous utilisons donc la fonction MIN pour trouver la plus petite différence, qui est 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Cela devient la valeur de recherche dans MATCH. Le tableau de recherche est généré comme précédemment:
ABS(cost-E5) // generate lookup array
qui renvoie le même tableau que nous avons vu précédemment:
(899;199;250;201;495;1000;450;101;500;795)
Nous avons maintenant ce dont nous avons besoin pour trouver la position de la correspondance la plus proche (plus petite différence), et nous pouvons réécrire la partie MATCH de la formule comme ceci:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Avec 101 comme valeur de recherche, MATCH renvoie 8, puisque 101 est en 8ème position dans le tableau. Enfin, cette position est introduite dans INDEX comme argument de ligne, avec le trajet de plage nommé comme tableau:
=INDEX(trip,8)
et INDEX renvoie le 8ème voyage de la plage, "Espagne". Lorsque la formule est copiée dans les cellules F6 et F7, elle trouve la correspondance la plus proche de 1000 et 1500, "France" et "Thaïlande" comme indiqué.
Remarque: s'il y a égalité, cette formule renverra la première correspondance.
Avec XLOOKUP
La fonction XLOOKUP fournit un moyen intéressant de résoudre ce problème, car un type de correspondance de 1 (correspondance exacte ou suivant le plus grand) ou -1 (correspondance exacte ou suivant le plus petit) ne nécessite pas le tri des données. Cela signifie que nous pouvons écrire une formule comme celle-ci:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Comme ci-dessus, nous utilisons la valeur absolue de (cost-E5) pour créer un tableau de recherche:
(899;199;250;201;495;1000;450;101;500;795)
Ensuite, nous configurons XLOOKUP pour rechercher zéro, avec le type de correspondance défini sur 1, pour la correspondance exacte ou la plus grande suivante. Nous fournissons le trajet de plage nommé comme tableau de retour, donc le résultat est "Espagne" comme auparavant.
