Sådan bruges VLOOKUP-funktionen i Excel

Vil du vide, hvordan du bruger VLOOKUP-funktionen i Excel ? I denne vejledning viser vi dig, hvordan du bruger VLOOKUP-funktionen til at kopiere data fra et andet regneark eller en anden projektmappe, udføre VLOOKUP på tværs af flere ark og dynamisk søge for at returnere værdier fra forskellige ark til forskellige celler.

Når man søger efter information i Excel, er det sjældent, at alle dataene er på det samme ark. Oftest skal man søge på tværs af flere ark eller endda forskellige projektmapper. Den gode nyhed er, at Microsoft Excel tilbyder mere end én måde at gøre dette på , og den dårlige nyhed er, at alle disse metoder er lidt mere komplicerede end en standard VLOOKUP-formel. Men med lidt tålmodighed lærer du det.

Sådan bruger du funktionen VLOOKUP i Excel mellem to ark

Lad os starte med at se på et simpelt eksempel: brug af VLOOKUP-funktionen i Excel til at kopiere data fra et andet regneark. Det minder meget om en almindelig VLOOKUP-formel, der søger i det samme regneark. Forskellen er, at den inkluderer arknavnet i argumentet table_array for at fortælle din formel, hvilket regneark der indeholder søgeområdet.

Her kan du lære om: Sådan forbedrer du din Excel-oplevelse – 13 nyttige tips

Den generiske formel for VLOOKUP-funktionen i Excel fra et andet ark er som følger:

• VLOOKUP (opslagsværdi, Ark! Rækkevidde, col_index_num, [range_lookup])

Lad os som eksempel trække salgstallene fra januarrapporten til oversigtsarket. For at gøre dette skal du definere følgende argumenter:

• Søgeværdier De er i kolonne A på arket Resumé og vi henviser til den første datacelle, som er A2.
• Tabelarrayer rang A2: B6 på januararket. For at referere til det skal du præfikse områdereferencen med arknavnet efterfulgt af udråbstegn: Jan! $A$2:$B$6.

BEMÆRK : Her skal du være opmærksom på at låse området med absolutte cellereferencer for at forhindre det i at ændre sig, når du kopierer formlen til andre celler.

• Col_index_numer 2, fordi du vil kopiere en værdi fra kolonne B, som er den anden kolonne i tabelmatricen.
• Range_lookuper indstillet til FALSK for at finde et nøjagtigt match.

Sætter man argumenterne sammen, får man denne formel:

• =OPSLAG(A2, jan!$A$2:$B$6, 2, FALSK)

Træk formlen ned i kolonnen, og du vil få dette resultat:

På samme måde kan du udføre Vlookup-funktionen ved hjælp af data fra februar og marts ark:

• =OPSLAG(A2, feb!$A$2:$B$6, 2, FALSK)
• =OPSLAG(A2, Tir!$A$2:$B$6, 2, FALSK)

Tips og noter om VLOOKUP-funktionen i Excel:

• Hvis arknavnet indeholder rumeller tegn ikke-alfabetisk, skal vedlægges enkelte anførselstegn, som f.eks 'Januarudsalg'! $A$2:$B$6.
• I stedet for at skrive et arknavn direkte i en formel, kan du skifte til opslagsarket og vælge området der. Excel vil automatisk indsætte en reference med den korrekte syntaks, hvilket sparer dig for besværet med at tjekke navnet og fejlfinding.

Metoder til at bruge VLOOKUP-funktionen i Excel

Lad os nu komme ind på detaljerne i brugen af ​​VLOOKUP-funktionen i Excel. Her giver vi dig nogle muligheder:

Metode 1: Vopslag af en anden projektmappe

For at bruge funktionen VOPSLAG i Excel mellem to projektmapper, skal du indsætte filnavnet i firkantede parenteser efterfulgt af arknavnet og udråbstegn.

• For eksempel: at finde værdien A2 i området A2:B6 på januararket i projektmappen xlsx, brug denne formel:
• =OPSLAG(A2, [Salg_reports.xlsx]Jan!$A$2:$B$6, 2, FALSK)

Metode 2: Vlookup på flere ark med IFERROR

Når du skal søge mellem mere end to ark, er den nemmeste løsning at bruge funktionen VOPSLAG i Excel i kombination med IFERROR. Ideen er at indlejre flere IFERROR-funktioner for at kontrollere flere regneark én efter én: hvis den første VLOOKUP ikke finder en match på det første ark, ser den på det næste ark, og så videre. For eksempel:

• FEJL (VLOOKUP (...), IFERROR (VLOOKUP (...),..., » Ikke fundet«))

For at se, hvordan denne tilgang virker på data fra det virkelige liv, lad os overveje følgende eksempel:

Nedenfor er den oversigtstabel , som du skal udfylde med varenavne og antal, når du slår ordrenummeret op på vest- og østarkene:

• trin 1: Først skal du tage elementerne ud. For at gøre dette angiver vi til formlen LOPSLAG Find ordrenummeret i A2 på arket dette og returner værdien af ​​kolonne B (2. kolonne i table_array A2:C6).
• trin 2: Hvis der ikke findes et nøjagtigt match, skal du søge på arket Vest.
• trin 3: Ja begge dele Vlookups går ned, returnerer meddelelsen: "Ikke fundet".
  • =IFERROR(VLOOKUP(A2, Øst!$A$2:$C$6, 2, FALSK), IFERROR(SØG OP(A2, Vest!$A$2:$C$6, 2, FALSK), «Ikke fundet»))

• trin 4: For at returnere beløbet skal du blot ændre kolonneindeksnummeret til 3:
  • =IFERROR(VLOOKUP(A2, Øst!$A$2:$C$6, 3, FALSK), IFERROR(SØG OP(A2, Vest!$A$2:$C$6, 3, FALSK), «Ikke fundet»))

Tip: Om nødvendigt kan du angive forskellige tabelmatrixer for forskellige VLOOKUP-funktioner. I dette eksempel har begge opslagsark det samme antal rækker (A2:C6), men dine regneark kan have forskellige størrelser.

Metode 3: Vlookup på flere projektmapper

For at søge mellem to eller flere projektmapper skal du omgive projektmappens navn i firkantede parenteser og placere det før arknavnet.

• E.g.- Sådan kan du bruge Vlookup ito forskellige filer (Bog1 og Bog2) med en enkelt formel:
  • =IFERROR(SØG OP(A2, [Bog1.xlsx]Øst!$A$2:$C$6, 2, FALSK), HVISFEJL(SØG OP(A2, [Bog2.xlsx]Vest!$A$2:$C$6, 2, FALSK),»Ikke fundet»))

Metode 4: Gør kolonneindeksnummer dynamisk for Vlookup flere kolonner

I en situation, hvor du skal returnere data fra flere kolonner, kan det spare dig tid at gøre `col_index_num` dynamisk. Et par justeringer er nødvendige:

• For argumentet col_index_num, brug funktionen COLUMNS som returnerer antallet af kolonner i en specificeret matrix: KOLONNER ($A$1:B$1). (Rækkekoordinaten betyder ikke rigtig noget, det kan være en hvilken som helst række.)
• I opslagsværdi-argumentet, låser kolonnereferencen med $-tegn ($A2), så den forbliver fast, når du kopierer formlen til andre kolonner.

Som et resultat vil du få en slags dynamisk formel, der udtrækker matchende værdier fra forskellige kolonner, afhængigt af hvilken kolonne formlen er kopieret til:

• =IFERROR(SLAG OP($A2, Øst!$A$2:$C$6, KOLONNER($A$1:B$1), FALSK), IFERROR(OPSLAG($A2, Vest!$A$2:$C$6, KOLONNER( $A$1:B$1), FALSE), «Ikke fundet»))

Når den indtastes i kolonne B, evalueres COLUMNS($A$1:B$1) til 2 og fortæller VLOOKUP, at værdien 2 skal returneres fra den anden kolonne i tabelmatrixen.

Når du kopierer formlen til kolonne C (dvs. trækker den fra B2 til C2), ændres B$1 til C$1, fordi kolonnereferencen er relativ. Derfor evalueres COLUMNS($A$1:C$1) til 3, hvilket tvinger VLOOKUP-funktionen i Excel til at returnere en værdi fra den tredje kolonne.

Denne formel fungerer godt til 2 eller 3 søgeark. Hvis du har flere, bliver gentagne IFERRORs for besværlige. Det følgende eksempel viser en lidt mere kompliceret, men meget mere elegant tilgang.

Metode 5: Vlookup flere ark med INDIREKTE

Endnu en måde at Vlookup mellem flere ark i Excel er at bruge en kombination af VLOOKUP og INDIREKTE funktioner. Denne metode kræver lidt forberedelse, men i sidste ende vil du have en mere kompakt formel for Vlookup i et vilkårligt antal regneark.

En generisk formel for Vlookup på ark er som følger:

• OPSLAG ( opslagsværdi, INDIREKTE («'» & INDEX ( Opslagsark , MATCH (1, – (COUNTIF (INDIREKTE («'» & Sheet_list & «'! Opslagsområde "), opslagsværdi )> 0), 0)) & «'! tabelmatrix "), col_index_num , FALSK)

hvor:

• Opslagsark- Et navngivet område bestående af søgearkets navne.
• Opslagsværdi: den værdi, der skal søges efter.
• Opslagsområde- Området af kolonner i opslagsark, hvor der skal søges efter opslagsværdien.
• Tabel array- Rækken af ​​data i opslagsarkene.
• Col_index_num- Nummeret på kolonnen i tabelarrayet, hvorfra en værdi returneres.

For at formlen skal fungere korrekt, skal du tage hensyn til følgende advarsler:

1. Dette er en matrixformel, som skal udfyldes ved at trykke på Ctrl + Shift + Enter nøgler sammen.
2. Alle ark skal have samme kolonnerækkefølge.
3. Da du bruger et tabelarray til alle opslagsark, skal du angive største udvalg hvis dine ark har forskellige antal rækker.

Sådan bruger du formlen til Vlookup på ark

For at bruge Vlookup-funktionen på flere ark på én gang, skal du følge disse trin:

• trin 1: Skriv alle opslagsarknavnene et sted i din projektmappe og navngiv det område (Opslagsark I dette tilfælde).

• trin 2: Juster den generiske formel for dine data. I dette eksempel ville det være:
  • leder efter værdien A2 (opslagsværdi)
  • i området A2: A6 (opslag_område) på fire regneark (Øst, Nord, Syd og Vest), og
  • udtræk matchende værdier fra kolonne B, som er kolonne 2 (сol_index_num) i dataområdet A2: C6 (tabelmatrix).

Med de foregående argumenter har formlen denne form:

• =VOPSLAG ($A2, INDIREKTE («'»&INDEX (Lookup_sheets, MATCH (1, –(COUNTIF (INDIREKTE («'»& Lookup_sheets&»'!$A$2:$A$6″), $A2)>0), 0)) &»'!$A$2:$C$6»), 2, FALSK)

BEMÆRK : Husk, at vi har låst begge områder ($A$2:$A$6 og $A$2:$C$6) med absolutte cellereferencer.

• trin 3: Indtast formlen i den øverste celle (B2 i dette eksempel) og tryk Ctrl + Shift + Enter at fuldføre det.
• trin 4: Dobbeltklik eller træk udfyldningshåndtaget for at kopiere formlen ned i kolonnen.

Som følge heraf har du formlen til at søge efter ordrenummeret på tværs af 4 ark og finde den tilsvarende vare. Hvis et specifikt ordrenummer ikke findes, vises en #N/A -fejl , som i række 14:

For at returnere mængden skal du blot erstatte 2 med 3 i argumentet col_index_num , da mængderne er i den 3. kolonne i tabelarrayet:

• =VOPSLAG($A2, INDIREKTE(«'»&INDEKS(Opslagsark, SAMMENLIGN(1, –(ANTALHVIS(INDIREKTE(«'» & Opslagsark & ​​«'!$A$2:$A$6»), $A2)>0) , 0)) & «'!$A$2:$C$6»), 3, FALSK)

Hvis du vil erstatte standardfejlnotationen #N/A med din egen tekst, skal du ombryde formlen i IFNA-funktionen:

• =IFNA (VOPSLAG($A2, INDIREKTE («'»&INDEX (Opslagsark, MATCH (1, –(ANTALHVIS(INDIREKTE («'» & Opslagsark & ​​«'!$A$2:$A$6»), $A2)> 0), 0)) & «'!$A$2:$C$6»), 3, FALSE), «Ikke fundet»)

Vlookup flere ark mellem projektmapper

Denne generiske formel (eller enhver variation) kan også bruges til at vise flere ark i en anden projektmappe . For at gøre dette skal du sammenkæde projektmappens navn i INDIRECT som vist i følgende formel:

• =IFNA(VOPSLAG ($A2, INDIREKTE («'[Bog1.xlsx]» & INDEX (Opslagsark, MATCH (1, –(ANTALHVIS(INDIREKTE («'[Bog1.xlsx]» & Opslagsark & ​​«'!$A$2) :$A$6»), $A2)>0), 0)) & «'!$A$2:$C$6»), 2, FALSK), «Ikke fundet»)

Vopslag mellem ark og returnerer flere kolonner

Hvis du vil udtrække data fra flere kolonner, kan du gøre det hele på én gang med en flercellet matrixformel . For at oprette en sådan formel skal du angive en matrixkonstant for argumentet col_index_num.

I dette eksempel returnerer vi elementnavnene (kolonne B) og mængderne (kolonne C), som er henholdsvis den 2. og 3. kolonne i tabelmatricen. Derfor er den nødvendige matrix {2,3}.

• =VOPSLAG($A2, INDIREKTE(«'»&INDEX(Opslagsark, MATCH(1, –(ANTALHVIS(INDIREKTE(«'»& Opslagsark &»'!$A$2:$C$6″), $A2)>0) , 0)) &»'!$A$2:$C$6»), {2,3}, FALSE)

For at kunne indtaste formlen i flere celler, skal du gøre her:

• trin 1: I den første række skal du vælge alle de celler, der skal udfyldes (B2:C2 i dette eksempel).
• trin 2: Skriv formlen og tryk på tasterne Ctrl + Shift + Enter. Dette indtaster den samme formel i de valgte celler, som vil returnere en anden værdi i hver kolonne.
• trin 3: Træk formlen til de resterende rækker.

Hvordan funktionsformlen VLOOKUP fungerer i Excel

For bedre at forstå logikken, lad os nedbryde denne grundlæggende formel i de enkelte funktioner:

• =VOPSLAG ($A2, INDIREKTE («'»&INDEX(Lookup_sheets, MATCH (1, –(COUNTIF (INDIRECT («'»& Lookup_sheets&»'!$A$2:$A$6″), $A2)>0), 0)) &»'!$A$2:$C$6»), 2, FALSK)

Når man arbejder indefra og ud, er dette, hvad formlen gør:

COUNTIF og INDIREKTE

Kort sagt opretter INDIRECT referencerne for alle opslagsark, og COUNTIF tæller forekomsterne af opslagsværdien (A2) i hvert ark:

• –(COUNTIF( INDIRECT(«'»&Opslagsark&»'!$A$2:$A$6»), $A2)>0)

Mere detaljeret:

Først skal du sammenkæde områdenavnet (Lookup_sheets) og områdereferencen ($A$2:$A$6), tilføje apostroffer og udråbstegnet de rigtige steder for at lave en ekstern reference, og derefter indtaste den resulterende streng i INDIRECT-funktionen for dynamisk at referere til opslagsarkene:

• INDIREKTE ({«'Øst'!$A$2:$A$6»; «'Syd'!$A$2:$A$6»; «'Nord'!$A$2:$A$6»; «'Vest'! $A$2:$A$6»})

COUNTIF kontrollerer hver celle i området A2:A6 i hvert opslagsark mod værdien i A2 i hovedarket og returnerer antallet af match for hvert ark.

I dette datasæt er ordrenummeret i A2 (101) i West leaf , som er nummer 4 ⁱ det navngivne område, så COUNTIF returnerer dette array:

• {0; 0; 0; 1}

Sammenlign derefter hvert element i ovenstående array med 0:

• –({0; 0; 0; 1}>0)

Dette producerer et array af TRUE (større end 0) og FALSE (lig med 0) værdier , som fremtvinger 1 og 0 ved at bruge en dobbelt unær (-), og du får følgende array som resultat:

• {0; 0; 0; 1}

Denne handling er en ekstra forholdsregel til at håndtere en situation, hvor et opslagsark indeholder flere forekomster af opslagsværdien. I så fald vil COUNTIF returnere et antal større end 1, hvorimod du kun ønsker 1'ere og 0'ere i det endelige array (du vil om et øjeblik forstå hvorfor).

Efter alle disse transformationer ser formlen sådan ud:

• OPSLAG($A2, INDIREKTE(«'»&INDEX(Opslagsark, MATCH(1, {0;0;0;1}, 0)) &»'!$A$2:$C$6»), 2, FALSK)

INDEX og MATCH

På dette tidspunkt bliver en klassisk INDEX STARTING- kombination:

• INDEX(Opslagsark, MATCH(1; {0;0;0;1}; 0))

Funktionen MATCH , der er indstillet til eksakt match (0 i det sidste argument), søger efter værdien 1 i arrayet {0; 0; 0; 1} og returnerer dens position, som er 4:

INDEX(Opslagsark, 4)

Funktionen INDEX bruger det tal, der returneres af MATCH, som rækkenummerargumentet (row_num) og returnerer den fjerde værdi i det navngivne område Lookup_sheets , som er West.

Så formlen reducerer yderligere til:

• OPSLAG($A2, INDIREKTE(«'»&»Vest»&»'!$A$2:$C$6»), 2, FALSK)

VLOOKUP og INDIREKTE

Funktionen INDIRECT behandler tekststrengen i den:

• INDIREKTE («'»&»Vest»&»'!$A$2:$C$6»)

Og den laver den om til en reference, der går til table_array- argumentet i VLOOKUP:

• VLOOKUP ($A2, 'West'!$A$2:$C$6, 2, FALSE)

Til sidst søger denne meget standard VLOOKUP-formel efter værdien A2 i den første kolonne i området A2:C6 på arket Vest og returnerer et match fra den anden kolonne.

Dynamisk VLOOKUP for at returnere data fra flere ark til forskellige celler

Lad os først definere præcis, hvad ordet "dynamisk" betyder i denne sammenhæng, og hvordan denne formel vil adskille sig fra de foregående.

I tilfælde af at du har store mængder data i det samme format, der er opdelt på tværs af flere regneark, vil du måske udtrække information fra forskellige ark til forskellige celler. Følgende billede illustrerer konceptet:

I modsætning til de tidligere formler, der hentede en værdi fra et specifikt ark baseret på en unik identifikator, vil du denne gang forsøge at udtrække værdier fra flere ark på én gang.

Der er to forskellige løsninger til denne opgave. I begge tilfælde skal du lave lidt forberedelse og oprette navngivne områder for datacellerne i hvert opslagsark. For dette eksempel definerer vi følgende intervaller:

• East_Sales- A2: B6 på østark
• North_Sales- A2: B6 på arket North
• Syd_Salg- A2: B6 på Syd-ark
• West_Sales- A2: B6 på det vestlige ark

VLOOKUP og indlejrede IF'er

Hvis du har et rimeligt antal ark at søge i, kan du bruge indbyggede HVIS-funktioner til at vælge arket baseret på nøgleord i foruddefinerede celler ( i dette tilfælde cellerne B1 til D1 ).

Med opslagsværdien i A2 er formlen som følger:

• =VOPSLAG ($A2, IF (B$1=»øst», East_Sales, IF (B$1=»nord», North_Sales, IF (B$1=»syd», South_Sales, IF (B$1=»vest», West_Sales) ))), 2, FALSK)

Oversat til engelsk siger IF- delen:

Hvis B1 er Øst , søg i området kaldet Øst_Salg ; hvis B1 er Nord , søg i området kaldet Nord_Salg ; hvis B1 er Syd , søg i området kaldet Syd_Salg ; og hvis B1 er Vest , søg i området kaldet Vest_Salg.

Det område, der returneres af HVIS, går til tabelmatrixen i VOPSLAG , som udtrækker en matchende værdi fra den anden kolonne i det tilsvarende ark.

Den intelligente brug af blandede referencer til opslagsværdien ($A2 – absolut kolonne og relativ række) og den logiske HVIS-test ($B$1 – relativ kolonne og absolut række) giver dig mulighed for at kopiere formlen til andre celler uden ændringer – Excel justerer referencerne automatisk baseret på den relative position af en række og kolonne.

Så du indtaster formlen i B2, kopierer den til højre og ned i så mange kolonner og rækker som nødvendigt, og du får følgende resultat:

INDIREKTE OPSLAG

Når man arbejder med mange ark, kan flere indbyggede niveauer gøre formlen for lang og svær at læse. En meget bedre måde er at oprette et dynamisk VLOOKUP-område ved hjælp af INDIRECT:

• =VOPSLAG ($A2, INDIREKTE (B$1&»_Salg»), 2, FALSK)

Her sammenkæder vi cellereferencen, der indeholder en unik del af det navngivne område (B1) og den fælles del (_Salg) . Dette producerer en tekststreng som "Øst_Salg" , som INDIREKT konverterer til et områdenavn, der er forståeligt af Excel.

Som et resultat får du en kompakt formel, der fungerer vidunderligt på et vilkårligt antal ark:

Du kan også være interesseret i at lære om: Sådan grupperer du en pivottabel efter måneder i Excel

Som du kan se, er dette måderne at bruge VLOOKUP-funktionen i Excel på, sammen med andre arkopslagsfunktioner som VLOOKUP, som kan være meget nyttige til at finde de data , du har brug for, på tværs af ark og filer i Excel. Vi håber, at dette har været nyttigt.