Recherchez une valeur unique avec vlookup et RECHERCHEH dans Excel

Le plus populaire des fonctions de recherche dans Excel 2013 sont RECHERCHEH (pour Horizontal Lookup) et VLOOKUP (pour une recherche verticale) fonctions. Ces fonctions sont situées à l'Lookup Référence dans le menu déroulant sur l'onglet Formules du ruban ainsi que dans la Recherche Catégorie de référence dans la boîte de dialogue Insérer une fonction.

Ils font partie d'un groupe puissant de fonctions qui peuvent renvoyer des valeurs en les regardant dans les tableaux de données.

La fonction RECHERCHEV recherches verticalement (de haut en bas) de la colonne de gauche une table de consultation jusqu'à ce que le programme localise une valeur égale ou supérieure à celle que vous cherchez en place. La fonction RECHERCHEH recherches horizontalement (de gauche à droite) de la rangée supérieure de une table de consultation jusqu'à ce qu'il localise une valeur égale ou supérieure à celle que vous êtes à la recherche vers le haut.

La fonction RECHERCHEV utilise la syntaxe suivante:

VLOOKUP (lookup_value, table_array, col_index_num, [valeur_proche])

La fonction RECHERCHEH suit la syntaxe presque identique:

RECHERCHEH (lookup_value, table_array, row_index_num, [valeur_proche])

Dans les deux fonctions, le lookup_value argument est la valeur que vous souhaitez rechercher dans la table de correspondance, et table_array est la plage de cellules ou le nom de la table de consultation qui contient à la fois la valeur de rechercher et de la valeur liée à revenir.

La col_index_num argument dans la fonction RECHERCHEV est le nombre de la colonne dont les valeurs sont par rapport à la lookup_value argument dans un tableau vertical. La row_index_num argument de la fonction RECHERCHEH est le numéro de la ligne dont les valeurs sont comparées à la lookup_value dans une table horizontale.

En entrant dans le col_index_num ou row_index_num arguments dans les fonctions RECHERCHEV et RECHERCHEH, vous devez entrer une valeur supérieure à zéro qui ne dépasse pas le nombre total de colonnes ou lignes dans la table de recherche.

L'option valeur_proche argumentation à la fois dans les fonctions VLOOKUP et HLOOKUP est la logique VRAI ou FAUX qui spécifie si vous voulez Excel pour trouver une correspondance exacte ou approximative pour le lookup_value dans le table_array.

Lorsque vous spécifiez TRUE ou omettez le valeur_proche argument de la fonction RECHERCHEV ou RECHERCHEH, Excel trouve une correspondance approximative. Lorsque vous spécifiez FALSE comme valeur_proche l'argument, Excel ne trouve que des correspondances exactes.

Trouver des correspondances approximatives concerne uniquement lorsque vous êtes à la recherche des entrées numériques (plutôt que du texte) dans la première colonne ou une ligne de la table de recherche verticale ou horizontale. Lorsque Excel ne trouve pas une correspondance exacte dans cette colonne de recherche ou de la ligne, il localise la prochaine valeur la plus élevée qui ne dépasse pas la lookup_value l'argument et retourne la valeur dans la colonne ou la ligne désignée par le col_index_num ou row_index_num arguments.

Lorsque vous utilisez les fonctions RECHERCHEV et RECHERCHEH, le texte ou les entrées numériques dans la colonne de recherche ou de la ligne (qui est, la colonne de gauche d'une table de Lookup verticale ou la rangée du haut d'une table de Lookup horizontal) doivent être uniques. Ces inscriptions doivent également être organisées ou triés dans l'ordre croissant order- qui est, par ordre alphabétique des entrées de texte, et le plus bas à plus haut niveau pour les entrées numériques.

La figure montre un exemple d'utilisation de la fonction RECHERCHEV pour revenir soit à 15% ou 20% de pourboire à partir d'une table de pointe, selon le total avant impôts de la vérification. F3 cellule contient la fonction RECHERCHEV:

= VLOOKUP (Pretax_Total, Tip_Table, SI (Tip_Percentage = 0.15,2,3)) 

Cette formule renvoie le montant de la pointe basée sur le pourcentage de la pointe dans la cellule F1 et le montant avant impôts de la vérification dans la cellule F2.

Pour utiliser ce tableau de pointe, entrez le pourcentage de la pointe (15% ou 20%) dans la cellule F1 (nommé Tip_Percentage) et le montant du chèque avant impôt dans la cellule F2 (nommé Pretax_Total). Excel examine ensuite la valeur que vous entrez dans la cellule Pretax_Total dans la première colonne de la table de correspondance, qui inclut la plage de cellules A2: C101 et est nommé Tip_Table.

Excel se déplace alors vers le bas les valeurs de la première colonne de Tip_Table jusqu'à ce qu'il trouve une correspondance, après quoi le programme utilise la col_index_num argument de la fonction RECHERCHEV pour déterminer qui pointe montant de cette ligne de la table pour retourner à la cellule F3.

Si Excel trouve que la valeur saisie dans la cellule Pretax_Total (16,50 $ dans cet exemple) ne correspond pas exactement à l'une des valeurs de la première colonne de Tip_Table, le programme continue à chercher en bas de la plage de comparaison jusqu'à ce qu'il rencontre la première valeur qui dépasse le total avant impôts (17.00 dans la cellule A19 dans cet exemple).

Excel se déplace ensuite remonter à la ligne précédente dans le tableau et renvoie la valeur dans la colonne qui correspond à la col_index_num argument de la fonction VLOOKUP. (En effet, le facultatif valeur_proche l'argument a été omis de la fonction.)

Notez que l'exemple de la table de pointe utilise une fonction SI pour déterminer la col_index_num argument de la fonction RECHERCHEV dans la cellule F3.

La fonction SI détermine le nombre de colonne pour être utilisé dans le tableau de pointe en faisant correspondre le pourcentage entré dans Tip_Percentage (la cellule F1) avec 0,15. Si elles correspondent, la fonction retourne le 2 col_index_num argumentation et la fonction RECHERCHEV renvoie une valeur de la deuxième colonne (15% colonne B) dans la gamme Tip_Table.

Sinon, la fonction SI renvoie 3 que le col_index_num argumentation et la fonction RECHERCHEV renvoie une valeur à partir de la troisième colonne (20% colonne C) dans la gamme Tip_Table.

La figure suivante montre un exemple qui utilise la fonction RECHERCHEH pour rechercher le prix de chaque article de boulangerie stockées dans une table prix Lookup distinct, puis de retourner ce prix au Prix / Doz colonne de la liste des ventes quotidiennes. F3 cellule contient la formule originale avec la fonction RECHERCHEH qui est ensuite copié vers le bas de la colonne F:

= RECHERCHEH (point, Price_table, 2, FALSE)

Dans cette fonction RECHERCHEH, le nom de la plage Point qui est donné à la colonne d'objet dans la gamme C3: C62 est définie comme la lookup_value argumentation et la plage de cellules tableau Nom de prix qui est donné à la plage de cellules I1: M2 est le table_array argument.

La row_index_num argument est 2 parce que vous voulez Excel pour revenir les prix dans la deuxième rangée de la table Prix de Recherche, et l'option valeur_proche argument est faux parce que le nom de l'élément dans la liste des ventes Daily doit correspondre exactement au nom de l'élément dans le tableau de prix Lookup.

En ayant la fonction de RECHERCHEH utiliser la gamme de table de prix à l'entrée du prix par douzaine pour chaque élément de produits de boulangerie dans la liste des ventes quotidiennes, vous en faites une question très simple à mettre à jour des ventes dans la liste.

Tout ce que vous avez à faire est de changer son / Doz coût de prix dans cette gamme, et la fonction de RECHERCHEH met immédiatement à jour le nouveau prix dans la liste des ventes quotidiennes où l'article est vendu.