Conseil
Essayez d’utiliser les nouvelles fonctions XLOOKUP et XMATCH , qui sont des versions améliorées des fonctions décrites dans cet article. Ces nouvelles fonctions fonctionnent dans n’importe quelle direction et renvoient des correspondances exactes par défaut, ce qui les rend plus faciles et plus pratiques à utiliser que leurs prédécesseurs.
Supposons que vous disposiez d’une liste de numéros de bureau et que vous deviez savoir quels employés sont présents dans chaque bureau. La feuille de calcul est énorme, vous pourriez donc penser que c’est une tâche difficile. C’est en fait assez facile à faire avec une fonction de recherche.
Les fonctions RECHERCHEV et RECHERCHEH , ainsi que les fonctions INDEX et EQUIV, comptent parmi les fonctions les plus utiles dans Excel.
Remarque
L’Assistant Liste de choix n’est plus disponible dans Excel.
Voici un exemple d’utilisation de la fonction RECHERCHEV.
=RECHERCHEV(B2;C2:E7;3;VRAI)
Dans cet exemple, B2 est le premier argument, un élément de données dont la fonction a besoin pour fonctionner. Pour RECHERCHEV, ce premier argument correspond à la valeur à rechercher. Cet argument peut être une référence de cellule ou une valeur fixe telle que « forgeron » ou 21 000. Le deuxième argument est la plage de cellules, C2- :E7, dans laquelle rechercher la valeur à rechercher. Le troisième argument est la colonne de cette plage de cellules qui contient la valeur que vous recherchez.
Le quatrième argument est facultatif. Entrez TRUE ou FALSE. Si vous entrez VRAI, ou que vous laissez l’argument vide, la fonction renvoie une correspondance approximative de la valeur que vous spécifiez dans le premier argument. Si vous entrez FAUX, la fonction correspondra à la valeur fournie par le premier argument. En d’autres termes, laisser le quatrième argument vide ou entrer VRAI vous offre plus de flexibilité.
Cet exemple vous montre le fonctionnement de cette fonction. Lorsque vous entrez une valeur dans la cellule B2 (premier argument), RECHERCHEV recherche les cellules de la plage C2 :E7 (2e argument) et renvoie la correspondance approximative la plus proche de la troisième colonne de la plage, colonne E (3e argument).
Le quatrième argument étant vide, la fonction renvoie une correspondance approximative. Si ce n’est pas le cas, vous devez entrer une des valeurs dans les colonnes C ou D pour obtenir un résultat.
Lorsque vous êtes à l’aise avec RECHERCHEV, la fonction RECHERCHEH est tout aussi facile à utiliser. Vous entrez les mêmes arguments, mais la recherche s’effectue dans des lignes au lieu de colonnes.
Utilisation des fonctions INDEX et EQUIV au lieu de la fonction RECHERCHEV
L’utilisation de la fonction RECHERCHEV présente certaines limites : la fonction RECHERCHEV ne peut rechercher une valeur que de gauche à droite. Cela signifie que la colonne contenant la valeur que vous recherchez doit toujours se trouver à gauche de la colonne contenant la valeur de retour. Maintenant, si votre feuille de calcul n’est pas conçue de cette façon, n’utilisez pas la fonction RECHERCHEV. Utilisez plutôt la combinaison des fonctions INDEX et EQUIV.
Cet exemple montre une petite liste dans laquelle la valeur recherchée, Chicago, ne figure pas dans la colonne la plus à gauche. Nous ne pouvons donc pas utiliser la fonction RECHERCHEV. Au lieu de cela, nous allons utiliser la fonction MATCH pour trouver Chicago dans la plage B1 :B11. Elle se trouve à la ligne 4. Ensuite, INDEX utilise cette valeur comme argument de recherche et trouve la population de Chicago dans la 4ème colonne (colonne D). La formule utilisée est affichée dans la cellule A14.
Découvrir
Si vous souhaitez expérimenter les fonctions de recherche avant de les essayer avec vos propres données, voici quelques exemples de données.
Exemple de RECHERCHEV au travail
Copiez les données suivantes dans une feuille de calcul vide.
Conseil
Avant de coller les données dans Excel, définissez la largeur des colonnes A à C à 250 pixels, puis cliquez sur Renvoyer à la ligne automatiquement (onglet Accueil , groupe Alignement ).
| Densité | Viscosité | Température |
|---|---|---|
| 0,457 | 3,55 | 500 |
| 0,525 | 3,25 | 400 |
| 0,606 | 2,93 | 300 |
| 0,675 | 2,75 | 250 |
| 0,746 | 2,57 | 200 |
| 0,835 | 2,38 | 150 |
| 0,946 | 2,17 | 100 |
| 1,09 | 1,95 | 50 |
| 1,29 | 1,71 | 0 |
| Formule | Description | Résultat |
| =RECHERCHEV(1;A2:C10;2) | Recherche, en correspondance proche, la valeur 1 dans la colonne A, trouve la valeur immédiatement inférieure ou égale à 1 dans la colonne A (0,946), puis renvoie la valeur de la même ligne dans la colonne B. | 2,17 |
| =RECHERCHEV(1;A2:C10;3;VRAI) | Recherche, en correspondance proche, la valeur 1 dans la colonne A, trouve la valeur immédiatement inférieure ou égale à 1 dans la colonne A (0,946), puis renvoie la valeur de la même ligne dans la colonne C. | 100 |
| =RECHERCHEV(0,7;A2:C10;3;FAUX) | Recherche, en correspondance exacte, la valeur 0,7 dans la colonne A. Étant donné que la colonne A ne contient aucune correspondance exacte, une erreur est renvoyée. | #N/A |
| =RECHERCHEV(0,1;A2:C10;2;VRAI) | Recherche, en correspondance proche, la valeur 0,1 dans la colonne A. Étant donné que 0,1 est une valeur inférieure à la valeur la plus petite de la colonne A, une erreur est renvoyée. | #N/A |
| =RECHERCHEV(2,A2:C10,2,VRAI) | Recherche, en correspondance proche, la valeur 2 dans la colonne A, trouve la valeur immédiatement inférieure ou égale à 2 dans la colonne A (1,29), puis renvoie la valeur de la même ligne dans la colonne B. | 1,71 |
Exemple HLOOKUP
Copiez toutes les cellules dans ce tableau, puis collez-le dans la cellule A1 d’une feuille de calcul vierge dans Excel.
Conseil
Avant de coller les données dans Excel, définissez la largeur des colonnes A à C à 250 pixels, puis cliquez sur Renvoyer à la ligne automatiquement (onglet Accueil , groupe Alignement ).
| Axes | Roulements | Boulons |
|---|---|---|
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| Formule | Description | Résultat |
| =RECHERCHEH("Axes"; A1:C4; 2; VRAI) | Recherche « Axes » dans la ligne 1 et renvoie la valeur de la ligne 2 qui est comprise dans la même colonne (colonne A). | 4 |
| =RECHERCHEH("Roulements"; A1:C4; 3; FAUX) | Recherche « Roulements » dans la ligne 1 et renvoie la valeur de la ligne 3 qui est comprise dans la même colonne (colonne B). | 7 |
| =RECHERCHEH("B"; A1:C4; 3; VRAI) | Recherche « B » dans la ligne 1 et renvoie la valeur de la ligne 3 qui est comprise dans la même colonne. Étant donné qu’une correspondance exacte de « B » est introuvable, la valeur la plus élevée de la ligne 1 qui est inférieure à « B » est utilisée : « Axes » dans la colonne A. | 5 |
| =RECHERCHEH("Boulons"; A1:C4; 4) | Recherche « Boulons » dans la ligne 1 et renvoie la valeur de la ligne 4 qui est comprise dans la même colonne (colonne C). | 11 |
| =RECHERCHEH(3; {1; 2; 3;"a","b","c";"d","e","f"}; 2; VRAI) | Recherche le nombre 3 dans la constante de matrice à trois lignes et renvoie la valeur de la ligne 2 qui est comprise dans la même colonne (la troisième dans cet exemple). Il existe trois lignes de valeurs dans la constante de matrice, chacune étant séparée par un point-virgule (;). Étant donné que « c » se trouve dans la ligne 2 et dans la même colonne que 3, « c » est renvoyé. | c |
Exemples INDEX et MATCH
Ce dernier exemple utilise les fonctions INDEX et EQUIV ensemble pour renvoyer le numéro de facture le plus ancien et sa date correspondante pour chacune des cinq villes. La date étant renvoyée sous forme de nombre, nous utilisons la fonction TEXTE pour la mettre en forme en tant que date. La fonction INDEX utilise en fait le résultat de la fonction EQUIV comme argument. La combinaison des fonctions INDEX et EQUIV est utilisée deux fois dans chaque formule : la première fois pour retourner le numéro de facture et la seconde pour retourner la date.
Copiez toutes les cellules dans ce tableau, puis collez-le dans la cellule A1 d’une feuille de calcul vierge dans Excel.
Conseil
Avant de coller les données dans Excel, définissez la largeur des colonnes A à D à 250 pixels, puis cliquez sur Renvoyer à la ligne automatiquement (onglet Accueil , groupe Alignement ).
| Facture | Ville | Date de facturation | Facture la plus ancienne par ville, avec la date |
|---|---|---|---|
| 3115 | Nantes | 7/4/12 | ="Nantes = "&INDEX($A$2:$C$33;EQUIV("Nantes";$B$2:$B$33;0);1)& "; Date de facturation : " & TEXTE(INDEX($A$2:$C$33;EQUIV("Nantes";$B$2:$B$33;0);3);"d/m/yy") |
| 3137 | Nantes | 9/4/12 | ="Lyon = "&INDEX($A$2:$C$33;EQUIV("Lyon";$B$2:$B$33;0);1)& "; Date de facturation : " & TEXTE(INDEX($A$2:$C$33;EQUIV("Lyon";$B$2:$B$33;0);3);"d/m/yy") |
| 3154 | Nantes | 11/4/12 | ="Bordeaux = "&INDEX($A$2:$C$33;EQUIV("Bordeaux";$B$2:$B$33;0);1)& "; Date de facturation : " & TEXTE(INDEX($A$2:$C$33;EQUIV("Bordeaux";$B$2:$B$33;0);3);"d/m/yy") |
| 3191 | Nantes | 21/4/12 | ="Brest = "&INDEX($A$2:$C$33;EQUIV("Brest";$B$2:$B$33;0);1)& "; Date de facturation : " & TEXTE(INDEX($A$2:$C$33;EQUIV("Brest";$B$2:$B$33;0);3);"d/m/yy") |
| 3293 | Nantes | 25/4/12 | ="Nice = "&INDEX($A$2:$C$33;EQUIV("Nice";$B$2:$B$33;0);1)& "; Date de facturation : " & TEXTE(INDEX($A$2:$C$33;EQUIV("Nice";$B$2:$B$33;0);3);"d/m/yy") |
| 3331 | Nantes | 27/4/12 | |
| 3350 | Nantes | 28/4/12 | |
| 3390 | Nantes | 1/5/12 | |
| 3441 | Nantes | 2/5/12 | |
| 3517 | Nantes | 8/5/12 | |
| 3124 | Lyon | 9/4/12 | |
| 3155 | Lyon | 11/4/12 | |
| 3177 | Lyon | 19/4/12 | |
| 3357 | Lyon | 28/4/12 | |
| 3492 | Lyon | 6/5/12 | |
| 3316 | Bordeaux | 25/4/12 | |
| 3346 | Bordeaux | 28/4/12 | |
| 3372 | Bordeaux | 1/5/12 | |
| 3414 | Bordeaux | 1/5/12 | |
| 3451 | Bordeaux | 2/5/12 | |
| 3467 | Bordeaux | 2/5/12 | |
| 3474 | Bordeaux | 4/5/12 | |
| 3490 | Bordeaux | 5/5/12 | |
| 3503 | Bordeaux | 8/5/12 | |
| 3151 | Brest | 9/4/12 | |
| 3438 | Brest | 2/5/12 | |
| 3471 | Brest | 4/5/12 | |
| 3160 | Nice | 18/4/12 | |
| 3328 | Nice | 26/4/12 | |
| 3368 | Nice | 29/4/12 | |
| 3420 | Nice | 1/5/12 | |
| 3501 | Nice | 6/5/12 |
Voir aussi
Aide-mémoire : actualisateur RECHERCHEV
Fonctions de recherche et de référence (référence)
Utilisation de l’argument table_matrice dans une fonction RECHERCHEV