Nous pouvons appliquer le concept de recherche d’une valeur et de décalage des cellules dans une colonne ou une rangée dans Google Sheets.
Dans ce post, nous expliquerons comment retourner une valeur à gauche ou à droite d’une valeur de recherche. Le résultat peut provenir de la même rangée ou d’une autre rangée.
Si vous avez besoin de rechercher dans une colonne, veuillez consulter les deux tutoriels ci-dessous.
- Comment filtrer la ligne suivante selon la ligne de critères de filtre dans Google Sheets.
- Comment décaler une correspondance en utilisant la requête dans Google Sheets.
Pour rechercher une valeur, nous n’utiliserons pas la fonction RECHERCHE. Au lieu de cela, nous utiliserons la fonction FILTRE.
Avec FILTRE, nous pouvons facilement rechercher une valeur et décaler les cellules à gauche ou à droite de la valeur trouvée dans Google Sheets. Voici comment.
Voici un exemple de données juste pour vous donner une idée.
Parfois, nous organisons les données comme ci-dessus. Nous avons les noms des mois dans une colonne et les valeurs correspondantes dans la colonne suivante.
Supposons que je veuille rechercher la valeur « A » dans la rangée n°2 et retourner son en-tête.
Dans le cas habituel, le résultat serait vide car la cellule F1 est vide.
Ce que je veux, c’est décaler d’une cellule vers la gauche et retourner « Mar » de la cellule E1.
Dans la rangée suivante, c’est-à-dire la rangée n°3, je veux répéter la même opération et retourner la valeur « Feb » et ainsi de suite.
J’ai présenté mon résultat attendu dans les cellules J2:J7 (voir l’image n°1 ci-dessus).
Dans l’exemple ci-dessus, ma formule renvoie la valeur de la rangée d’en-tête après avoir décalé d’une cellule.
Vous pouvez choisir la même rangée de recherche au lieu de la rangée d’en-tête à décaler et retourner la valeur.
Voyons maintenant comment rechercher une valeur et décaler les cellules à gauche ou à droite dans Google Sheets.
Rechercher une valeur et décaler les cellules vers la gauche dans Google Sheets
Comme je l’ai mentionné au début, la fonction simple à utiliser ici est FILTRE.
Nous voulons la valeur décalée de la rangée n°1.
Si nous ne voulons pas décaler les cellules à gauche ou à droite de la clé de recherche, nous pouvons utiliser la formule suivante :
=filtre($A$1:$H$1,A2:H2="A")
Cela renverra une cellule vide. Si vous entrez une valeur dans la cellule F1, la formule renverra cette valeur.
Nous voulons décaler d’une cellule vers la gauche. Nous pouvons donc modifier FILTRE comme suit :
=filtre({0,$A$1:$H$1},{A2:H2,0}="A")
Insérez cette formule dans la cellule J2 (comme sur l’image n°1 ci-dessus). Ensuite, faites glisser cette formule vers le bas.
De cette manière, nous pouvons rechercher une valeur dans chaque rangée et décaler les cellules à gauche de la valeur trouvée dans la rangée d’en-tête.
Si vous souhaitez décaler de deux cellules vers la gauche, insérez un autre zéro dans les deux tableaux (tableau 1 est la plage et tableau 2 est la condition) dans la formule :
=filtre({0,0,$A$1:$L$1},{A2:L2,0,0}="A")
Rechercher une valeur et décaler les cellules vers la droite dans Google Sheets
Ici, il n’y a pas beaucoup de changements. Nous devons simplement former les deux tableaux (plage et condition) dans la formule différemment.
Dans la formule ci-dessus, les zéros sont ajoutés en préfixe à la rangée de résultats (plage) et en suffixe à la rangée de recherche (condition).
Voici la formule correspondante :
=filtre({$A$1:$L$1,0,0},{0,0,A2:L2}="A")
Cette formule recherche la valeur « A » dans la rangée n°2 et décale de 2 cellules vers la droite dans la première rangée (en-tête).
Comment supprimer l’erreur #N/A et gérer plusieurs valeurs
Veuillez consulter la capture d’écran ci-dessus.
La formule renvoie deux valeurs dans la rangée n°4 car il existe plusieurs correspondances dans la recherche.
Si vous voulez uniquement la première correspondance, utilisez la fonction INDEX comme suit :
=index(filtre({$A$1:$L$1,0,0},{0,0,A2:L2}="A"),0,1)
Si vous voulez toutes les valeurs, utilisez TEXTJOIN :
=textjoin(", ",true,filtre({$A$1:$L$1,0,0},{0,0,A2:L2}="A"))
Et que faire de l’erreur #N/A ?
Il suffit d’envelopper la formule avec SI.NA :
Utilisez les formules ci-dessus pour rechercher une valeur et décaler les cellules dans Google Sheets.
La version HLOOKUP
Nous pouvons remplacer la formule FILTRE par un HLOOKUP. Mais cela ne renverra pas plusieurs valeurs décalées.
Je préfère FILTRE à HLOOKUP car la première est plus facile à comprendre et peut renvoyer plusieurs valeurs décalées.
Si vous êtes curieux et souhaitez apprendre à utiliser HLOOKUP dans le cas ci-dessus, voici un exemple :
Utilisons HLOOKUP pour rechercher la valeur « A » et décaler de deux cellules vers la gauche dans Google Sheets :
=hlookup("A",{{A2:L2,0,0};{0,0,$A$1:$L$1}},2,0)
Comme vous pouvez le constater, la clé du décalage est la modification de la plage HLOOKUP en ajoutant des colonnes supplémentaires à droite (rangée de recherche) et à gauche (rangée de résultat).
En résumé, avec HLOOKUP :
-
Pour rechercher et décaler de deux cellules vers la gauche, modifiez la première rangée dans la plage en ajoutant deux cellules supplémentaires et la deuxième rangée en préfixant deux cellules supplémentaires.
-
Pour rechercher et décaler de deux cellules vers la droite, modifiez la première rangée dans la plage en préfixant deux cellules supplémentaires et la deuxième rangée en suffixant deux cellules supplémentaires.
Voilà, c’est tout. Amusez-vous bien !
Retrouvez plus d’articles et de conseils sur bolamarketing.com.
