Comment VLookUp avec critères multiples à l'aide INDEX et MATCH dans Excel
En utilisant INDEX et MATCH pour remplacer RECHERCHEV
Ce dont nous avons besoin est d'être en mesure de regarder le modèle et le déplacement en même temps. MATCH est une fonction qui vous donne l'emplacement d'un élément dans un tableau. La syntaxe de MATCH est la suivante:
Le valeur_cherchée est ce que vous recherchez.
Le tableau_recherche est le tableau de valeurs que vous essayez de trouver le valeur_cherchée dans.
Le match_type option détermine si MATCH doit trouver le valeur_cherchée exactement (avec un 0), ou retourner la plus proche qui vient avant (avec un 1) ou après (avec un marqueur -1) alphanumérique.
La fonction d'index a un emplacement et renvoie la valeur qui est dans la cellule. La syntaxe de INDEX est la suivante:
Le tableau est le tableau de données qui contient la valeur de la cellule que vous voulez.
Le no_lig est le numéro de ligne relatif de la cellule que vous voulez.
Le col_num est le numéro de colonne relative de la cellule que vous voulez.
En combinant INDEX et MATCH nous pouvons produire le même résultat que RECHERCHEV. En utilisant la même recherche que nous avons fait pour RECHERCHEV. la paire INDEX / MATCH ressemble à ceci:
C4: H1159 est le tableau que INDEX utilise pour trouver la valeur.
A2 est la cellule que la valeur que nous voulons MATCH trouver.
C4: C1159 est le tableau_recherche que MATCH regarde à travers pour trouver la valeur dans A2.
Le 0 signifie que MATCH recherchera la valeur exacte au lieu d'une valeur approximative.
La colonne 6 est la colonne de la C4: H1159array qui contient les valeurs d'économie de carburant combinée.
Le résultat est identique au résultat RECHERCHEV. MATCH trouve la première valeur économie de carburant combinée pour l'Acura ILX, ce qui signifie qu'il donnera 38 MPG pour 1,5 au lieu de l'une des autres options de moteur. Pour trouver un modèle et moteur spécifique combinaison déplacement, nous devons modifier notre formule INDEX / MATCH dans une formule de tableau.
Obtenir les derniers conseils et astuces Excel en se joignant à la newsletter!
Joignez-vous à la newsletter pour rester au-dessus des derniers articles. Inscrivez-vous et vous obtenez un guide gratuit avec 10 fois gain de raccourcis clavier!
187 réflexions sur « Comment VLookUp avec critères multiples à l'aide INDEX et MATCH »
Merci pour le grand poste sur l'utilisation de l'indice combiné # 038; fonctions MATCH. Il sera certainement utile pour moi dans l'avenir. Malheureusement, il tombe juste de résoudre un problème, je travaille maintenant. Pouvez-vous suggérer comment cette approche peut être modifiée (ou d'une autre approche tout à fait) pour une utilisation avec deux critères, dont l'un est pas exact? Plus précisément, je veux chercher un tableau où une valeur est une correspondance exacte, et la deuxième valeur se situe dans une plage (spécifiée par deux colonnes - une partie supérieure et une borne inférieure). Lorsque ces deux critères sont remplis, je veux sortir la valeur dans une colonne adjacente aux valeurs qui ont été recherchés. Par exemple, dans l'image ci-joint, les cellules sont mises en évidence jaune ce que je voudrais générer une formule pour produire (ici je les déterminé à la main). Je dois rechercher la colonne A pour un match pour la colonne F, puis trouver des cas où la colonne G est comprise entre les valeurs dans les colonnes B - C. Ensuite, je veux sortir la valeur contenue dans la colonne D. S'il vous plaît laissez-moi savoir si vous comme toute précision, et merci pour toute aide que vous pouvez fournir.
dit Chuck Trese:
Seulement besoin de reconnaître que vous pouvezdit Ryan Carroll:
Dans mon scénario, j'utilise G2 comme une date et que vous souhaitez récupérer la date la plus proche de la date prévue sur le G2. Idéalement, je dois récupérer le match exacte de la date à G2, mais parfois il n'y aura pas une correspondance exacte. Si cela se produit, je dois récupérer la date la plus proche dans la gamme que ce soit avant ou après la date indiquée sur G2. Est une formule pour ce possible? Je vous remercie!
Cette formule (INDEX = ($ A $ 1: $ D $ 7, MATCH (1, ($ A $ 1: $ A 7 = $ F2) * (B $ 1 $: B $ 7 $<$G2),0),4)) works, however I need to retrieve the closest date
dit Chuck Trese:
désolé, éditeur de texte ne permettra pas supérieur à et moins de symboles.
Vous avez juste besoin de reconnaître que vous pouvez utiliser « plus-que » et « moins que », non seulement « égaux dans vos chèques de comparaison. (Malheureusement, l'équation dans le post précédent est un gâchis car il ne montre pas les plus ou moins-que-de symboles, mais au lieu retiré au milieu de la formule -. La partie qui comptait le plus)
La suggestion de Chuck d'utiliser un plus grand ou moins que pour définir les limites supérieure et inférieure est juste sur l'argent. Sa formule vérifie que la colonne B est inférieure à la colonne G élément. Pour être plus précis, vous pouvez vérifier la limite supérieure trop, comme suit:
Ne pas oublier d'entrer comme une formule de tableau avec CTRL + SHIFT + ENTRER!
dit sai krishna:
J'ai une feuille avec des noms dans une colonne, et dans une autre feuille avec les noms et les numéros. Je l'ai utilisé vlookup pour obtenir le numéro de la feuille 2 à la valeur appropriée dans la feuille 1. La difficulté que je suis face est en tôle 2 mêmes noms sont là pour des numéros différents, donc RECHERCHEV donne la première valeur du match et en laissant le reste. Aidez-moi à résoudre ce problème
J'ai eu le même problème. La façon dont je résolu le problème est d'ajouter » 2" derrière le nom de la deuxième entrée pour la personne (s) avec deux numéros, et dans le tableau des résultats, vous devez avoir deux entrées pour ces personnes, en ajoutant » 2 "derrière le nom de famille.
Si quelqu'un connaît une meilleure solution ...
Merci pour ce grand tutoriel. Je comprends enfin comment utiliser la fonction Index + Match et a pu l'utiliser dans une feuille de calcul complexe pour le travail. En fin de compte, il fallait transposer les données d'une table à structure verticale à une structure horizontale. Est-il possible d'utiliser Index + Match pour les données structurées de haut en bas? De toute façon, un bon tutoriel et un blog bien écrit. Continuez le bon travail!
Merci - c'est une grande aide. Je suis en mesure de faire la formule avec 3 variables. Mon seul problème est que je voudrais copier cette formule dans une colonne entière de 100K + postes. Je ne parviens pas à cliquer juste le coin en bas à droite parce que je reçois un #REF! Erreur. Les copies de la formule avec « #REF! » Où la gamme de correspondance est censé être. La seule façon que je peux obtenir de travailler est frappé CTRL + MAJ + ENTRÉE dans chaque cellule, mais je ne peux pas faire ce 100K + fois. De toute façon simple de copier cette formule dans une colonne entière?
Félicitations pour avoir la formule et en cours d'exécution! Formules de tableau (ceux que vous entrez avec CTRL + SHIFT + Entrée) sont un peu pointilleux sur la façon dont vous les copiez. Vous pouvez sélectionner la cellule avec la formule entrée et faites-le glisser vers le bas avec le coin inférieur droit de copier la formule vers le bas. Vous pouvez également copier et coller la formule dans d'autres cellules, mais vous devez exclure la cellule d'origine de la sélection de pâte ou Excel une erreur.
En aparté, je vous mets en garde qu'un INDEX / MATCH 3 critères appliqués dans les cellules 100k prendront probablement un temps très long à calculer (sur l'ordre des heures). Assurez-vous d'enregistrer votre travail avant de copier la formule vers le bas et le plan pour le faire lorsque vous êtes en mesure de quitter Excel seul pour traiter pendant plusieurs heures.
Bonne chance!
Andrew
alberto dit Jaume:
Ceci est un article / tutoriel très agréable! Je l'ai trouvé peut être utilisé dans des circonstances differnet, mais je me demandais si vous pouvez fournir des explications supplémentaires en ce qui concerne une situation particualr. Je suis en train de produire un rapport qui vérifie un nom dans le tableau (qui se répète plusieurs fois) et sélectionne l'entrée la plus récente (chaque entrée a une date différente dans une autre colonne). I ma tête me casser en essayant d'utiliser votre approche, mais ne savez pas comment il pourrait le faire fonctionner. Pourriez-vous jeter un peu de lumière à ce sujet?
Merci pour les commentaires! Cette méthode doit toujours retourner le premier match, il trouve, donc si vous pré-trier votre liste de sorte que les entrées plus récentes apparaissent en haut, la recherche devrait fonctionner comme vous le souhaitez.
David Elms dit:
Salut, je me demandais s'il y avait un moyen de le faire dans la formule en utilisant la commande MAX en quelque sorte?
Je essentiellement en train de réaliser la même chose, mais je ne veux pas avoir quelqu'un pour aller et re-trier les données dans la feuille tout le temps que la recherche INDEX / MATCH est utilisé dans une autre feuille aux données source . C'est possible?
Salut,
Je pense qu'il ya un moyen facile de le faire (mais vous avez besoin d'une nouvelle colonne dans votre feuille de données)
S'il vous plaît vérifier ci-dessous.
i fusionné les cellules que je veux utiliser dans les critères et les mettre dans la colonne C ensemble (= A6 # 038; » « # 038; B6)
Ensuite utilisé cette formule pour le match. = VLOOKUP (G6 # 038; » « # 038; H6, $ C $ 6: $ D $ 11,2, FAUX)
C'est tout. Vous pouvez même créer autant de critères que vous voulez.
Salut,
Je pense qu'il ya un moyen facile de le faire (mais vous avez besoin d'une nouvelle colonne dans votre feuille de données)
S'il vous plaît vérifier ci-dessous.
i fusionné les cellules que je veux utiliser dans les critères et les mettre dans la colonne C ensemble (= A6- » « -B6)
Ensuite utilisé cette formule pour le match. = VLOOKUP (G6- » « -H6, C $ 6 $: $ D 11,2 $, FALSE)
C'est tout. Vous pouvez même créer autant de critères que vous voulez.
Ceci est très cool. Merci d'avoir partagé. J'ai eu bonne chance avec ctrl R et D ctrl pour copier vers le bas et à droite. Je ne savais pas IFERROR. J'utilise couramment = IF (ESTERREUR (formule), » », formule) pour obtenir des blancs au lieu de # N / A ou #VALUE. À votre santé.
Je vais avoir un problème similaire à Terry; il est drôle, parce que si je clique sur le symbole de Fx, il montre le résultat que je veux, mais la cellule dit encore # N / A. J'ai cliqué sur le symbole à côté de l'analyser et il dit que la première partie de la fonction MATCH (dans la formule donnée, c'est la C4: gamme C1159) est le problème, mais je ne peux pas déterminer pourquoi.
Toutes les pensées ou des idées? J'aimerais pouvoir le faire sans avoir recours à concaténer les deux champs pour créer la valeur de consultation!
Merci d'avance!
Une fois, vous entrez la formule dans la cellule, ne pas appuyez sur Entrée. à la place Appuyez sur CTRL + SHIFT + Entrée pour dire Excel est une formule de tableau. Il ne fonctionnera alors. Sinon, il va continuer à donner # N / A.
Espérons que cela a été utile.
Jay ... merci beaucoup! Nous avons concaténer la valeur dans les deux champs, puis faire RECHERCHEV ... ce qui est pas tout à fait idéal. Maintenant que je pouvais obtenir ce travail, je vais partager avec mes collègues!
c'est grande information, merci sooo beaucoup. c'est exactement ce que je avais besoin.
Je l'ai fait un test sur ce sujet et cela a fonctionné parfaitement, mais toutes mes données était sur 1 feuille.
puis j'ai essayé de l'appliquer à mon classeur réel, ce qui a des données sur 2 feuilles, et a reçu l'erreur « la formule que vous avez saisi contient une erreur. voici ma vraie formule:
l'erreur semble provenir de la « 11 » à la fin de la formule. Je suppose qu'il ne sait pas que le 11 fait référence à la 11ème colonne dans ma table spécifiée sur ma page « ligne de détail. J'ai essayé de mettre avant la 11 « ligne Détail » et cela n'a pas fonctionné.
des conseils utiles? Merci d'avance!
J'ai une feuille principale avec les colonnes A à U et des données sur les lignes 2 à 900. Je souhaite extraire toutes les données de colonnes / lignes basées sur un critère (qui se trouvent dans la colonne O et l'un des 5 options) et d'affichage des résultats avec les mêmes têtes de colonne que le maître, mais dans un autre onglet du classeur. Pouvez-vous s'il vous plaît me dire si cela est possible?
dit Avraham Nadell:
Grande poste. Merci Andrew.
Dans mes données source J'ai ajouté une date d'effet pour chaque variation des taux qui était la date LAST des taux. Le taux effectif de base était de 10 ans dans l'avenir.
Le l'indice formule I pour les valeurs regardé où la date était> = la date je cherchais.
A travaillé très bien, tout le tableau est lourd sur les calculs.
Heureux de partager si quelqu'un veut plus d'informations
Avraham