I VLOOKUP -funksjonen definerer vi ofte col_index_no statisk. Vi hardkoder det innenfor VLOOKUP -formelen, som VLOOKUP (id, data,3, 0). Problemet oppstår når vi setter inn eller sletter en kolonne i data. Hvis vi fjerner eller legger til en kolonne før eller etter den tredje kolonnen, vil den tredje kolonnen ikke lenger referere til den tiltenkte kolonnen. Dette er ett problem. Annet er når du har flere kolonner å slå opp. Du må redigere kolonneindeksen i hver formel. Enkel kopiering vil ikke hjelpe.
Men hva med, hvis du kan fortelle VLOOKUP å se på overskrifter til og bare returnere matchende overskrifter. Dette kalles toveis VLOOKUP.
For eksempel, hvis jeg har en VLOOKUP -formel formerker kolonne, så bør VLOOKUP lete etter merker kolonne i data og returverdi fra den kolonnen. Dette vil løse problemet vårt.
Hmm … Ok, så hvordan gjør vi det? Ved å bruke Match -funksjonen i VLOOKUP -funksjonen.
Generisk formel
=VLOOKUP(oppslag_verdi, tabell_array, MATCH (oppslag_overskrift, tabell_overskrifter, 0), 0)
Oppslagsverdi: oppslagsverdien i den første kolonnen i table_array.
Tabell_array: området du vil gjøre et oppslag i. F.eks. A2, D10.
Lookup_heading: overskriften du vil slå opp i overskriftene til table_array.
Tabelloverskrifter: Referanse til overskriftene i tabelloppsettet. F.eks. hvis tabellen er A2, D10 og overskrifter øverst i hver kolonne, så er dens A1: D1.
Så, nå vet vi hva vi trenger for dynamisk col_index, la oss få alt avklart med et eksempel.
Eksempel på dynamisk oppslag
For dette eksemplet har vi denne tabellen som inneholder data for studenter i område A4: E16.
Ved å bruke rull nr og overskrift, vil jeg hente data fra denne tabellen. For denne forekomsten, i celle H4, vil jeg få data fra roll no skrevet i celle G4 og overskrift i H3. Hvis jeg endrer overskriften, skal data fra det respektive området hentes i celle H4.
Skriv denne formelen i celle H4
= VISNING (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Siden tabelloppsettet vårt er B4: E16, blir overskriftsmatrisen B3: E3.
Merk: Hvis dataene dine er godt strukturert, vil kolonneoverskriftene ha samme antall kolonner, og det er den første raden i tabellen.
Hvordan det fungerer:
Så hoveddelen er å evaluere kolonneindeksnummeret automatisk. For å gjøre det brukte vi MATCH -funksjonen.
MATCH (H3, B3: E3,0): Siden H3 inneholder “student”, vil MATCH returnere 2. Hvis H3 hadde “Karakter” den, ville den ha returnert 4, og så videre. VLOOKUP -formelen vil endelig ha col_index_num.
= VISNING (G4, B4: E16,2,0)
Som vi vet returnerer MATCH-funksjonen indeksnummeret til en gitt verdi i det medfølgende endimensjonale området. Derfor vil MATCH slå opp etter enhver verdi skrevet i H3 i område B3: E3 og returnere indeksnummeret.
Når du endrer overskrift i H3, hvis den er i overskrifter, vil denne formelen returnere en verdi fra den respektive kolonnen. Ellers får du en #N/A -feil.
VLOOKUP i flere kolonner raskt
I eksemplet ovenfor trengte vi svaret fra en kolonneverdi. Men hva om du vil få flere kolonner samtidig. Hvis du kopierer formelen ovenfor, returnerer den feil. Vi må gjøre noen mindre endringer i den for å gjøre den bærbar.
Bruke Absolutte referanser med VLOOKUP
Skriv formelen nedenfor i celle H2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Kopier nå H2 i alle cellene i område H2: J6 for å fylle den med data.
Hvordan det fungerer:
Her har jeg gitt absolutt referanse av hvert område unntatt rad i oppslagsverdi for VLOOKUP ($ G2) og kolonne i oppslagsverdi for MATCH (H $ 1).
$ G2: Dette vil tillate raden å endre for oppslagsverdi for VLOOKUP -funksjon mens du kopierer nedover, men begrenser kolonnen til å endres når den kopieres til høyre. Noe som får VLOOKUP til å lete etter Id fra G -kolonnen bare med den relative raden.
På samme måte, H $ 1 lar kolonnen endres når den kopieres horisontalt, og begrense raden når den kopieres nedover.
Bruke navngitte områder
Eksemplet ovenfor fungerer fint, men blir utfordrende å lese og skrive denne formelen. Og dette er ikke bærbart i det hele tatt. Dette kan forenkles ved å bruke navngitte områder.
Vi skal gjøre noen navn her først. For dette eksempelet navngav jeg
$ B $ 2: $ E $ 14: som data
$ B $ 1: $ E $ 1: som overskrifter
H $ 1: Gi det navnet Overskrift. Gjør kolonnene relative. Velg H1 for å gjøre det. Trykk CTRL+F3, klikk på nytt, i Refererer til seksjonen fjern '$' fra forsiden av H.
$ G2: På samme måte, gi det navnet RollNo. Denne gangen gjør raden relativ ved å fjerne '$' fra forsiden av 2.
Nå, når du har alle navnene på arket, skriver du denne formelen hvor som helst i excel -filen. Det vil alltid få det riktige svaret.
= VLOOKUP (RollNo, Data, MATCH (Overskrift, Overskrifter, 0), 0)
Se, hvem som helst kan lese dette og forstå det.
Så ved å bruke disse metodene kan du gjøre col_index_num dynamisk. Gi meg beskjed om dette var nyttig i kommentarfeltet nedenfor.
Slik bruker du tVLOOKUP -funksjonen i Excel
Relativ og absolutt referanse i Excel
Navngitte områder i Excel
Hvordan VLOOKUP fra forskjellige Excel -ark
VLOOKUP Flere verdier
Populære artikler
50 Excel -snarvei for å øke produktiviteten : Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du tVLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.
