Gå til hovedindhold

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.


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:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

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 Præcis match og omtrentlig match VLOOKUP

 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:

Bemærk: Hvis opslagsværdien ikke findes i kolonnen længst til venstre, returnerer den fejlen #N/A.
 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.

  1. Klik Kutools > Super OPSLAG > LOOKUP mellem to værdier at aktivere denne funktion.
  2. Angiv derefter handlingerne fra dialogboksen baseret på dine data.
Bemærk: For at anvende denne funktion skal du downloade Kutools til Excel med 30-dages gratis prøveperiode for det første.


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.)
Tips:
  • 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.


Avancerede eksempler på VLOOKUP

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.

  1. Klik Kutools > Super OPSLAG > Multi-condition opslag at aktivere denne funktion.
  2. Angiv derefter handlingerne fra dialogboksen baseret på dine data.
Bemærk: For at anvende denne funktion skal du downloade Kutools til Excel med 30-dages gratis prøveperiode for det første.


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:


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:
doc vlookup funktion 50 1

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)),"")
    doc vlookup funktion 51 2

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.
doc vlookup funktion 53 1

Den generiske formel for indlejret VLOOKUP-funktion er:

=VLOOKUP(VLOOKUP(lookup_value, table_array1, col_index_num1, 0), table_array2, col_index_num2, 0)

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.
doc vlookup funktion 56 1

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.

  1. Klik Kutools > Super OPSLAG > OPSLAG og sum at aktivere denne funktion.
  2. Angiv derefter handlingerne fra dialogboksen baseret på dit behov.
Bemærk: For at anvende denne funktion skal du downloade Kutools til Excel med 30-dages gratis prøveperiode for det første.
 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:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

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:

Tips: I Excel 2016 eller nyere versioner kan du også bruge Power Query funktion til at flette to eller flere tabeller til en baseret på nøglekolonner. Klik for at kende detaljerne trin for trin.

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

  1. I regnearket indeholder de data, du vil VLOOKUP, højreklik på arkfanen og vælg Vis kode fra genvejsmenuen. Se skærmbillede:
  2. I åbnet Microsoft Visual Basic til applikationer vindue, skal du kopiere nedenstående VBA-kode til kodevinduet.
  3. VBA-kode 1: VLOOKUP for at få celleformatering sammen med opslagsværdi
  4. 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

  1. Stadig i Microsoft Visual Basic til applikationer vindue, skal du klikke på indsatte > Moduler, og kopier derefter nedenstående VBA-kode 2 til modulvinduet.
  2. VBA-kode 2: VLOOKUP for at få celleformatering sammen med opslagsværdi
  3. 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

  1. Når du har indsat ovenstående koder, skal du klikke Værktøj > Referencer i Microsoft Visual Basic til applikationer vindue. Kontroller derefter Microsoft Scripting Runtime afkrydsningsfeltet i Referencer - VBAProject dialog boks. Se skærmbilleder:
  2. Klik derefter på OK for at lukke dialogboksen og derefter gemme og lukke kodevinduet.

Trin 4: Indtast formlen for at få resultatet

  1. 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

  1. Hold nede i ALT + F11 nøgler til at åbne Microsoft Visual Basic til applikationer vindue.
  2. 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
  3. Gem og luk derefter kodevinduet.

Trin 2: Indtast formlen for at få resultatet

  1. 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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations