In de functie VERT.ZOEKEN definiëren we vaak col_index_no statisch. We coderen het hard in de VLOOKUP-formule, zoals VLOOKUP(id,data,3,0). Het probleem doet zich voor wanneer we een kolom in gegevens invoegen of verwijderen. Als we een kolom verwijderen of toevoegen voor of na de 3e kolom, zal de 3e kolom niet meer verwijzen naar de beoogde kolom. Dit is één probleem. Anders is wanneer u meerdere kolommen moet opzoeken. U moet de kolomindex in elke formule bewerken. Simpel kopiëren en plakken helpt niet.
Maar hoe zit het, als u VERT.ZOEKEN kunt vertellen om naar koppen te kijken en alleen overeenkomende koppen te retourneren. Dit wordt tweerichtingsVERT.ZOEKEN genoemd.
Als ik bijvoorbeeld een VERT.ZOEKEN-formule heb voor demerken kolom, dan moet VERT.ZOEKEN zoeken naar merken kolom in gegevens en retourneert de waarde van die kolom. Dit zal ons probleem oplossen.
Hmm… Oké, dus hoe doen we dat? Door de Match-functie te gebruiken binnen de VERT.ZOEKEN-functie.
Generieke formule
=VERT.ZOEKEN(lookup_value,table_array,MATCH(lookup_heading,table_headings,0),0)
Opzoekwaarde: de opzoekwaarde in de eerste kolom van table_array.
Tabel_array: het bereik waarin u wilt opzoeken. Bijv. A2, D10.
Lookup_heading: de kop die u wilt opzoeken in de koppen van table_array.
Tabelkoppen: Referentie van de koppen in de tabelmatrix. bijv. als de tabel A2, D10 is en koppen bovenaan elke kolom, dan is het A1:D1.
Dus nu we weten wat we nodig hebben voor dynamische col_index, laten we alles duidelijk maken met een voorbeeld.
Dynamisch VERT.ZOEKEN Voorbeeld
Voor dit voorbeeld hebben we deze tabel met gegevens van studenten in het bereik A4:E16.
Met rol nr en rubriek wil ik gegevens uit deze tabel ophalen. Voor dit geval wil ik in cel H4 gegevens krijgen van rol nr geschreven in cel G4 en van kop in H3. Als ik de kop wijzig, moeten gegevens uit het respectieve bereik worden opgehaald in cel H4.
Schrijf deze formule in cel H4
=VERT.ZOEKEN(G4,B4:E16,VERGELIJKEN(H3,B3:E3,0),0)
Aangezien onze tabelarray B4:E16 is, wordt onze koppenarray B3:E3.
Opmerking: Als uw gegevens goed gestructureerd zijn, hebben kolomkoppen hetzelfde aantal kolommen en is dit de eerste rij in de tabel.
Hoe het werkt:
Het belangrijkste onderdeel is dus het automatisch evalueren van het kolomindexnummer. Hiervoor hebben we de MATCH-functie gebruikt.
GELIJK(H3,B3:E3,0): Aangezien H3 "student" bevat, zal MATCH 2 retourneren. Als H3 "Grade" had, zou het 4 hebben geretourneerd, enzovoort. De VERT.ZOEKEN-formule heeft eindelijk zijn col_index_num.
=VERT.ZOEKEN(G4,B4:E16,2,0)
Zoals we weten, retourneert de MATCH-functie het indexnummer van een bepaalde waarde in het opgegeven eendimensionale bereik. Daarom zoekt MATCH elke waarde op die is geschreven in H3 in het bereik B3:E3 en retourneert het zijn indexnummer.
Wanneer u nu de kop in H3 wijzigt, als deze zich in de kop bevindt, retourneert deze formule een waarde uit de respectieve kolom. Anders krijgt u een #N/A-fout.
VERT.ZOEKEN snel in meerdere kolommen
In het bovenstaande voorbeeld hadden we het antwoord uit één kolomwaarde nodig. Maar wat als u meerdere kolommen tegelijk wilt krijgen. Als u de bovenstaande formule kopieert, worden er fouten geretourneerd. We moeten er enkele kleine wijzigingen in aanbrengen om het draagbaar te maken.
Absolute referenties gebruiken met VERT.ZOEKEN
Schrijf onderstaande formule in cel H2.
=VERT.ZOEKEN($G2,$B$2:$E$14,MATCH(H$1,$B$1:$E$1,0),0)
Kopieer nu H2 in alle cellen in het bereik H2:J6 om het met gegevens te vullen.
Hoe het werkt:
Hier heb ik gegeven absolute referentie van elk bereik behalve rij in opzoekwaarde voor VERT.ZOEKEN ($G2) en kolom in lookup_value voor MATCH (H $ 1).
$G2: Hierdoor kan de rij veranderen voor de opzoekwaarde voor de VERT.ZOEKEN-functie terwijl naar beneden wordt gekopieerd, maar wordt de kolom beperkt om te veranderen wanneer deze naar rechts wordt gekopieerd. Waardoor VERT.ZOEKEN alleen naar de ID van de G-kolom zoekt met de relatieve rij.
evenzo, H $ 1 zal toestaan dat de kolom verandert wanneer deze horizontaal wordt gekopieerd, en de rij beperken wanneer deze naar beneden wordt gekopieerd.
Benoemde bereiken gebruiken
Het bovenstaande voorbeeld werkt prima, maar wordt een uitdaging om deze formule te lezen en te schrijven. En dit is helemaal niet draagbaar. Dit kan worden vereenvoudigd met benoemde bereiken.
We zullen hier eerst een naam geven. Voor dit voorbeeld noemde ik
$B$2:$E$14 : als gegevens
$B$1:$E$1 : als koppen
H$1 : Noem het als Kop. Maak de kolommen relatief. Selecteer hiervoor H1. Druk op CTRL+F3, klik op nieuw, in Verwijst naar sectie verwijder '$' van de voorkant van H.
$G2: Noem het ook RollNo. Deze tijd maakt de rij relatief door '$' van de voorkant van 2 te verwijderen.
Als u nu alle namen op het blad hebt, schrijft u deze formule ergens in het Excel-bestand. Het zal altijd het juiste antwoord krijgen.
=VLOOKUP(RollNo,Data, MATCH(Heading, Headings,0),0)
Kijk, iedereen kan dit lezen en begrijpen.
Met deze methoden kunt u col_index_num dus dynamisch maken. Laat me weten of dit nuttig was in de comments hieronder.
Hoe te gebruikende VERT.ZOEKEN-functie in Excel
Relatieve en absolute referentie in Excel
Benoemde bereiken in Excel
Hoe te VERT.ZOEKEN vanuit een ander Excel-blad
VERT.ZOEKEN Meerdere waarden
populaire artikels
50 Excel-snelkoppeling om uw productiviteit te verhogen : Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe te gebruikende VERT.ZOEKEN-functie in Excel : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.