Formule générique
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Sommaire
Pour rechercher une valeur en faisant correspondre plusieurs colonnes, vous pouvez utiliser une formule matricielle basée sur MMULT, TRANSPOSE, COLUMN et INDEX. Dans l'exemple illustré, la formule en H4 est:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
où "noms" est la plage nommée C4: E7 et "groupes" est la plage nommée B4: B7. La formule renvoie le groupe auquel appartient chaque nom.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec la touche Ctrl + Entrée.
Explication
En travaillant de l'intérieur vers l'extérieur, les critères logiques utilisés dans cette formule sont:
--(names=G4)
où names est la plage nommée C4: E7. Cela génère un résultat TRUE / FALSE pour chaque valeur dans les données, et le double négatif contraint les valeurs TRUE FALSE à 1 et 0 pour donner un tableau comme celui-ci:
(0,0,0;1,0,0;0,0,0;0,0,0)
Ce tableau est de 4 lignes par 3 colonnes, correspondant à la structure des «noms».
Un deuxième tableau est créé avec cette expression:
TRANSPOSE(COLUMN(names)^0))
La fonction COLUMN est utilisée pour créer un tableau numérique avec 3 colonnes et 1 ligne, et TRANSPOSE convertit ce tableau en 1 colonne et 3 lignes. Élever à la puissance de zéro convertit simplement tous les nombres du tableau en 1. La fonction MMULT est ensuite utilisée pour effectuer la multiplication matricielle:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
et le résultat entre dans la fonction MATCH en tant que tableau, avec 1 comme valeur de recherche:
MATCH(1,(0;1;0;0),0)
La fonction MATCH renvoie la position de la première correspondance, qui correspond à la ligne de la première ligne correspondante répondant aux critères fournis. Ceci est alimenté dans INDEX comme numéro de ligne, avec la plage nommée «groupes» comme tableau:
=INDEX(groups,2)
Enfin, INDEX renvoie "Bear", le groupe auquel Adam appartient.
Le littéral contient pour les critères
Pour vérifier des valeurs de texte spécifiques au lieu d'une correspondance exacte, vous pouvez utiliser les fonctions ISNUMBER et SEARCH ensemble. Par exemple, pour faire correspondre les cellules contenant "pomme", vous pouvez utiliser:
=ISNUMBER(SEARCH("apple",data))
Cette formule est expliquée ici.
