RECHERCHEV avec Dynamic Index Col
Dans link: / formulas-and-functions-introduction-of-vlookup-function [la fonction VLOOKUP], nous définissons souvent col_index_no static. Nous le codons en dur dans la formule VLOOKUP, comme VLOOKUP (id, data, 3,0). Le problème survient lorsque nous insérons ou supprimons une colonne dans les données. Si nous supprimons ou ajoutons une colonne avant ou après la 3e colonne, la 3e colonne ne fera plus référence à la colonne voulue. C’est un problème. L’autre est lorsque vous avez plusieurs colonnes à rechercher. Vous devrez modifier l’index de la colonne dans chaque formule. Un simple copier-coller n’aidera pas.
Mais qu’en est-il, si vous pouvez dire à VLOOKUP de regarder les en-têtes et de ne renvoyer que la valeur des en-têtes correspondante. Cela s’appelle RECHERCHEV bidirectionnelle.
Par exemple, si j’ai une formule RECHERCHEV pour la colonne des marqueurs, alors RECHERCHEV devrait rechercher la colonne de marques dans les données et renvoyer la valeur de cette colonne. Cela résoudra notre problème.
Hmm … Ok, alors comment on fait ça? En utilisant`link: / lookup-formulas-excel-match-function [la fonction de correspondance] dans le`link: / formulas-and-functions-introduction-of-vlookup-function [fonction RECHERCHEV].
Formule générique
=VLOOKUP(lookup_value,table_array,MATCH(lookup_heading,table_headings,0),0)
Lookup_value: la valeur de recherche dans la première colonne de table_array.
Table_array: la plage dans laquelle vous souhaitez effectuer une recherche. Par exemple, A2, D10.
Lookup_heading: l’en-tête que vous souhaitez rechercher dans les en-têtes de table_array.
Table_headings: référence des en-têtes dans le tableau du tableau. Par exemple. si le tableau est A2, D10 et les titres en haut de chaque colonne, alors son A1: D1.
Donc, maintenant que nous savons ce dont nous avons besoin pour col_index dynamique, clarifions tout avec un exemple.
Exemple de RECHERCHEV dynamique Pour cet exemple, nous avons ce tableau qui contient les données des étudiants dans la plage A4: E16.
En utilisant le numéro de rouleau et l’en-tête, je souhaite récupérer les données de cette table. Pour cet exemple, dans la cellule H4, je veux obtenir des données de rouleau non écrites dans la cellule G4 et de cap dans H3. Si je change l’en-tête, les données de la plage respective doivent être récupérées dans la cellule H4.
Écrivez cette formule dans la cellule H4
=VLOOKUP(G4,B4:E16,MATCH(H3,B3:E3,0),0)
Puisque notre tableau de table est B4: E16, notre tableau d’en-têtes devient B3: E3.
Remarque: si vos données sont bien structurées, les en-têtes de colonne auront le même nombre de colonnes et il s’agit de la première ligne du tableau.
Comment ça marche:
Ainsi, la partie principale évalue automatiquement le numéro d’index de la colonne.
Pour ce faire, nous avons utilisé la fonction MATCH.
MATCH (H3, B3: E3,0): Puisque H3 contient «student», MATCH retournera 2.
Si H3 avait «Grade», il aurait renvoyé 4, et ainsi de suite. La formule RECHERCHEV aura enfin son col_index_num.
=VLOOKUP(G4,B4:E16,2,0)
Comme nous le savons, la fonction`link: / lookup-formulas-excel-match-function [MATCH] `
La fonction renvoie le numéro d’index d’une valeur donnée dans la plage unidimensionnelle fournie. Par conséquent, link: / lookup-formulas-excel-match-function [MATCH] recherchera toute valeur écrite dans H3 dans la plage B3: E3 et retournera son numéro d’index.
Désormais, chaque fois que vous changez d’en-tête dans H3, s’il est dans les en-têtes, cette formule renverra une valeur de la colonne respective. Sinon, vous aurez une erreur # N / A.
RECHERCHEV dans plusieurs colonnes rapidement
Dans l’exemple ci-dessus, nous avions besoin de la réponse d’une valeur de colonne. Mais que faire si vous voulez obtenir plusieurs colonnes à la fois. Si vous copiez la formule ci-dessus, elle renverra des erreurs. Nous devons y apporter quelques modifications mineures pour le rendre portable.
En utilisant link: / excel-range-name-absolute-reference-in-excel [Absolute References] avec VLOOKUP Écrivez la formule ci-dessous dans la cellule H2.
=VLOOKUP($G2,$B$2:$E$14,MATCH(H$1,$B$1:$E$1,0),0)
Copiez maintenant H2 dans toutes les cellules de la plage H2: J6 pour le remplir de données.
Comment ça marche:
Ici, j’ai donné link: / excel-range-name-absolute-reference-in-excel [référence absolue] de chaque plage sauf ligne dans la valeur de recherche pour VLOOKUP ($ G2)
et colonne dans lookup_value pour MATCH (H $ 1).
$ G2: Cela permettra à la ligne de changer la valeur de recherche pour la fonction RECHERCHEV lors de la copie vers le bas, mais limitera la colonne à changer lorsqu’elle est copiée vers la droite. Ce qui obligera RECHERCHEV à rechercher l’ID de la colonne G uniquement avec la ligne relative.
De même, H $ 1 permettra à la colonne de changer lorsqu’elle est copiée horizontalement et restreindra la ligne lorsqu’elle sera copiée vers le bas.
Utilisation des plages nommées
L’exemple ci-dessus fonctionne bien mais devient difficile à lire et à écrire cette formule. Et ce n’est pas du tout portable. Cela peut être simplifié à l’aide de plages nommées.
Nous allons commencer par nommer ici. Pour cet exemple, j’ai nommé $ B $ 2: $ E $ 14: as Data $ B $ 1: $ E $ 1: as Headings H $ 1: Nommez-le comme Heading. Rendez les colonnes relatives. Pour ce faire, sélectionnez H1. Appuyez sur CTRL + F3, cliquez sur nouveau, dans Se réfère à la section supprimer ‘$’ de l’avant de H.
$ G2: De même, nommez-le RollNo. Cette fois, rend la ligne relative en supprimant «$» du début de 2.
Maintenant, lorsque vous avez tous les noms sur la feuille, écrivez cette formule n’importe où sur le fichier Excel. Il obtiendra toujours la bonne réponse.
=VLOOKUP(RollNo,Data, MATCH(Heading, Headings,0),0)
Voyez, n’importe qui peut lire ceci et le comprendre.
Ainsi, en utilisant ces méthodes, vous pouvez rendre dynamique col_index_num. Faites-moi savoir si cela vous a été utile dans la section commentaires ci-dessous.
Articles liés:
link: / formulas-and-functions-introduction-of-vlookup-function [Comment utiliser la fonction RECHERCHEV dans Excel] link: Relative% 20and% 20Absolute% 20Reference% 20in% 20Excel [Relative and Absolute Reference in Excel]
lien: / excel-range-name-all-about-named-ranges-in-excel [Named Ranges In Excel]
lien: / lookup-formulas-how-to-vlookup-from-different-excel-sheet [Comment VLOOKUP à partir d’une feuille Excel différente]
lien: / lookup-formulas-vlookup-multiple-values [VLOOKUP Multiple Values]
Articles populaires
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourci Excel pour augmenter votre productivité]: Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.
link: / formulas-and-functions-introduction-of-vlookup-function [Comment utiliser la fonction RECHERCHEV dans Excel]: C’est l’une des fonctions les plus utilisées et les plus populaires d’Excel qui est utilisée pour rechercher des valeurs dans différentes plages et feuilles.
link: / tips-countif-in-microsoft-excel [Comment utiliser la fonction COUNTIF dans Excel]: Comptez les valeurs avec des conditions en utilisant cette fonction étonnante. Vous n’avez pas besoin de filtrer vos données pour compter des valeurs spécifiques. La fonction Countif est indispensable pour préparer votre tableau de bord.
lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel]: Ceci est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.