I VLOOKUP -funktionen definerer vi ofte col_index_no statisk. Vi hardkoder det inden for VLOOKUP -formlen, som VLOOKUP (id, data,3, 0). Problemet opstår, når vi indsætter eller sletter en kolonne i data. Hvis vi fjerner eller tilføjer en kolonne før eller efter den tredje kolonne, refererer den tredje kolonne ikke længere til den påtænkte kolonne. Dette er et problem. Andet er, når du har flere kolonner at slå op. Du skal redigere kolonneindekset i hver formel. Enkel kopi-indsætning hjælper ikke.
Men hvad med, hvis du kan fortælle VLOOKUP at se på overskrifter til og kun returnere matchende overskrifters værdi. Dette kaldes tovejs VLOOKUP.
For eksempel, hvis jeg har en VLOOKUP -formel formærker kolonne, skal VLOOKUP søge efter mærker kolonne i data og returværdi fra den kolonne. Dette vil løse vores problem.
Hmm… Okay, så hvordan gør vi det? Ved at bruge Match -funktionen i VLOOKUP -funktionen.
Generisk formel
=VLOOKUP(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)
Opslag_værdi: opslagsværdien i den første kolonne i table_array.
Table_array: det område, hvor du vil søge. F.eks. A2, D10.
Lookup_heading: den overskrift, du vil slå op i i tabel_arrays overskrifter.
Tabeloverskrifter: Reference til overskrifterne i tabellen array. F.eks. hvis tabellen er A2, D10 og overskrifter øverst i hver kolonne, så dens A1: D1.
Så nu ved vi, hvad vi har brug for til dynamisk col_index, lad os få alt ryddet op med et eksempel.
Dynamisk VLOOKUP Eksempel
I dette eksempel har vi denne tabel, der indeholder data fra elever i område A4: E16.
Ved hjælp af rulle nr og overskrift vil jeg hente data fra denne tabel. I dette tilfælde vil jeg i celle H4 få data om rulle nr skrevet i celle G4 og overskrift i H3. Hvis jeg ændrer overskriften, skal data fra det respektive område hentes i celle H4.
Skriv denne formel i celle H4
= OPLYSNING (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Da vores tabel array er B4: E16, bliver vores overskrifter array B3: E3.
Bemærk: Hvis dine data er velstruktureret, har kolonneoverskrifter det samme antal kolonner, og det er den første række i tabellen.
Hvordan det virker:
Så hoveddelen evaluerer kolonneindeksnummeret automatisk. For at gøre det brugte vi funktionen MATCH.
MATCH (H3, B3: E3,0): Da H3 indeholder “student”, returnerer MATCH 2. Hvis H3 havde “Grade” den, ville den have returneret 4 osv. VLOOKUP -formlen vil endelig have det col_index_num.
= OPLYSNING (G4, B4: E16,2,0)
Som vi ved, returnerer MATCH-funktionen indeksnummeret for en given værdi i det medfølgende endimensionelle område. Derfor vil MATCH søge efter enhver værdi skrevet i H3 i område B3: E3 og returnere dets indeksnummer.
Når du nu ændrer overskrift i H3, hvis den er i overskrifter, returnerer denne formel en værdi fra den respektive kolonne. Ellers får du en #N/A fejl.
VLOOKUP i flere kolonner hurtigt
I eksemplet ovenfor havde vi brug for svaret fra en kolonneværdi. Men hvad nu hvis du vil have flere kolonner på én gang. Hvis du kopierer ovenstående formel, returnerer den fejl. Vi skal lave nogle mindre ændringer i den for at gøre den bærbar.
Brug af absolutte referencer med VLOOKUP
Skriv formlen nedenfor i celle H2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Kopier nu H2 i alle celler i område H2: J6 for at fylde den med data.
Hvordan det virker:
Her har jeg givet absolut reference af hvert område undtagen række i opslagsværdi for VLOOKUP ($ G2) og kolonne i opslagsværdi for MATCH (H $ 1).
$ G2: Dette giver rækken mulighed for at ændre for opslagsværdi for VLOOKUP -funktion, mens den kopieres nedad, men begrænser kolonnen til at ændre sig, når den kopieres til højre. Hvilket får VLOOKUP til kun at søge efter Id fra G -kolonnen med den relative række.
Tilsvarende H $ 1 tillader kolonnen at ændre sig, når den kopieres vandret, og begrænser rækken, når den kopieres nedad.
Brug af navngivne intervaller
Ovenstående eksempel fungerer fint, men bliver udfordrende at læse og skrive denne formel. Og dette er slet ikke bærbart. Dette kan forenkles ved hjælp af navngivne områder.
Vi vil først navngive her. Til dette eksempel navngav jeg
$ B $ 2: $ E $ 14: som data
$ B $ 1: $ E $ 1: som overskrifter
H $ 1: Navngiv det som overskrift. Gør kolonnerne relative. Vælg H1 for at gøre det. Tryk på CTRL+F3, klik på ny, i Henviser til sektion fjern '$' fra forsiden af H.
$ G2: Navngiv det på samme måde som RollNo. Denne gang gør rækken relativ ved at fjerne '$' fra forsiden af 2.
Når du nu har alle navnene på arket, skal du skrive denne formel hvor som helst i excel -filen. Det vil altid få det korrekte svar.
= VLOOKUP (RollNo, Data, MATCH (Overskrift, Overskrifter, 0), 0)
Se, alle kan læse dette og forstå det.
Så ved hjælp af disse metoder kan du gøre col_index_num dynamisk. Lad mig vide, om dette var nyttigt i kommentarfeltet herunder.
Sådan bruges than VLOOKUP -funktion i Excel
Relativ og absolut reference i Excel
Navngivet intervaller i Excel
Sådan VLOOKUP fra forskellige Excel -ark
VLOOKUP Flere værdier
Populære artikler
50 Excel -genvej til at øge din produktivitet : Bliv hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges than VLOOKUP -funktion i Excel : Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges funktionen COUNTIF i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.