20+ VLOOKUP eksempler for Excel begyndere og avancerede brugere
VLOOKUP-funktionen er en af de mest populære funktioner i Excel. Denne tutorial vil introducere, hvordan du bruger VLOOKUP-funktionen i Excel med snesevis af grundlæggende og avancerede eksempler trin for trin.
Indholdsfortegnelse:
1. Introduktion af VLOOKUP-funktionen - syntaks og argumenter
2. Grundlæggende VLOOKUP-eksempler
- 2.1 Præcis match og omtrentlig match VLOOKUP
- 2.2 Versalfølsomt VLOOKUP
- 2.3 VLOOKUP fra højre mod venstre
- 2.4 VLOOKUP den anden, n'te eller sidste matchende værdi
- 2.5 VOPSLAG mellem to givne værdier eller datoer
- 2.6 Brug af jokertegn til delvise kampe i VLOOKUP-funktionen
- 2.7 VLOOKUP værdier fra et andet regneark
- 2.8 VLOOKUP værdier fra en anden projektmappe
- 2.9 VLOOKUP og returner tom eller specifik tekst i stedet for 0 eller #N/A fejlværdi
3. Avancerede eksempler på VLOOKUP
- 3.1 To-vejs opslag med VLOOKUP-funktion (VLOOKUP i række og kolonne)
- 3.2 VLOOKUP matchende værdi baseret på to eller flere kriterier
- 3.3 VLOOKUP for at returnere flere matchende værdier med en eller flere betingelser
- 3.4 VLOOKUP for at returnere hele eller hele rækken af en matchet celle
- 3.5 Udfør flere VLOOKUP-funktioner (indlejret VLOOKUP) i Excel
- 3.6 VLOOKUP for at kontrollere, om værdien eksisterer baseret på en listedata i en anden kolonne
- 3.7 VLOOKUP og sum alle matchede værdier i rækker eller kolonner
- 3.8 VLOOKUP for at flette to tabeller baseret på en eller flere nøglekolonner
- 3.9 VLOOKUP matchende værdier på tværs af flere regneark
Download VLOOKUP-eksempelfiler
Grundlæggende Vlookup eksempler | Eksempler på avanceret Vlookup | Vlookup fortsæt med celleformatering
Introduktion af VLOOKUP-funktionen - syntaks og argumenter
I Excel er VLOOKUP-funktionen en kraftfuld funktion for de fleste Excel-brugere, den giver dig mulighed for at lede efter en værdi længst til venstre i dataområdet og returnere en matchende værdi i samme række fra en kolonne, du har angivet som følgende skærmbillede. .
Syntaksen for VLOOKUP-funktionen:
argumenter:
Opslagsværdi (påkrævet): Den værdi, du vil søge efter. Det kan være en værdi (tal, dato eller tekst) eller cellereference. Det skal være i den første kolonne i table_array-området.
Tabelarray (påkrævet): Dataområdet eller tabellen, hvor opslagsværdikolonnen og resultatværdikolonnen er placeret.
Col_index_num (påkrævet): Kolonnenummeret, der indeholder returværdierne. Det starter med 1 fra kolonnen længst til venstre i tabelarrayet.
Range_lookup (valgfrit): En logisk værdi, der bestemmer, om denne VLOOKUP-funktion vil returnere et nøjagtigt match eller et omtrentligt match.
- Omtrentlig match – 1 / SAND / udeladt (standard): Hvis et eksakt match ikke findes, søger formlen efter det nærmeste match - den største værdi, der er mindre end opslagsværdien.
Varsel: I dette tilfælde skal du sortere opslagskolonnen (længst til venstre i dataområdet) i stigende rækkefølge, ellers vil den returnere et forkert eller #N/A fejlresultat. - Præcis match – 0 / FALSK: Dette bruges til at søge efter en værdi, der nøjagtigt svarer til opslagsværdien. Hvis der ikke findes et nøjagtigt match, returneres fejlværdien # N / A.
Funktionsnoter:
- Vlookup-funktionen søger kun efter en værdi fra venstre mod højre.
- Vlookup-funktionen udfører et opslags-ufølsomt opslag.
- Hvis der er flere matchende værdier baseret på opslagsværdien, vil kun den først matchede blive returneret ved at bruge Vlookup-funktionen.
Grundlæggende VLOOKUP-eksempler
I dette afsnit vil vi tale om nogle Vlookup-formler, du brugte ofte.
2.1.1 Lav en nøjagtig match VLOOKUP
Normalt, hvis du leder efter et nøjagtigt match med VLOOKUP-funktionen, skal du blot bruge FALSK som det sidste argument.
For eksempel, for at få de tilsvarende matematikscores baseret på de specifikke ID-numre, skal du gøre som dette:
Venligst kopier og indsæt nedenstående formel i en tom celle (her vælger jeg G2), og tryk Indtast nøgle for at få resultatet:
=VLOOKUP(F2,$A$2:$D$7,3,FALSE)
Bemærk: I ovenstående formel er der fire argumenter:
- F2 er den celle, der indeholder værdien C1005, du vil slå op;
- A2: D7 er det tabelarray, hvor du udfører opslag;
- 3 er kolonnenummeret, som din matchede værdi returneres fra; (Når funktionen finder ID'et - C1005, vil den gå til den tredje kolonne i tabelarrayet og returnere værdierne i samme række som ID'et - C1005. )
- FALSK henviser til det nøjagtige match.
Hvordan fungerer VLOOKUP-formlen?
Først søger den efter ID - C1005 i kolonnen længst til venstre i tabellen. Den går fra top til bund og finder værdien i celle A6.
Så snart den finder værdien, går den til højre i den tredje kolonne og udtrækker værdien i den.
Så du får resultatet som vist nedenfor skærmbillede:
Kutools til Excel kan prale af over 300 funktioner, At sikre, at det, du har brug for, kun er et klik væk...
2.1.2 Lav en omtrentlig match VLOOKUP
Det omtrentlige match er nyttigt til at søge værdier mellem dataområder. Hvis det nøjagtige match ikke findes, vil den omtrentlige VLOOKUP returnere den største værdi, der er mindre end opslagsværdien.
For eksempel, hvis du har følgende dataudvalg, og de angivne ordrer ikke er i kolonnen Ordrer, hvordan får du den nærmeste rabat i kolonne B?
Trin 1: Anvend VLOOKUP-formlen og udfyld den til andre celler
Kopier og indsæt følgende formel i en celle, hvor du vil placere resultatet, og træk derefter udfyldningshåndtaget ned for at anvende denne formel på andre celler.
=VLOOKUP(D2,$A$2:$B$9,2,TRUE)
Resultat:
Nu vil du få de omtrentlige matches baseret på de givne værdier, se skærmbillede:
Bemærkninger:
- I ovenstående formel:
- D2 er den værdi, du ønsker at returnere dens relative information;
- A2: B9 er dataområdet;
- 2 angiver kolonnenummeret, som din matchede værdi returneres;
- TRUE henviser til det omtrentlige match.
- Det omtrentlige match vil returnere den største værdi, der er mindre end din specifikke opslagsværdi, hvis der ikke findes et eksakt match.
- For at bruge VOPSLAG-funktionen til at få en omtrentlig matchværdi, skal du sortere kolonnen længst til venstre i dataområdet i stigende rækkefølge, ellers vil det returnere et forkert resultat.
2.2 Lav et versalfølsomt VLOOKUP i Excel
Som standard udfører VLOOKUP-funktionen et opslag ufølsomt, hvilket betyder, at den behandler små og store bogstaver som identiske. Nogle gange skal du muligvis udføre et opslagsfølsomt opslag i Excel, den normale VLOOKUP-funktion løser det muligvis ikke. I dette tilfælde kan du bruge alternative funktioner som INDEX og MATCH med EXACT-funktionen eller LOOKUP og EXACT-funktionerne.
For eksempel har jeg følgende dataområde, hvilken ID-kolonne indeholder tekststreng med store eller små bogstaver, nu vil jeg returnere den tilsvarende matematikscore for det givne ID-nummer.
Trin 1: Anvend en formel, og udfyld den til andre celler
Kopier og indsæt en af nedenstående formler i en tom celle, hvor du vil have resultatet. Vælg derefter formelcellen, træk udfyldningshåndtaget ned til de celler, hvor du vil udfylde denne formel.
Formel 1: Når du har indsat formlen, skal du trykke på Ctrl + Skift + Enter nøgler.
=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))
Formel 2: Når du har indsat formlen, skal du trykke på Indtast nøgle.
=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)
Resultat:
Så får du de rigtige resultater, du har brug for. Se skærmbillede:
Bemærkninger:
- I ovenstående formel:
- A2: A10 er den kolonne, som indeholder de specifikke værdier, du vil slå op i;
- F2 er opslagsværdien;
- C2: C10 er den kolonne, hvor resultatet returneres fra.
- Hvis der findes flere matches, vil denne formel altid returnere det sidste match.
2.3 VLOOKUP værdier fra højre mod venstre i Excel
Funktionen VOPSLAG søger altid efter en værdi i kolonnen længst til venstre i et dataområde og returnerer den tilsvarende værdi fra en kolonne til højre. Hvis du vil udføre et omvendt VLOOKUP, hvilket betyder at slå en specifik værdi op i højre kolonne og returnere dens tilsvarende værdi i venstre kolonne som vist nedenfor:
Klik for at kende detaljerne trin for trin om denne opgave ...
2.4 VLOOKUP den anden, n'te eller sidste matchende værdi i Excel
Normalt, hvis der findes flere matchende værdier ved brug af Vlookup-funktionen, vil kun den første matchede post blive returneret. I dette afsnit vil jeg tale om, hvordan man får den anden, n'te eller sidste matchende værdi i et dataområde.
2.4.1 VLOOKUP og returner den anden eller n'te matchende værdi
Antag, at du har en liste over navne i kolonne A, det uddannelseskursus, de købte i kolonne B. Nu søger du at finde det 2. eller n. uddannelseskursus købt af den givne kunde. Se skærmbillede:
Her løser VLOOKUP-funktionen muligvis ikke denne opgave direkte. Men du kan bruge INDEX-funktionen som et alternativ.
Trin 1: Anvend og udfyld formlen på andre celler
For eksempel, for at få den anden matchende værdi baseret på de givne kriterier, skal du anvende følgende formel i en tom celle og trykke på Ctrl + Skift + Enter nøgler sammen for at få det første resultat. Og vælg derefter formelcellen, træk udfyldningshåndtaget ned til de celler, hvor du vil udfylde denne formel.
=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))
Resultat:
Nu er alle de andre matchede værdier baseret på de givne navne blevet vist på én gang.
Bemærk: I ovenstående formel:
- A2: A14 er området med alle værdierne for opslag;
- B2: B14 er intervallet for de matchende værdier, du vil vende tilbage fra;
- E2 er opslagsværdien;
- 2 angiver den anden matchede værdi, du ønsker at få, for at returnere den tredje matchende værdi, skal du blot ændre den til 3.
2.4.2 VLOOKUP og returner den sidste matchende værdi
Hvis du vil slå op og returnere den sidste matchende værdi som vist nedenstående skærmbillede, dette VLOOKUP og returner den sidste matchende værdi tutorial kan hjælpe dig med at få den sidste matchende værdi i detaljer.
2.5 VLOOKUP matchende værdier mellem to givne værdier eller datoer
Nogle gange vil du måske slå værdier op mellem to værdier eller datoer og returnere de tilsvarende resultater som vist på skærmbilledet nedenfor. I sådanne tilfælde kan du bruge OPSLAG-funktionen i stedet for OPSLAG-funktionen med en sorteret tabel.
2.5.1 VLOOKUP, der matcher værdier mellem to givne værdier eller datoer med formel
Trin 1: Arranger dataene og anvend følgende formel
Din oprindelige tabel skal være et sorteret dataområde. Og kopier derefter eller indtast følgende formel i en tom celle. Træk derefter udfyldningshåndtaget for at udfylde denne formel til andre celler, du har brug for.
=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)
Resultat:
Og nu vil du få alle matchede poster baseret på den givne værdi, se skærmbillede:
Bemærkninger:
- I ovenstående formel:
- A2: A6 er intervallet af mindre værdier;
- B2: B6 er rækken af større tal;
- E2 er opslagsværdien, som du vil have dens tilsvarende værdi;
- C2: C6 er den kolonne, hvorfra du vil returnere en tilsvarende værdi.
- Denne formel kan også bruges til at udtrække matchede værdier mellem to datoer som vist nedenfor skærmbillede:
2.5.2 VLOOKUP matchende værdier mellem to givne værdier eller datoer med en praktisk funktion
Hvis du har svært ved at huske og forstå ovenstående formel, vil jeg her introducere et nemt værktøj – Kutools til Excel, Med LOOKUP mellem to værdier funktion, kan du nemt returnere den tilsvarende vare baseret på den specifikke værdi eller dato mellem to værdier eller datoer.
- Klik Kutools > Super OPSLAG > LOOKUP mellem to værdier at aktivere denne funktion.
- Angiv derefter handlingerne fra dialogboksen baseret på dine data.
2.6 Brug af jokertegn til delvise kampe i VLOOKUP-funktionen
I Excel kan jokertegnene bruges inden for VLOOKUP-funktionen, som giver dig mulighed for at udføre en delvis match på en opslagsværdi. For eksempel kan du bruge VLOOKUP til at returnere matchet værdi fra en tabel baseret på en del af en opslagsværdi.
Antag, jeg har en række data som vist nedenstående skærmbillede, nu vil jeg udtrække scoren baseret på fornavnet (ikke det fulde navn). Hvordan kunne løse denne opgave i Excel?
Trin 1: Anvend og udfyld formlen på andre celler
Kopier eller indtast følgende formel i en tom celle, og træk derefter udfyldningshåndtaget for at udfylde denne formel til andre celler, du har brug for:
=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)
Resultat:
Og alle de matchede resultater er blevet returneret som vist nedenfor skærmbillede:
Bemærk: I ovenstående formel:
- E2 & ”*” er kriterierne for den delvise matematik. Det betyder, at du leder efter enhver værdi, der starter med værdien i celle E2. (Jokertegnet "*" angiver et hvilket som helst tegn eller tegn)
- A2: C11 er det dataområde, hvor du vil søge efter den matchede værdi;
- 3 betyder at returnere den matchende værdi fra den 3. kolonne i dataområdet;
- False angiver den nøjagtige matematik. (Når du bruger jokertegn, skal du indstille det sidste argument i funktionen som FALSK eller 0 for at aktivere eksakt match-tilstand i VOPSLAG-funktionen.)
- For at finde og returnere de matchende værdier, der slutter med en bestemt værdi, skal du sætte jokertegnet "*" foran værdien. Anvend venligst denne formel:
-
=VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)
- For at slå op og returnere den matchede værdi baseret på en del af tekststrengen, uanset om den angivne tekst er i bebinningen, slutningen eller i midten af tekststrengen, skal du blot omslutte cellereferencen eller teksten med to stjerner (*) på begge sider. Gør venligst med denne formel
-
=VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)
2.7 VLOOKUP værdier fra et andet regneark
Normalt skal du muligvis arbejde med mere end et regneark, funktionen VOPSLAG kan bruges til at slå data fra et andet ark på samme måde som på et regneark.
For eksempel har du to regneark som vist nedenstående skærmbillede for at slå op og returnere de tilsvarende data fra det regneark, du har angivet, skal du gøre med følgende trin:
Trin 1: Anvend og udfyld formlen på andre celler
Indtast eller kopier nedenstående formel ind i en tom celle, hvor du ønsker at få de matchede varer. Træk derefter udfyldningshåndtaget ned til de celler, som du vil anvende denne formel.
=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)
Resultat:
Du får de tilsvarende resultater, som du har brug for, se skærmbillede:
Bemærk: I ovenstående formel:
- A2 repræsenterer opslagsværdien;
- 'Datablad'!A2:C15 angiver at søge i værdierne fra området A2:C15 på arbejdsarket med navnet Dataark; (Hvis arknavnet indeholder mellemrum eller tegnsætningstegn, skal du sætte arknavnet i enkelte anførselstegn, ellers kan du direkte bruge arknavnet som f.eks. =OPSLAG(A2,Datablad!$A$2:$C$15,3,0) ).
- 3 er kolonnenummeret, som indeholder matchede data, du ønsker at returnere fra;
- 0 betyder at udføre et nøjagtigt match.
2.8 VLOOKUP værdier fra en anden projektmappe
Denne sektion vil tale om opslag og returnere de matchende værdier fra en anden projektmappe ved at bruge VOPSLAG-funktionen.
Lad os f.eks. sige, at du har to projektmapper. Den første projektmappe indeholder en liste over produkter og deres respektive omkostninger. I den anden projektmappe vil du udtrække de tilsvarende omkostninger for hvert produktelement som vist nedenfor.
Trin 1: Anvend og udfyld formlen
Åbn begge de projektmapper, du vil bruge, og anvend derefter følgende formel i en celle, hvor du vil placere resultatet i den anden projektmappe. Træk og kopier derefter denne formel til andre celler, du har brug for
=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)
Resultat:
Bemærkninger:
- I ovenstående formel:
- B2 repræsenterer opslagsværdien;
- '[Produktliste.xlsx]Sheet1'!A2:B6 angiver at søge fra området A2:B6 på arket med navnet Sheet1 fra projektmappen Produktliste; (Referencen til projektmappe er omgivet af firkantede parenteser, og hele projektmappen + ark er omgivet af enkelte anførselstegn.)
- 2 er kolonnenummeret, som indeholder matchede data, du ønsker at returnere fra;
- 0 angiver at returnere et nøjagtigt match.
- Hvis opslagsprojektmappen er lukket, vil den fulde filsti til opslagsprojektmappen blive vist i formlen som følgende skærmbillede vist:
2.9 Returner tom eller specifik tekst i stedet for 0 eller #N/A fejl
Normalt, når du bruger SLAG OP-funktionen til at returnere en tilsvarende værdi, hvis den matchende celle er tom, vil den returnere 0. Og hvis den matchende værdi ikke findes, vil du få en fejlværdi på #N/A som vist i skærmbillede nedenfor. Hvis du ønsker at vise en tom celle eller en specifik værdi i stedet for 0 eller #N/A, dette VLOOKUP For at returnere tom eller specifik værdi i stedet for 0 eller N/A tutorial kan gøre dig en tjeneste.
3.1 To-vejs opslag (VLOOKUP i række og kolonne)
Nogle gange skal du muligvis udføre et 2-dimensionelt opslag, hvilket betyder at søge efter en værdi i både række og kolonne på samme tid. For eksempel, hvis du har følgende datainterval, og du skal muligvis få værdien for et bestemt produkt i et bestemt kvartal. Dette afsnit introducerer en formel til at håndtere dette job i Excel.
I Excel kan du bruge en kombination af VLOOKUP og MATCH funktioner til at lave et tovejs opslag.
Anvend venligst følgende formel i en tom celle, og tryk derefter på Indtast nøgle for at få resultatet.
=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)
Bemærk: I ovenstående formel:
- G2 er opslagsværdien i kolonnen, som du ønsker at få den tilsvarende værdi baseret på;
- A2: E7 er den datatabel, du vil se ud fra;
- H1 er opslagsværdien i rækken, som du ønsker at få den tilsvarende værdi baseret på;
- A2: E2 er cellerne i kolonneoverskrifter;
- FALSK angiver for at få et nøjagtigt match.
3.2 VLOOKUP matchende værdi baseret på to eller flere kriterier
Det er nemt for dig at slå matchningsværdien op ud fra ét kriterium, men hvis du har to eller flere kriterier, hvad kan du så gøre?
3.2.1 VLOOKUP matchende værdi baseret på to eller flere kriterier med formler
I dette tilfælde kan LOOKUP eller MATCH og INDEX funktionerne i Excel hjælpe dig med at løse dette job hurtigt og nemt.
For eksempel har jeg nedenstående datatabel for at returnere den matchede pris baseret på det specifikke produkt og størrelse, kan følgende formler hjælpe dig.
Trin 1: Anvend en hvilken som helst formel
Formel 1: Når du har indsat formlen, skal du trykke på Indtast nøgle.
=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))
Formel 2: Når du har indsat formlen, skal du trykke på Ctrl + Skift + Enter nøgler.
=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))
Resultat:
Bemærkninger:
- I ovenstående formler:
- A2: A12 = G1 betyder at søge efter kriterierne for G1 i området A2:A12;
- B2: B12 = G2 betyder at søge efter kriterierne for G2 i området B2:B12;
- D2: D12 is det område, som du vil returnere den tilsvarende værdi fra.
- Hvis du har mere end to kriterier, skal du blot tilføje de andre kriterier i formlen, såsom:
=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
3.2.2 VLOOKUP matchende værdi baseret på to eller flere kriterier med en smart funktion
Det kan være udfordrende at huske ovenstående komplekse formler, der skal anvendes gentagne gange, hvilket kan bremse din arbejdseffektivitet. Imidlertid, Kutools til Excel tilbyder en Multi-condition opslag funktion, der giver dig mulighed for at returnere det tilsvarende resultat baseret på en eller flere betingelser med blot flere klik.
- Klik Kutools > Super OPSLAG > Multi-condition opslag at aktivere denne funktion.
- Angiv derefter handlingerne fra dialogboksen baseret på dine data.
3.3 VLOOKUP for at returnere flere værdier med et eller flere kriterier
I Excel søger VLOOKUP-funktionen efter en værdi og returnerer kun den første matchende værdi, hvis der er fundet flere tilsvarende værdier. Nogle gange vil du måske returnere alle de tilsvarende værdier i en række, i en kolonne eller i en enkelt celle. Dette afsnit vil tale om, hvordan man returnerer de flere matchende værdier med en eller flere betingelser i en projektmappe.
3.3.1 VLOOKUP alle matchende værdier baseret på en eller flere betingelser horisontalt
Forudsat at du har en tabel med data, der indeholder land, by og navne i området A1:C14, og nu vil du returnere alle navne vandret, som er fra "US", som vist nedenfor. For at løse denne opgave, tak klik her for at få resultatet trin for trin.
3.3.2 VLOOKUP alle matchende værdier baseret på en eller flere betingelser lodret
Hvis du har brug for at Vlookup og returnere alle matchende værdier lodret baseret på specifikke kriterier som vist nedenfor skærmbillede, klik venligst her for at få løsningen i detaljer.
3.3.3 VLOOKUP alle matchende værdier baseret på en eller flere betingelser i en enkelt celle
Hvis du vil Vlookup og returnere flere matchede værdier i en enkelt celle med specificeret separator, den nye funktion i TEXTJOIN kan hjælpe dig med at løse dette job hurtigt og nemt.
Bemærkninger:
- TEXTJOIN-funktionen er kun tilgængelig i Excel 2019, Excel 365 og senere versioner.
- Hvis du bruger Excel 2016 og tidligere versioner, skal du bruge den brugerdefinerede funktion i nedenstående artikel:
- Vlookup returnerer flere værdier i en celle i Excel
3.4 VLOOKUP for at returnere hele rækken af en matchet celle
I dette afsnit vil jeg tale om, hvordan man henter hele rækken af en matchet værdi ved at bruge VLOOKUP-funktionen.
Trin 1: Anvend og udfyld følgende formel
Kopier eller skriv nedenstående formel i en tom celle, hvor du vil udskrive resultatet, og tryk Indtast nøgle for at få den første værdi. Træk derefter formelcellen til højre, indtil dataene for hele rækken vises.
=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)
Resultat:
Nu kan du se, at hele rækkedata er returneret. Se skærmbillede:
Bemærk: i ovenstående formel:
- F2 er den opslagsværdi, du vil returnere hele rækken baseret på;
- A1: D12 er det dataområde, du vil søge efter opslagsværdien fra;
- A1 angiver det første kolonnenummer inden for dit dataområde;
- FALSK angiver nøjagtigt opslag.
tips:
- Hvis der findes flere rækker baseret på den matchede værdi, for at returnere alle de tilsvarende rækker, skal du anvende nedenstående formel og derefter trykke på Ctrl + Skift + Enter nøgler sammen for at få det første resultat. Træk derefter udfyldningshåndtaget til højre. Og fortsæt derefter med at trække udfyldningshåndtaget ned over cellerne for at få alle matchende rækker. Se demoen herunder:
=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
3.5 Indlejret VLOOKUP i Excel
Nogle gange kan det være nødvendigt at slå værdier op, som er forbundet på tværs af flere tabeller. I dette tilfælde kan du indlejre flere VLOOKUP-funktioner sammen for at få den endelige værdi.
For eksempel har jeg et regneark, der indeholder to separate tabeller. Den første tabel viser alle produktnavne sammen med deres tilsvarende sælger. Den anden tabel viser det samlede salg for hver sælger. Nu, hvis du ønsker at finde salget af hvert produkt, som vist på det følgende skærmbillede, kan du indlejre VLOOKUP-funktionen for at udføre denne opgave.
Den generiske formel for indlejret VLOOKUP-funktion er:
Bemærk:
- opslagsværdi er den værdi, du leder efter;
- Tabel_array1, Tabel_array2 er de tabeller, hvori opslagsværdien og returværdien findes;
- col_index_num1 angiver kolonnenummeret i den første tabel til at finde de mellemliggende fælles data;
- col_index_num2 angiver kolonnenummeret i den anden tabel, som du ønsker at returnere den matchende værdi;
- 0 bruges til et nøjagtigt match.
Trin 1: Anvend og udfyld følgende formel
Anvend venligst følgende formel i en tom celle, og træk derefter udfyldningshåndtaget ned til de celler, som du vil anvende denne formel.
=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)
Resultat:
Nu får du resultatet som vist på følgende skærmbillede:
Bemærk: i ovenstående formel:
- G3 indeholder den værdi, du leder efter;
- A3: B7, D3: E7 er de tabelområder, hvori opslagsværdien og returværdien findes;
- 2 er kolonnenummeret i området, som den matchende værdi skal returneres fra.
- 0 angiver VLOOKUP nøjagtig matematik.
3.6 Kontroller, om der findes værdi baseret på en listedata i en anden kolonne
VLOOKUP-funktionen kan også hjælpe dig med at kontrollere, om der findes værdier baseret på datalisten i en anden kolonne. For eksempel, hvis du vil lede efter navnene i kolonne C og bare returnere Ja eller Nej, hvis navnet er fundet eller ej i kolonne A som vist nedenfor.
Trin 1: Anvend og udfyld følgende formel
Anvend venligst følgende formel i en tom celle, og træk derefter udfyldningshåndtaget ned til de celler, som du vil udfylde denne formel.
=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")
Resultat:
Og du får resultatet, som du har brug for, se skærmbillede:
Bemærk: i ovenstående formel:
- C2 er den opslagsværdi, du vil kontrollere;
- A2: A10 er listen over rækkevidde, hvorfra man skal kontrollere, om opslagsværdierne bliver fundet eller ej;
- FALSK angiver for at få et nøjagtigt match.
3.7 VLOOKUP og sum alle matchede værdier i rækker eller kolonner
Når du arbejder med numeriske data, skal du muligvis udtrække matchede værdier fra en tabel og summere tallene i flere kolonner eller rækker. Dette afsnit introducerer nogle formler, der kan hjælpe dig med at udføre denne opgave.
3.7.1 VLOOKUP og sum alle matchede værdier i en række eller flere rækker
Antag, at du har en produktliste med salg i flere måneder, som vist på det følgende skærmbillede. Nu skal du summere alle ordrer i alle måneder baseret på de givne produkter.
Trin 1: Anvend og udfyld følgende formel
Kopier eller indtast følgende formel i en tom celle, og tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få det første resultat. Træk derefter udfyldningshåndtaget ned for at kopiere denne formel til andre celler, du har brug for.
=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))
Resultat:
Alle værdierne i en række af den første matchende værdi er blevet summeret sammen, se skærmbillede:
Bemærk: i ovenstående formel:
- H2 er cellen, der indeholder den værdi, du leder efter;
- A2: F9 er dataområdet (uden kolonneoverskrifter), som inkluderer opslagsværdien og de matchede værdier;
- 2,3,4,5,6 {} er kolonnenumre, der bruges til at beregne totalen af området;
- FALSK angiver et nøjagtigt match.
tips: Hvis du vil summere alle matches i flere rækker, skal du bruge følgende formel:
-
=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
3.7.2 VLOOKUP og sum alle matchede værdier i en kolonne eller flere kolonner
Hvis du vil summere den samlede værdi for de specifikke måneder som vist på skærmbilledet nedenfor. Den normale VLOOKUP-funktion hjælper dig muligvis ikke, her skal du anvende funktionerne SUM, INDEX og MATCH sammen for at skabe en formel.
Trin 1: Anvend følgende formel
Anvend nedenstående formel i en tom celle, og træk derefter udfyldningshåndtaget ned for at kopiere denne formel til andre celler.
=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))
Resultat:
Nu er de første matchede værdier baseret på den specifikke måned i en kolonne blevet summeret sammen, se skærmbillede:
Bemærk: i ovenstående formel:
- H2 er cellen, der indeholder den værdi, du leder efter;
- B1: F1 er kolonneoverskrifterne, der indeholder opslagsværdien;
- B2: F9 er det dataområde, der indeholder de numeriske værdier, som du vil summere.
tips: For at VLOOKUP og summere alle matchede værdier i flere kolonner, skal du bruge følgende formel:
-
=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
3.7.3 VLOOKUP og sum de første matchede eller alle matchede værdier med en kraftfuld funktion
Måske er ovenstående formler svære for dig at huske, i dette tilfælde vil jeg anbefale en kraftfuld funktion - Opslag og sum of Kutools til Excel, med denne funktion kan du Vlookup og summere den første matchende eller alle matchende værdier i rækker eller kolonner så nemt som muligt.
- Klik Kutools > Super OPSLAG > OPSLAG og sum at aktivere denne funktion.
- Angiv derefter handlingerne fra dialogboksen baseret på dit behov.
3.7.4 VLOOKUP og sum alle matchede værdier både i rækker og kolonner
Hvis du vil sammenfatte værdierne, når du f.eks. Skal matche både kolonne og række, for at få den samlede værdi af produkttrøjen i marts måned som vist nedenstående skærmbillede.
Her kan du bruge SUMPRODCT-funktionen til at udføre denne opgave.
Anvend venligst følgende formel i en celle, og tryk derefter på Indtast nøgle for at få resultatet, se skærmbillede:
=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))
Bemærk: I ovenstående formel:
- B2: F9 er dataområdet, der indeholder de numeriske værdier, som du vil summere;
- B1: F1 er kolonneoverskrifterne, der indeholder opslagsværdien, som du vil summere baseret på;
- I2 er opslagsværdien inden for de kolonneoverskrifter, du leder efter;
- A2: A9 er rækkeoverskrifterne, der indeholder opslagsværdien, som du vil summere baseret på;
- H2 er opslagsværdien i de rækkeoverskrifter, du leder efter.
3.8 VLOOKUP for at flette to tabeller baseret på nøglekolonner
I dit daglige arbejde, når du analyserer data, skal du muligvis samle alle nødvendige oplysninger i en enkelt tabel baseret på en eller flere nøglekolonner. For at udføre denne opgave kan du bruge funktionerne INDEX og MATCH i stedet for OPSLAG-funktionen.
3.8.1 VLOOKUP for at flette to tabeller baseret på en nøglekolonne
For eksempel har du to tabeller, den første tabel indeholder produkt- og navnedataene, og den anden tabel indeholder produkt- og ordredata, nu vil du kombinere disse to tabeller ved at matche den fælles produktkolonne til én tabel.
Trin 1: Anvend og udfyld følgende formel
Anvend venligst følgende formel i en tom celle. Træk derefter udfyldningshåndtaget ned til de celler, som du vil anvende denne formel
=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))
Resultat:
Nu vil du få en flettet tabel, hvor rækkefølgekolonnen slutter sig til den første tabel baseret på nøglekolonnens data.
Bemærk: I ovenstående formel:
- A2 er den opslagsværdi, du leder efter;
- F2: F8 er rækkevidde af data, som du vil returnere de matchende værdier;
- E2: E8 er opslagsområde, der indeholder opslagsværdien.
3.8.2 VLOOKUP for at flette to tabeller baseret på flere nøglekolonner
Hvis de to tabeller, du vil tilslutte dig, har flere nøglekolonner, skal du følge trinene nedenfor for at flette tabellerne baseret på disse fælles kolonner.
Den generiske formel er:
Bemærk:
- opslagstabel er dataområdet, der indeholder opslagsdata og matchende poster;
- opslagsværdi1 er det første kriterium, du leder efter;
- opslagsområde1 er datalisten indeholder de første kriterier;
- opslagsværdi2 er det andet kriterium, du leder efter;
- opslagsområde2 er datalisten indeholder det andet kriterium;
- return_column_number angiver kolonnenummeret i opslagstabellen, som du vil returnere den matchende værdi.
Trin 1: Anvend følgende formel
Anvend venligst formlen nedenfor i en tom celle, hvor du vil placere resultatet, og tryk derefter Ctrl + Skift + Enter nøgler sammen for at få den første matchede værdi, se skærmbillede:
=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)
Trin 2: Udfyld formlen til andre celler
Vælg derefter den første formelcelle, og træk i udfyldningshåndtaget for at kopiere denne formel til andre celler efter behov:
3.9 VLOOKUP matchende værdier på tværs af flere regneark
Har du nogensinde haft brug for at udføre en VLOOKUP på tværs af flere regneark i Excel? For eksempel, hvis du har tre regneark med dataområder, og du ønsker at hente specifikke værdier baseret på kriterier fra disse ark, kan du følge trin-for-trin vejledningen VLOOKUP værdier på tværs af flere regneark at udføre denne opgave.
VLOOKUP-matchede værdier beholder celleformatering
Når du slår matchede værdier op, bevares den originale celleformatering såsom skriftfarve, baggrundsfarve, dataformat osv. ikke. For at beholde cellen eller dataformateringen vil dette afsnit introducere nogle tricks til at løse opgaverne.
4.1 VLOOKUP matchende værdi og behold cellefarve, skrifttypeformatering
Som vi alle ved, kan den normale VLOOKUP-funktion kun hente den matchende værdi fra et andet dataområde. Der kan dog være tilfælde, hvor du gerne vil have den tilsvarende værdi sammen med celleformateringen, såsom fyldfarve, skriftfarve og skrifttype. I dette afsnit vil vi diskutere, hvordan man henter matchende værdier, mens kildeformateringen bevares i Excel.
Gør med følgende trin for at slå op og returnere den tilsvarende værdi sammen med celleformatering:
Trin 1: Kopier koden 1 ind i arkkodemodulet
- I regnearket indeholder de data, du vil VLOOKUP, højreklik på arkfanen og vælg Vis kode fra genvejsmenuen. Se skærmbillede:
- I åbnet Microsoft Visual Basic til applikationer vindue, skal du kopiere nedenstående VBA-kode til kodevinduet.
- VBA-kode 1: VLOOKUP for at få celleformatering sammen med opslagsværdi
-
Sub Worksheet_Change(ByVal Target As Range) 'Updateby Extendoffice Dim I As Long Dim xKeys As Long Dim xDicStr As String On Error Resume Next Application.ScreenUpdating = False xKeys = UBound(xDic.Keys) If xKeys >= 0 Then For I = 0 To UBound(xDic.Keys) xDicStr = xDic.Items(I) If xDicStr <> "" Then Range(xDic.Keys(I)).Interior.Color = _ Range(xDic.Items(I)).Interior.Color Range(xDic.Keys(I)).Font.FontStyle = _ Range(xDic.Items(I)).Font.FontStyle Range(xDic.Keys(I)).Font.Size = _ Range(xDic.Items(I)).Font.Size Range(xDic.Keys(I)).Font.Color = _ Range(xDic.Items(I)).Font.Color Range(xDic.Keys(I)).Font.Name = _ Range(xDic.Items(I)).Font.Name Range(xDic.Keys(I)).Font.Underline = _ Range(xDic.Items(I)).Font.Underline Else Range(xDic.Keys(I)).Interior.Color = xlNone End If Next Set xDic = Nothing End If Application.ScreenUpdating = True End Sub
Trin 2: Kopier koden 2 ind i modulvinduet
- Stadig i Microsoft Visual Basic til applikationer vindue, skal du klikke på indsatte > Moduler, og kopier derefter nedenstående VBA-kode 2 til modulvinduet.
- VBA-kode 2: VLOOKUP for at få celleformatering sammen med opslagsværdi
-
Public xDic As New Dictionary Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long) Dim xFindCell As Range On Error Resume Next Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole) If xFindCell Is Nothing Then LookupKeepFormat = "" xDic.Add Application.Caller.Address, "" Else LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address End If End Function
Trin 3: Vælg muligheden for VBAproject
- Når du har indsat ovenstående koder, skal du klikke Værktøjer > Referencer i Microsoft Visual Basic til applikationer vindue. Kontroller derefter Microsoft Scripting Runtime afkrydsningsfeltet i Referencer - VBAProject dialog boks. Se skærmbilleder:
- Klik derefter på OK for at lukke dialogboksen og derefter gemme og lukke kodevinduet.
Trin 4: Indtast formlen for at få resultatet
- Gå nu tilbage til regnearket, anvend følgende formel. Og træk derefter udfyldningshåndtaget ned for at få alle resultater sammen med deres formatering. Se skærmbillede:
=LookupKeepFormat(E2,$A$1:$C$10,3)
Bemærk: i ovenstående formel:
- E2 er den værdi, du vil slå op;
- A1: C10 er bordsortimentet;
- 3 er kolonnenummeret på den tabel, hvorfra du vil hente den matchede værdi.
4.2 Behold datoformatet fra en VLOOKUP returneret værdi
Når du bruger funktionen VOPSLAG til at slå op og returnere en værdi med datoformat, kan det returnerede resultat blive vist som et tal. For at beholde datoformatet i det returnerede resultat, bør du omslutte UPLOOK-funktionen i TEKST-funktionen.
Trin 1: Anvend og udfyld følgende formel
Anvend venligst formlen nedenfor i en tom celle. Træk derefter udfyldningshåndtaget for at kopiere denne formel til andre celler.
=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")
Resultat:
Alle matchede datoer er blevet returneret som vist nedenfor skærmbillede:
Bemærk: I ovenstående formel:
- E2 er opslagsværdien;
- A2: C9 er opslagsområdet;
- 3 er det kolonnenummer, du ønsker værdien returneret;
- FALSK angiver at få et nøjagtigt match;
- mm / dd / åååå er det datoformat, du vil beholde.
4.3 Returner cellekommentar fra VLOOKUP
Har du nogensinde haft brug for at hente både de matchende celledata og dens tilknyttede kommentar ved hjælp af VLOOKUP i Excel, som vist på følgende skærmbillede? Hvis det er tilfældet, kan den brugerdefinerede funktion nedenfor hjælpe dig med at udføre denne opgave.
Trin 1: Kopier koden til et modul
- Hold nede i ALT + F11 nøgler til at åbne Microsoft Visual Basic til applikationer vindue.
- Klik indsatte > Moduler, kopier og indsæt derefter følgende kode i modulvinduet.
VBA-kode: Vlookup og returner matchende værdi med cellekommentar:Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant 'Updateby Extendoffice Application.Volatile Dim xRet As Variant 'could be an error Dim xCell As Range xRet = Application.Match(LookVal, FTable.Columns(1), FType) If IsError(xRet) Then VlookupComment = "Not Found" Else Set xCell = FTable.Columns(FColumn).Cells(1)(xRet) VlookupComment = xCell.Value With Application.Caller If Not .Comment Is Nothing Then .Comment.Delete End If If Not xCell.Comment Is Nothing Then .AddComment xCell.Comment.Text End If End With End If End Function
- Gem og luk derefter kodevinduet.
Trin 2: Indtast formlen for at få resultatet
- Indtast nu følgende formel, og træk udfyldningshåndtaget for at kopiere denne formel til andre celler. Det vil returnere både de matchede værdier og kommentarer samtidigt, se skærmbillede:
=vlookupcomment(D2,$A$2:$B$9,2,FALSE)
Bemærk: I ovenstående formel:
- D2 er den opslagsværdi, du vil returnere dens tilsvarende værdi;
- A2: B9 er den datatabel, du vil bruge;
- 2 er kolonnenummeret, som indeholder den matchede værdi, du ønsker at returnere;
- FALSK angiver for at få et nøjagtigt match.
4.4 VLOOKUP-numre gemt som tekst
For eksempel har jeg en række data, hvor ID-nummeret i den originale tabel er i talformat, og ID-nummeret i opslagscellerne er gemt som tekst, kan du støde på en #N/A-fejl, når du bruger den normale VLOOKUP-funktion. I dette tilfælde, for at hente de korrekte oplysninger, kan du ombryde funktionerne TEKST og VÆRDI i VOPSLAG-funktionen. Nedenfor er formlen for at opnå dette:
Trin 1: Anvend og udfyld følgende formel
Anvend venligst følgende formel i en tom celle, og træk derefter udfyldningshåndtaget ned for at kopiere denne formel.
=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))
Resultat:
Nu vil du få de korrekte resultater som vist nedenfor skærmbillede:
Bemærkninger:
- I ovenstående formel:
- D2 er den opslagsværdi, du vil returnere dens tilsvarende værdi;
- A2: B8 er den datatabel, du vil bruge;
- 2 er kolonnenummeret, som indeholder den matchede værdi, du ønsker at returnere;
- 0 angiver for at få et nøjagtigt match.
- Denne formel fungerer også godt, hvis du ikke er sikker på, hvor du har tal, og hvor du har tekst.
Bedste kontorproduktivitetsværktøjer
Overlad dine Excel-færdigheder med Kutools til Excel, og oplev effektivitet som aldrig før. Kutools til Excel tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid. Klik her for at få den funktion, du har mest brug for...
Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere
- Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
- Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
- Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
Indholdsfortegnelse
- 1. Introduktion af VLOOKUP-funktionen
- 2. Grundlæggende VLOOKUP-eksempler
- 2.1 Præcis og omtrentlig Vlookup
- Præcis kamp
- Omtrentlig kamp
- 2.2 Der skelnes mellem store og små bogstaver i Vlookup
- 2.3 Vlookup fra højre mod venstre
- 2.4 Vlookup den anden, n'te eller sidste matchende værdi
- Den anden eller n'te matchende værdi
- Den sidste matchende værdi
- 2.5 Vopslag mellem to værdier
- Ved at bruge formel
- Ved at bruge en praktisk funktion - Kutools
- 2.6 Delvis match Vlookup
- 2.7 Vlookup fra et andet regneark
- 2.8 Vlookup fra en anden projektmappe
- 2.9 Ret 0 eller #N/A fejlværdi i Vlookup
- 3. Avancerede eksempler på VLOOKUP
- 3.1 To-vejs opslag
- 3.2 Vlookup baseret på flere kriterier
- Ved hjælp af formler
- Ved at bruge en smart funktion - Kutools
- 3.3 Vlookup flere matchende værdier
- Returner værdier vandret
- Returner værdier lodret
- Returner værdier i én celle
- 3.4 Vlookup hele rækken
- 3.5 Indlejret Vlookup
- 3.6 Tjek om værdien findes
- 3.7 Vlookup og sum
- I rækker
- I kolonner
- Med en kraftfuld funktion - Kutools
- Både i rækker og kolonner
- 3.8 Vlookup for at flette to tabeller
- Ved en nøglekolonne
- Ved flere nøglekolonner
- 3.9 Vlookup på tværs af flere regneark
- 4. VLOOKUP og fortsæt med celleformatering
- 4.1 Behold farve og skrifttypeformatering
- 4.2 Behold datoformatet
- 4.3 Behold en cellekommentar
- 4.4 Tal gemt som tekst
- De bedste Office-produktivitetsværktøjer
- Kommentarer