Récupérer des informations entre deux tableaux

Rechercher des informations dans un classeur A pour les afficher dans un classeur B ayant une ou plusieurs colonnes communes est souvent très utile, mais plus ou moins complexe en fonction de l’unicité des données. En effet si on utilise une colonne de type identifiant (un numéro de sécurité sociale, une adresse mail…) la recherche pourra se faire en cherchant dans une seule colonne, mais si on utilise comme données communes un nom et un prénom par exemple, il faudra vérifier 2 colonnes et la formule se complexifie. On essaiera ici de présenter les différents cas.

Valeur commune unique (identifiant)

Cas où la valeur commune est dans une seule colonne du fichier source

On peut alors utiliser la fonction RECHERCHEV comme indiqué ici :

Fonction RECHERCHEV

Cependant cette fonction nécessite que la valeur recherchée soit dans la première colonne de la matrice passée en paramètre. Cela n’est pas toujours possible et dans ce cas on procédera comme ci-après.

Cas où la valeur commune peut se trouver dans plusieurs colonnes du fichier source

On traitera avec un exemple pour une meilleure compréhension.

Situation

On a deux classeurs ou deux feuilles de calcul. Dans le premier on a une liste de personnes avec leurs nom, prénom, adresse mail. Dans le second on a ces mêmes informations avec en plus l’UAI et d’autres informations qui nous intéressent, mais plusieurs personnes de la liste sont sur une même ligne (car même établissement).

Comment récupérer automatiquement les informations pour chaque personne, l’UAI en particulier ?

Données

Fonctions utilisées

Fonction EQUIV

Le résultat est un nombre entier naturel (= numéro de ligne).

• Premier paramètre : valeur recherchée
• Deuxième paramètre : colonne dans laquelle chercher (ne fonctionne pas avec une matrice)
• Troisième paramètre : 0 (zéro) si la colonne n’est pas triée (1 pour tri croissant / -1 pour tri décroissant)

Fonction INDEX

Le résultat est la valeur d’une cellule dont on indique le numéro de ligne et le numéro de colonne.

• Premier paramètre : matrice dans laquelle on cherche
• Deuxième paramètre : numéro de ligne (dans la matrice)
• Troisième paramètre : numéro de colonne (dans la matrice)

Formule possible si une ligne de la feuille 2 correspond à une seule ligne de la feuille 1

Dans la cellule D2 de la feuille 1 :

=INDEX($Feuille2.$A$2:$G$32;EQUIV($C2;$Feuille2.$D$2:$D$32;0);1)

La fonction EQUIV récupère le numéro de la ligne où on trouve C2 (ici le mail) dans la colonne D de la Feuille2, puis la fonction INDEX récupère la valeur qui est dans la première colonne de cette ligne (ici l’UAI).

Formule correspondant à l’exemple

Dans la cellule D2 de la feuille 1 :

=INDEX($Feuille2.$A$2:$G$32;EQUIV(1;($Feuille2.$D$2:$D$32=$C2)+($Feuille2.$G$2:$G$32=$C2)+($Feuille2.$J$2:$J$32=$C2);0);1)

Comme le mail peut se trouver dans trois colonnes différentes (ici D, G et J), mais dans une seule de ces trois colonnes, on va utiliser la fonction EQUIV un peu différemment.

On cherche le nombre 1 dans la colonne constituée des résultats de la somme des tests vérifiant si C2 est dans chacune de ces colonnes.

• Si C2 n’est pas sur la ligne, le résultat sera 0 (0+0+0).
• Si C2 est sur la ligne, quelle que soit la colonne qui le contient, le résultat sera 1 (1+0+0 ou 0+1+0 ou 0+0+1).

Résultat

Valeurs communes à chercher sur 2 colonnes ou plus

L’exemple présenté ici se base sur la recherche de « nom » et « prénom » (soit 2 valeurs à chercher), mais la méthode est identique pour 3 valeurs ou plus.

Formule classique

Cette fois on veut récupérer des données en utilisant deux colonnes de recherche. La première feuille contient une liste de personnes avec leurs nom et prénom dans deux colonnes. Dans la deuxième feuille se trouvent les mêmes personnes, mais ni le nom, ni le prénom ne sont des identifiants uniques. Il faut donc vérifier que les deux correspondent.

Dans la cellule C2 de la feuille 1 :

=INDEX($Feuille2.$A$2:$D$6; EQUIV(1;($Feuille2.$B$2:$B$6=$A2)*($Feuille2.$C$2:$C$6= $B2);0);4)

Dans la cellule D2 de la feuille 1 :

=INDEX($Feuille2.$A$2:$D$6; EQUIV(1;($Feuille2.$B$2:$B$6=$A2)*($Feuille2.$C$2:$C$6= $B2);0);1)

On cherche le nombre 1 dans la colonne constituée des résultats du produit des tests vérifiant si les nom ET prénom correspondent.

• Si ni le nom, ni le prénom ne figure sur la ligne, le résultat sera 0 (0x0).
• Si seulement le nom ou seulement le prénom est sur la ligne, le résultat sera 0 (1×0 ou 0x1).
• Si les nom ET prénom correspondent, le résultat sera 1 (1×1).

Formule matricielle

On peut reprendre ce 2e exemple en utilisant des fonctions matricielles.

Pour saisir des fonctions matricielles, on procède comme d’habitude, mais on valide par la combinaison Ctrl + MAJ + Entrée.

Attention si vous appuyez simplement sur Entrée, vous ne pourrez plus modifier. Dans ce cas, il faudra modifier la formule en supprimant la dernière parenthèse et en validant à nouveau par Ctrl + MAJ + Entrée.

Autre solution : ouvrir l’assistant fonctions et cocher « Matrice » en bas à gauche.

Si vous avez validé correctement, des accolades vont s’ajouter en début et fin de formule.

En reprenant l’exemple ci-dessus, on cherche le nom contenu dans la cellule A2 et le prénom dans la cellule B2 (de la feuille 1) dans les colonnes B et C de la feuille 2.

Dans la cellule C2 de la feuille 1 :

{=INDEX($Feuille2.$A$2:$D$6; EQUIV($A2&$B2;$Feuille2.$B$2:$B$6&$Feuille2.$C$2:$C$6;0);4)}

Dans la cellule D2 de la feuille 1 :

{=INDEX($Feuille2.$A$2:$D$6; EQUIV($A2&$B2;$Feuille2.$B$2:$B$6&$Feuille2.$C$2:$C$6;0);1)}

Sources

• https://forum.openoffice.org/fr/forum/viewtopic.php?f=4&t=17301
• https://www.formuleexcel.com/faire-une-recherche-avec-plusieurs-criteres-sur-excel/