Il existe une solution parfaite pour ces situations où il faut compléter un tableau à partir d’informations venant d’un autre: INDEX-EQUIV.
INDEX-EQUIV est une combinaison des formules INDEX et EQUIV sur Excel et Google Sheets. Cette technique est plus flexible que d’autres comme RECHERCHEV.
Note: Sur Excel, les paramètres des fonctions sont séparés par des virgules. Sur Google Sheets, ils sont séparés par des points-virgules.
INDEX
La fonction INDEX renvoie le contenu d’une cellule selon ses coordonnées.
La syntaxe pour INDEX est la suivante:
=INDEX( [référence] ; [ligne] ; [colonne] )
La référence délimite le tableau dans lequel une cellule va être sélectionnée.
La ligne et la colonne représentent les coordonnées de la cellule à sélectionner dans le tableau de référence.
Voici un exemple:
La zone sélectionnée en référence est A2:C5. Notez que les numéros de ligne et de colonne sont relatifs à cette sélection plutôt qu’à la feuille entière. La fonction prend les donnés de la cellule (2,1) et nous renvoie le nom William.
EQUIV
La fonction EQUIV recherche une donnée dans une plage et renvoie sa position.
La syntaxe pour EQUIV est la suivante:
=EQUIV( [clé de recherche]; [plage] ; [type de recherche] )
La clé de recherche est la donnée à chercher. Elle peut être un mot ou la référence d’une cellule. Cette clé est comparée aux cellules de la plage sélectionnée. Pour le type de recherche, on utilise 0.
Note: Pour le type de recherche, 1 et -1 peuvent être utilisés dans les plages triées par ordre croissant et décroissant. Mais dans la plupart des cas, on utilise 0.
Voici un exemple:
Notez que la plage de recherche ne peut être qu’une rangée verticale ou une ligne horizontale, mais pas les deux.
La fonction renvoie 5. Le nom William est donc en 5ème position dans la plage sélectionnée.
INDEX-EQUIV
Maintenant que les deux fonctions sont claires individuellement, c’est l’heure de les combiner.
Le deuxième paramètre de la fonction INDEX demande la position verticale de la cellule à renvoyer. La fonction EQUIV nous renvoie la position verticale de la cellule qui correspond à sa clé de recherche. Vous voyez sûrement où je veux en venir.
Voyez ça directement en exemple ci-dessous:
Note: Le signe $ a été ajouté ou c’est nécessaire pour qu’on puisse copier coller la formule sans fausser les données.
Dans cet exemple, on veut récupérer l’âge dans le tableau 2 pour notre première personne, Juan, et la mettre dans le tableau 1.
On insère =INDEX( $A$8:$B$16 ; [ ligne ] ; 2 ) afin d’avoir le tableau 2, colonne 2, où l’on a les âges. Dans le paramètre ligne, on va mettre une fonction EQUIV qui donnera le numéro de ligne correspondant.
Pour la fonction EQUIV, on insère A2 pour Juan dans le tableau 1, et on le recherche dans le tableau 2. On a donc EQUIV(A2;$A$8:$A$16;0).
Pour combiner ces deux fonctions, on insère EQUIV(A2;$A$8:$A$16;0) dans le paramètre [ligne] dans =INDEX( $A$8:$B$16 ; [ ligne ] ; 2 ).
Voici donc le résultat final: =INDEX($A$8:$B$16 ; EQUIV(A2;$A$8:$A$16;0) ; 2)
Il suffit ensuite de copier cette formule sur les autres cellules du tableau 1 pour avoir les âges des 3 autres personnes.
Conclusion
Au début, cette technique demande un peu de compréhension et de pratique, mais une fois qu’on s’y habitue, elle permet de raccorder deux tableaux automatiquement. Et elle prend encore plus de valeur dans les situations ou l’on traite de larges plages de données.
Une fois maîtrisée, INDEX-EQUIV vous place au niveau avancé d’Excel et Sheets!
Références Google sheets pour INDEX et MATCH
Références Excel pour INDEX et MATCH