Dites adieu à RECHERCHEV, et Bonjour à INDEX-MATCH

Précédent ◈ Suivant

Dites adieu à RECHERCHEV, et Bonjour à INDEX-MATCH

RECHERCHEV est un puissant et bien utilisé la fonction Excel. Si vous n'êtes pas familier, il trouve une valeur de recherche dans un tableau et renvoie la valeur correspondante d'une autre colonne. C'est idéal pour la recherche d'informations par référence. Dans l'image ci-dessus par exemple, le nom est la valeur de recherche, et il renvoie le salaire correspondant. Si vous êtes un utilisateur pouvoir Excel, vous êtes probablement très familier avec cette fonction. Sinon, voici comment cela fonctionne:

  • = VLOOKUP (lookup_value. Table_array. Col_index_num, Range_lookup))
    • Valeur_cherchée - La valeur que vous voulez trouver dans la colonne de valeur de recherche
    • Valeur_cherchée - La gamme de table contenant des colonnes tant pour la recherche et les valeurs de retour
    • Col_index_num - Le numéro d'index de la colonne contenant les valeurs de retour
    • Valeur_proche - Le type de match: la plus proche Moins (TRUE) ou exacte (FAUX) [facultatif]

RECHERCHEV, si puissant et utile, n'est pas sans limites. En fait, il peut être dangereux en raison de certains effets inattendus et les comportements. Si vous utilisez RECHERCHEV, s'il vous plaît continuez à lire pour en apprendre davantage sur ces questions. Cela vaut également pour la fonction sœur de RECHERCHEV de HLOOKUP (qui fait horizontale au lieu de recherches verticales lookups) et une autre fonction similaire, LOOKUP. J'ai fourni une méthode de recherche d'alternative plus sûre et plus souple, que je l'utilise presque partout à la place de RECHERCHEV.

Tout d'abord, je vais vous expliquer quelques-unes des limites de CHERCHEV:

Maintenant, voici quelques dangers de RECHERCHEV:

  1. Par défaut, RECHERCHEV utilise approchante. Si cela est la façon dont vous voulez qu'il fonctionne, tant mieux ... Cependant, dans de nombreux cas, vous voulez une valeur exacte retournée. Il ne donne aucune indication qu'il est de choisir un résultat du match le plus proche. Si vous ne voulez pas ce comportement (ce qui est la plupart du temps, je l'ai trouvé ...), vous vous souvenez de définir explicitement l'argument Range_lookup dans la formule à FAUX. Valeur_proche est facultative, et non un nom très descrive de cette fonction, il est souvent négligé. Cette bizarrerie est exaspérée par le second danger ...
  2. RECHERCHEV peut fournir des résultats faux si la table ne sont pas triées dans l'ordre croissant! Ceci est un problème lorsque vous utilisez la fonction de correspondance approximative, ce qui est vrai par défaut. Fondamentalement, RECHERCHEV commence au sommet de la table et descend ligne par ligne jusqu'à ce que pour arrive à un Valie inférieur ou égal à la valeur de recherche. Si votre table ne sont pas triées dans l'ordre croissant, cela peut donner des résultats faux, comme la formule arrête les lignes de traitement immédiatement après avoir trouvé une « correspondance ».

Suffisamment peur? Vérification de vos feuilles de calcul pour les questions maintenant. La réponse à ces problèmes et les limites est la méthode de recherche INDEX-MATCH. Cette méthode utilise deux fonctions ensemble pour fournir une fonctionnalité de recherche plus sûre et flexible. Voici comment chaque fonction fonctionne, indépendamment:

  • INDEX renvoie la valeur à l'intersection d'une rangée et de colonne dans une plage donnée.
    • Formule: = INDEX (.. Tableau no_lig no_col)
      • Array - La plage de cellules
      • No_lig - La rangée pour renvoyer des données à partir de
      • Num_colonne - La colonne de retourner les données de [facultatif]
  • MATCH renvoie une position d'un élément dans un tableau qui correspond à une valeur.
    • Formule: = MATCH (.. Lookup_value tableau_recherche match_type)
      • Valeur_cherchée - La valeur que vous voulez trouver dans le tableau de valeurs de recherche
      • Tableau_recherche - La gamme contenant des valeurs de recherche
      • Match_type - Exact (0), à proximité Less Than (-1), ou le plus proche supérieur à (1) [facultatif]

En combinant les deux fonctions, vous êtes en mesure de battre efficacement les limites et les dangers de RECHERCHEV. Vous pouvez construire une recherche qui vous permet de spécifier la colonne de colonne de recherche et de la valeur de retour de manière totalement indépendante, et aussi le contrôle de la nature du match approximative, sinon exacte. Les tableaux sont des gammes et vous pouvez num_colonne spécifique à la place de no_lig, ce qui signifie que vous n'êtes pas limité à l'utilisation que des colonnes; cela peut être utilisé à la place de HLOOKUP. Il a toujours la fonction de correspondance approximative le plus proche « moins que » par défaut, et contient les mêmes avertissements de tri que RECHERCHEV. Voici comment fonctionne la fonction combinée:

  • = INDEX (Return_value_range. MATCH (lookup_value. Lookup_value_range. Match_type))
    • Return_value_range - La gamme qui contient les valeurs de retour
    • Valeur_cherchée - La valeur que vous voulez trouver dans le tableau de valeurs de recherche
    • Lookup_value_range - La gamme contenant des valeurs de recherche
    • Match_type - Exact (0), le plus proche supérieur à (-1), ou à proximité Less Than (1)

Profitez et utiliser en bonne santé!

Exemple de formule INDEX-MATCH exposée dans une feuille de calcul Excel

Comme ce poste? Partagez-le!

Salut Ben. Très instructif. Merci.
Question cependant. J'ai une table avec une colonne de codes postaux (CB2: CB71). Ce sont les codes postaux pour un emplacement AMORCG paquet. lieu de livraison des codes postaux sont (CC1: dv2).
La charge pour le ramassage et livraison (zip à glissière) se trouve dans la table.
Je l'ai essayé d'utiliser vlookup faire cela, même index et correspondance. Je continue à obtenir des erreurs # N / A. Comment puis-je matchup la charge au zip pu et del zip? Merci, Rick

Excellente solution!
Ben pour MVP!

Il y a un autre avantage majeur à l'utilisation Index-Match.

Si vous voulez faire plusieurs recherches sur des milliers de lignes de données.

IE - si vous aviez une table avec 20 colonnes de données et que vous vouliez récupérer 5 d'entre eux, une façon courante est d'utiliser 5 VLOOKUPs (et qui peut facilement être 10 ou 15 ou 20 si vous faites des recherches conditionnelles) - multiplier par 20.000 lignes de données et vous faites beaucoup de calculs.

Index / Match vous permet de faire une recherche (la ligne), puis réutiliser que pour toutes les autres valeurs dérivées. Cela peut apporter des avantages de performance significatifs avec de grands ensembles de données que les recherches sont les plus lents calcul, en particulier avec des données non triées.

Ligne / Col A B
3 ApplePie 3,00 $
4 $ 3.00 tarte aux myrtilles
5 StrawberryPie 3,00 $
6
7 AppleTart 4,50 $
8 BlueberryTart 4,50 $

S'il vous plaît aider!
David

Je suis en train de faire un simple indice / match (je ne pense pas que RECHERCHEV peut le faire) sur une gamme de footages carrés avec un prix associé. Je ne pensais pas que cela pourrait être si difficile!

J'utilise deux feuilles différentes, l'une avec toutes les données de la tarification de la superficie, et l'autre comme un type de feuille « reporting ». « Fiche technique » est l'endroit où les prix sont, et « Concrete » est ce que je travaille. Je gammes de SF, et un prix associé à cette plage.

Colonne S est le prix, la colonne Q est l'extrémité supérieure de la plage (-1 si retourne la valeur égale ou inférieure à la gamme haut de gamme)

! Entrée en D15 Le béton est cette formule:

= INDEX ( « Data LISTES S17 $: S57 $, MATCH (A15, 'données LISTES Q17 $: Q57 $, -1))

Ce n'est pas ramener les bonnes données, et si vous allez au-dessus de 500 sur la cellule de référence, il donne une erreur de #NA. Grrrr! Cela ne devrait pas être si compliqué que ça!

Je viens d'essayer de mélanger INDEX / MATCH ET MATCH ensemble, mais je suis un # N / Une erreur ... Voici la formule:

= INDEX ( 'Data LISTES S17:! S57, (MATCH (béton A15,' données LISTES Q17:! Q57,1) * ET (MATCH (béton A15, 'données LISTES P17: P57, -1 ))))

Sonne comme une question complexe, David! Votre formule semble bien ... Etes-vous désireux de supprimer la possibilité pour eux d'utiliser le caractère générique de sorte qu'ils doivent faire une correspondance exacte? Ou bien, vous essayez simplement de faire une recherche de caractères génériques comme ce travail correctement / cohérente?

Je l'avais utilisé à la fois et LOOKUP INDEX / méthodes match pour obtenir une valeur de la table de référence ci-dessous pour essayer de revenir correspondance exacte value.However, la valeur retournée de * 10 est correspondant à celui de * 1 (au lieu de 0,5 1) .
lookup_vector Result_vector
(Gamme de recherche) (indice de plage)
* 1 0,5
* 2 3
* 6 0
* 10 1
Votre aide serait grandement appréciée!

Intéressant. Je pense que le problème a à voir avec l'astérisque (*) dans votre cellule. Cela pourrait être à l'origine de la formule pour correspondre comme joker. Je suggère que la coupe caractère lorsque vous faites votre INDEX-MATCH ou LOOKUP comparer. Par exemple, voici comment couper le premier caractère d'une valeur dans la cellule A1:
= RIGHT (A1, LEN (A1) -1)

J'espère que cela pourra aider!

Salut ... Coincé avec un problème ..
Dans la feuille 1, j'ai la date d'entrée d'une facture et le nom d'utilisateur.
Dans la feuille 2, j'ai nom d'utilisateur et début et de fin dates de ses vacances.
Je veux suivre si cet utilisateur est entré dans une facture quand il était en vacances ...
Je suppose que je dois correspondre à la fois, le nom d'utilisateur et si la date tombe entre les dates de début et de fin ....
Quelqu'un peut-il aider à ...

Salut ... C'est un problème intéressant!
Vous pouvez vérifier si une date se situe dans une plage en utilisant une formule comme ceci:
= SI (ET ([date de facturation]> [date de début], [date de la facture]

Trackbacks / Pingbacks

Catégories

Articles Liés

Précédent ◈ Suivant