Dans cet article, nous allons apprendre comment trouver la correspondance la plus proche supérieure ou égale à la clé de recherche dans la fonction Vlookup de Google Sheets. C’est une astuce simple mais qui vaut la peine d’être essayée, car vous pouvez parfois remplacer les formules IF emboîtées par la fonction Vlookup de cette manière.
Comportement par défaut de Vlookup dans une plage triée
Dans une plage triée, le comportement par défaut de Vlookup est le suivant.
Nous pouvons utiliser Vlookup pour trouver la correspondance la plus proche inférieure ou égale à la clé de recherche dans une plage de données triées. Pour cela, il suffit de ne pas utiliser l’argument facultatif « triée » dans la formule.
Veuillez consulter l’exemple ci-dessous, qui explique le fonctionnement de la fonction de recherche verticale dans une plage triée.
Veuillez trouver la formule suivante dans la cellule G3.
=VLOOKUP(F3,B3:C7,2)
Vous connaissez peut-être déjà cette formule, mais laissez-moi vous l’expliquer.
Sytnax: VLOOKUP(clé_de_recherche, plage, index, [triée])
clé_de_recherche: 25 (F3)
plage: B3:C7
index: 2
triée: omis
La clé de recherche est 25, qui se trouve dans la cellule F3. Comme elle n’est pas disponible dans la première colonne de la plage Vlookup, la formule recherche la correspondance la plus proche inférieure ou égale à la clé de recherche, c’est-à-dire 24.
La colonne d’index dans la formule ci-dessus est 2, donc elle renvoie le nombre 4 de la colonne 2 de la plage. Jusqu’à présent, tout va bien. Mais je veux que la formule recherche la correspondance la plus proche supérieure ou égale à la clé de recherche dans Vlookup.
Selon l’exemple ci-dessus, je veux que la formule recherche la correspondance la plus proche, c’est-à-dire 32, et renvoie le nombre 6 de la colonne 2.
Quel est l’avantage de le faire ?
L’avantage de forcer la correspondance la plus proche supérieure ou égale à la clé de recherche dans Vlookup
Il existe des situations spécifiques où vous voulez rechercher la correspondance la plus proche supérieure ou égale à la clé de recherche. Habituellement, nous utilisons des tests logiques dans de tels cas.
Vous ne comprenez pas ? Ne vous inquiétez pas. Je vais expliquer davantage.
Lisons la première colonne dans le tableau ci-dessus comme suit dans la colonne 3.
Selon la colonne D, la valeur 17 dans A3 représente le groupe d’âge de 1 à 17 ans, 24 dans F4 représente le groupe d’âge de 17 à 24 ans, et ainsi de suite.
Si tel est le cas, la formule Vlookup ci-dessus ne remplit pas son objectif. Dans la cellule F3, j’ai mis 25 (âge) comme clé de recherche. Je veux donc que la formule renvoie la valeur du groupe d’âge de 24 à 32 ans, et non du groupe d’âge de 17 à 24 ans.
C’est là que réside l’intérêt d’utiliser la correspondance la plus proche supérieure ou égale à la clé de recherche dans Vlookup dans Google Sheets. Avant d’aborder cette astuce de formule, apprenons comment utiliser des formules IF emboîtées ici.
Méthode IF emboîtée
Nous pouvons utiliser IF ou IFS ici. Je préfère utiliser IF car il fonctionne mieux dans un tableau.
Contrairement à la recherche verticale ici, nous n’avons pas besoin du tableau. Entrez l’âge à rechercher dans la cellule F3. La formule se chargera du reste.
=if(F3<1,,if(F3<17,2,if(F3<24,4,if(F3<32,6,if(F3<41,8,if(F3<57,10,))))))
Ce qui précède est une alternative à la correspondance la plus proche supérieure ou égale à la clé de recherche dans Vlookup. Mais elle n’est pas aussi flexible que Vlookup. Pourquoi ?
J’y viendrai plus tard.
Correspondance la plus proche supérieure ou égale à la clé de recherche dans Vlookup
La fonction Vlookup est programmée pour fonctionner dans une plage triée comme suit.
Si la clé de recherche n’est pas disponible dans la colonne de recherche, la correspondance la plus proche inférieure à la clé de recherche dans la colonne de recherche est considérée. Nous ne pouvons pas changer cela.
Ce que nous pouvons faire, c’est modifier légèrement notre plage (tableau). En le faisant, nous pouvons nous assurer que la formule renvoie le résultat que nous souhaitons.
Solution
Déplacez virtuellement la première colonne de la plage d’une rangée vers le bas. Nous devons le faire sans compromettre la taille des deux colonnes de la plage. Voici comment.
Plage réelle : B3:C7
Plage modifiée (virtuelle) : {{1;B3:B7},{C3:C7;if(,,)}}
Pour déplacer B3:B7, la première colonne dans la plage, d’une ligne vers le bas, j’ai inséré une nouvelle cellule dans la première colonne de la plage virtuelle qui contient le nombre 1.
La deuxième colonne conserve sa position. Mais puisque la première colonne a maintenant une ligne supplémentaire, nous devons faire correspondre le nombre total de lignes de la deuxième colonne avec la première colonne.
Le if(,,) ajoute une cellule vide en bas de la deuxième colonne. Ainsi, les deux colonnes sont identiques en termes de nombre de lignes.
Maintenant, nous pouvons utiliser le Vlookup comme précédemment.
=ifna(Vlookup(F3,{{1;B3:B7},{C3:C7;if(,,)}},2))
De plus, j’ai utilisé la fonction IFNA pour renvoyer une cellule vide en cas d’erreur #N/A.
Vlookup ou IF emboîté ?
Voici une question légitime. Quelle formule est la plus flexible ?
Si les expressions logiques (ici le groupe d’âge) sont inférieures à 10, utilisez le IF emboîté ou IFS. Parce que vous n’aurez peut-être pas besoin de maintenir un tableau comme dans Vlookup.
Mais s’il y a plusieurs expressions logiques, il y a des chances que nous fassions des erreurs de syntaxe. Vous pourriez trouver ennuyeux de modifier la formule à l’avenir. Dans ce cas, optez pour Vlookup.
Il y a un autre avantage de Vlookup. C’est sa flexibilité.
Dans la formule IF emboîtée, nous avons prédéfini les valeurs à retourner. Dans Vlookup, nous pouvons changer le numéro d’index pour retourner des valeurs de n’importe quelle colonne dans le tableau.
C’est tout ce qu’il y a à savoir sur la correspondance la plus proche supérieure ou égale à la clé de recherche dans Vlookup dans Google Sheets.
Merci pour votre lecture. Profitez-en !
