Formule générique
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Sommaire
Pour comparer deux listes et extraire les valeurs manquantes d'une liste à l'autre, vous pouvez utiliser une formule matricielle basée sur INDEX et MATCH. Dans l'exemple illustré, la dernière valeur de la liste B se trouve dans la cellule D11. La formule en D12, recopiée, est:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
où "complet" est la plage nommée B5: B15.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Explication
En travaillant de l'intérieur vers l'extérieur, le noyau de cette formule est l'expression MATCH interne:
ISNA(MATCH(complete,$D$5:D11,0)
Ici, la fonction MATCH est utilisée pour comparer toutes les valeurs "complètes" avec la liste partielle. La plage nommée «complete» est utilisée pour les valeurs de recherche et la liste partielle est utilisée comme tableau de recherche. Notez, cependant, que la liste partielle est entrée comme une plage de développement qui se termine «une cellule au-dessus» de la cellule de formule. Cela permet à la liste partielle de s'étendre pour inclure de nouvelles valeurs telles qu'elles apparaissent sous la liste d'origine.
Le résultat de MATCH est un tableau de nombres et d'erreurs # N / A, où les nombres représentent les valeurs de la liste complète qui existent dans la liste partielle; et les erreurs représentent des valeurs manquantes:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
La fonction ISNA est utilisée pour convertir ces résultats en un tableau de valeurs TRUE et FALSE. Dans ce tableau, TRUE correspond aux valeurs manquantes et FALSE correspond aux valeurs existantes:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
La fonction ISNA renvoie ce tableau à la correspondance externe en tant que tableau de recherche. La fonction MATCH renvoie toujours la première correspondance trouvée, donc match retournera la position (ligne) de la première valeur manquante trouvée. Ce résultat est renvoyé à INDEX comme numéro de ligne, avec la plage nommée «complete» fournie comme tableau.
Dans la cellule D12, la première valeur manquante trouvée est "kiwi" à la ligne 2, nous avons donc:
=INDEX(complete,2) // returns "kiwi"
Dans D13, "kiwi" est désormais inclus dans la référence en expansion, donc la première valeur manquante est "poire":
=INDEX(complete,5) // returns "pear"
Etc. Une fois que toutes les valeurs manquantes ont été ajoutées, la formule renverra l'erreur # N / A.
