Note: The other languages of the website are Google-translated. Back to English
Log på  \/ 
x
or
x
Tilmeld  \/ 
x

or

VLOOKUP-funktion med nogle grundlæggende og avancerede eksempler i Excel

I Excel er VLOOKUP-funktionen en kraftig funktion for de fleste Excel-brugere, som bruges til at lede efter en værdi i det venstre venstre af dataområdet og returnere en matchende værdi i samme række fra en kolonne, du har angivet som vist nedenstående skærmbillede. . Denne vejledning taler om, hvordan du bruger VLOOKUP-funktionen med nogle grundlæggende og avancerede eksempler i Excel.

Indholdsfortegnelse:

1. Introduktion af VLOOKUP-funktionen - syntaks og argumenter

2. Grundlæggende VLOOKUP-eksempler

3. Avancerede eksempler på VLOOKUP

4. VLOOKUP-matchede værdier bevarer celleformatering

5. Download VLOOKUP-eksempelfiler


Introduktion af VLOOKUP-funktionen - syntaks og argumenter

Syntaksen for VLOOKUP-funktionen:

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

argumenter:

Opslagsværdi: Den værdi, du vil søge efter. Det skal være i den første kolonne i tabel_array-området.

Table_array: Dataområdet eller -tabellen, hvor opslagsværdikolonnen og resultatværdikolonnen lokaliseres.

Col_index_num: Antallet af kolonne, hvorfra den matchede værdi returneres. Det starter med 1 fra kolonnen længst til venstre i tabelarrayet.

Range_lookup: En logisk værdi, der bestemmer, om denne VLOOKUP-funktion returnerer et nøjagtigt match eller et omtrentligt match.

  • Omtrentlig kamp - 1 / SAND: Hvis der ikke findes et nøjagtigt match, søger formlen efter det tætteste match - den største værdi, der er mindre end opslagsværdien. I dette tilfælde skal du sortere opslagskolonnen i stigende rækkefølge.
    = VLOOKUP (lookup_value, table_array, col_index, TRUE)
    = VLOOKUP (lookup_value, table_array, col_index, 1)
  • Præcis kamp - 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.
    = VLOOKUP (lookup_value, table_array, col_index, FALSE)
    = VLOOKUP (lookup_value, table_array, col_index, 0)

Bemærkninger:

  • 1. Vlookup-funktionen ser kun efter værdi fra venstre mod højre.
  • 2. Hvis der er flere matchende værdier baseret på opslagsværdien, returneres kun den første matchede ved hjælp af Vlookup-funktionen.
  • 3. Det returnerer # N / A-fejlværdien, hvis opslagsværdien ikke kan findes.

Grundlæggende VLOOKUP-eksempler

1. Lav et nøjagtigt match Vlookup og omtrentligt match Vlookup

Lav et nøjagtigt match Vlookup i Excel

Normalt, hvis du leder efter et nøjagtigt match med Vlookup-funktionen, skal du bare bruge FALSE i det sidste argument.

For eksempel, for at få de tilsvarende matematikscores baseret på de specifikke ID-numre, skal du gøre som dette:

1. Anvend nedenstående formel i en tom celle, hvor du vil få resultatet:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

2. Træk derefter fyldhåndtaget ned til de celler, du vil udfylde denne formel, og du får resultaterne, som du har brug for. Se skærmbillede:

Bemærkninger:

  • 1. I ovenstående formel F2 er den værdi, som du vil returnere dens matchende værdi, A2: D7 er tabelopstillingen, antallet 3 er det kolonnenummer, som din matchede værdi returneres fra, og FALSK henviser til det nøjagtige match.
  • 2. Hvis din kriterieværdi ikke findes i dataområdet, vises en fejlværdi # N / A.

Lav en omtrentlig match Vlookup i Excel

Det omtrentlige match er nyttigt til søgning efter værdier mellem dataområder. Hvis det nøjagtige match ikke findes, returnerer den omtrentlige Vlookup den største værdi, der er mindre end opslagsværdien.

For eksempel, hvis du har følgende data om området, er de angivne ordrer ikke i kolonnen Ordrer. Hvordan får man den nærmeste rabat i kolonne B?

1. Indtast følgende formel i en celle, hvor du vil placere resultatet:

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

2. Træk derefter fyldhåndtaget ned til cellerne for at anvende denne formel, og du får de omtrentlige matches baseret på de givne værdier, se skærmbillede:

Bemærkninger:

  • 1. I ovenstående formel D2 er den værdi, som du vil returnere dens relative information, A2: B9 er dataområdet, antallet 2 angiver det kolonnenummer, som din matchede værdi returneres, og TRUE henviser til det omtrentlige match.
  • 2. Det omtrentlige match returnerer den største værdi, der er mindre end din specifikke opslagsværdi.
  • 3. For at bruge Vlookup-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 returnerer det et forkert resultat.

2. Lav en sagsfølsom Vlookup i Excel

Som standard udfører Vlookup-funktionen et stort bogstav, som betyder, at det behandler små og store bogstaver som identiske. Engang er du muligvis nødt til at foretage et skift mellem store og små bogstaver i Excel, funktionerne Indeks, match og nøjagtig eller Opslag og nøjagtige funktioner kan gøre dig en tjeneste.

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.

Formel 1: Brug af EXACT, INDEX, MATCH-funktioner

1. Indtast eller kopier nedenstående matrixformel til en tom celle, hvor du vil få resultatet:

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

2. Tryk derefter på Ctrl + Skift + Enter taster samtidigt for at få det første resultat, og vælg derefter formelcellen, træk påfyldningshåndtaget ned til de celler, du vil udfylde denne formel, så får du de korrekte resultater, du har brug for. Se skærmbillede:

Bemærkninger:

  • 1. I ovenstående formel A2: A10 er den kolonne, der indeholder de specifikke værdier, du vil slå op i, F2 er opslagsværdien, C2: C10 er den kolonne, hvor resultatet returneres fra.
  • 2. Hvis der findes flere match, returnerer denne formel altid den første kamp.

Formel 2: Brug af opslag og nøjagtige funktioner

1. Anvend nedenstående formel i en tom celle, hvor du vil få resultatet:

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

2. Træk derefter fyldhåndtaget ned til de celler, som du vil kopiere denne formel, og du får de matchede værdier med store og små bogstaver som vist nedenfor:

Bemærkninger:

  • 1. I ovenstående formel A2: A10 er den kolonne, der indeholder de specifikke værdier, du vil slå op i, F2 er opslagsværdien, C2: C10 er den kolonne, hvor resultatet returneres fra.
  • 2. Hvis der findes flere match, returnerer denne formel altid den sidste kamp.

3. Vlookup-værdier fra højre til venstre i Excel

Vlookup-funktionen ser altid op på 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 lave en omvendt Vlookup, hvilket betyder at slå en bestemt værdi til højre og returnere den tilsvarende værdi i venstre kolonne som vist nedenstående skærmbillede:

Klik for at kende detaljerne trin for trin om denne opgave ...


4. Vlookup den anden, nte eller sidste matchende værdi i Excel

Normalt, hvis der findes flere matchende værdier, når du bruger Vlookup-funktionen, returneres kun den første matchede post. I dette afsnit vil jeg tale om, hvordan man får den anden, niende eller sidste matchende værdi med Vlookup-funktionen.

Vlookup og returner den anden eller nte matchende værdi

Antag at du har en liste over navne i kolonne A, det kursus, de har købt i kolonne B, og nu søger du at finde det 2. eller XNUMX. kursus købt af den givne kunde. Se skærmbillede:

1. For at få den anden eller nte matchende værdi baseret på de givne kriterier skal du anvende følgende matrixformel i en tom celle:

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

2. Tryk derefter 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, du vil udfylde denne formel, og alle de andre matchede værdier baseret på de givne navne er blevet vist på én gang, se skærmbillede:

Bemærk:

  • I denne formel, A2: A14 er området med alle værdier til opslag, B2: B14 er området for de matchende værdier, du vil returnere fra, E2 er opslagsværdien og det sidste tal 2 angiver den anden matchede værdi, du vil få, hvis du vil returnere den tredje matchende værdi, skal du bare ændre den til 3, som du har brug for.

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.


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 nedenfor i skærmbilledet. I dette tilfælde kan du bruge LOOKUP-funktionen og en sorteret tabel.

Vlookup matcher værdier mellem to givne værdier eller datoer med formlen

1. For det første skal din originale tabel være et sorteret datainterval. Og derefter kopiere eller indtaste følgende formel i en tom celle:

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

2. Træk derefter fyldhåndtaget for at udfylde denne formel til andre celler, du har brug for, og nu får du alle matchede poster baseret på den givne værdi, se skærmbillede:

Bemærkninger:

  • 1. I ovenstående formel A2: A6 er området for mindre værdier og B2: B6 er rækkevidden af ​​større tal i dit dataområde, E2 er den givne værdi, som du vil have dens tilsvarende værdi, C2: C6 er de søjledata, som du vil udtrække fra.
  • 2. Denne formel kan også bruges til at udtrække matchede værdier mellem to datoer som vist nedenstående skærmbillede:

Vlookup matcher værdier mellem to givne værdier eller datoer med en nyttig funktion

Hvis du er smertefuld med ovenstående formel, her introducerer jeg et let værktøj - Kutools til Excel, Med LOOKUP mellem to værdier funktion kan du returnere det tilsvarende element baseret på den specifikke værdi eller dato mellem to værdier eller datoer uden at huske nogen formel.   Klik for at downloade Kutools til Excel nu!


6. Brug af jokertegn til delvise matches i Vlookup-funktionen

I Excel kan jokertegnene bruges inden for Vlookup-funktionen, hvilket gør det til at udføre en delvis matchning 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?

1. Den normale Vlookup-funktion fungerer ikke korrekt, du skal sammenføje teksten eller cellereferencen med et jokertegn. Kopier eller indtast følgende formel i en tom celle:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

2. Træk derefter fyldhåndtaget for at udfylde denne formel til andre celler, du har brug for, og alle de matchede scores er returneret som vist nedenstående skærmbillede:

Bemærkninger:

  • 1. I ovenstående formel E2 & ”*” er opslagsværdien, værdien i E2 og * jokertegn ("*" angiver et eller flere tegn), A2: C11 er opslagsområdet, antallet 3 den kolonne, der indeholder den værdi, der skal returneres.
  • 2. Vlookup, når du bruger jokertegn, skal du indstille nøjagtig matchningstilstand med FALSE eller 0 for det sidste argument i Vlookup-funktionen.

tips:

1. Find og returner de matchende værdier, der slutter med en bestemt værdi, brug denne formel: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. For at slå op og returnere den matchede værdi baseret på en del af tekststrengen, uanset om den angivne tekst er foran, bag eller i midten af ​​tekststrengen, skal du bare forbinde to * tegn rundt om cellereferencen eller teksten. Gør venligst med denne formel: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Vlookup-værdier fra et andet regneark

Normalt skal du muligvis arbejde med mere end et regneark, Vlookup-funktionen kan bruges til at slå data fra et andet ark op som det samme 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:

1. Indtast eller kopier nedenstående formel til en tom celle, hvor du vil hente de matchede varer:

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

2. Træk derefter fyldhåndtaget ned til de celler, som du vil anvende denne formel, og 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 er navnet på det regneark, som du vil slå data fra, (Hvis arknavnet indeholder mellemrum eller tegnsætningstegn, skal du vedlægge enkelte citater omkring arknavnet, ellers kan du direkte bruge arknavnet som = VLOOKUP (A2, Dataark! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 er rækkevidden af ​​data i databladet, hvor vi søger data efter;
  • antallet 3 er kolonnenummeret, der indeholder matchede data, du vil returnere fra.

8. Vlookup-værdier fra en anden projektmappe

Dette afsnit vil tale om opslag og returnere de matchende værdier fra en anden projektmappe ved hjælp af Vlookup-funktionen.

For eksempel indeholder den første projektmappe produkt- og omkostningslisterne. Nu vil du udtrække de tilsvarende omkostninger i den anden projektmappe baseret på produktelementet som vist nedenstående skærmbillede.

1. For at hente de relative omkostninger fra en anden projektmappe skal du først åbne begge de projektmapper, du vil bruge, og derefter anvende følgende formel i en celle, hvor du vil placere resultatet:

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

2. Træk derefter og kopier denne formel til andre celler, du har brug for, se skærmbillede:

Bemærkninger:

  • 1. I ovenstående formel:
    B2 repræsenterer opslagsværdien;
    [Produktliste.xlsx] Ark1 er navnet på den projektmappe og det regneark, som du vil slå data fra, (Henvisningen til projektmappen er omsluttet af firkantede parenteser, og hele projektmappen + arket er lukket i enkelt anførselstegn);
    A2: B6 er rækkevidden af ​​data i regnearket i en anden projektmappe, hvor vi søger data efter;
    antallet 2 er kolonnenummeret, der indeholder matchede data, du vil returnere fra.
  • 2. Hvis opslag-projektmappen er lukket, vises den fulde filsti for opslag-projektmappen i formlen som følgende skærmbillede vist:

9. Vlookup og returner tom eller specifik tekst i stedet for 0 eller # N / A-fejlværdi

Normalt, når du anvender vlookup-funktionen for at returnere den tilsvarende værdi, hvis din matchende celle er tom, returnerer den 0, og hvis din matchende værdi ikke findes, får du en fejl # N / A-værdi som vist nedenstående skærmbillede. I stedet for at vise 0 eller # N / A-værdien med tom celle eller en anden værdi, du kan lide, dette Vlookup returnerer tom eller specifik værdi i stedet for 0 eller ikke relevant tutorial kan gøre dig en tjeneste trin for trin.


Avancerede eksempler på VLOOKUP

1. Tovejs opslag med Vlookup-funktion (Vlookup i række og kolonne)

Nogle gange skal du muligvis foretage et 2-dimensionelt opslag, hvilket betyder at Vlookup i både række og kolonne på samme tid. Lad os sige, hvis du har følgende datainterval, og nu skal du muligvis få værdien for et bestemt produkt i et bestemt kvartal. Dette afsnit introducerer en formel til håndtering af dette job i Excel.

Formel 1: Brug af VLOOKUP- og MATCH-funktioner

I Excel kan du bruge en kombination af VLOOKUP- og MATCH-funktioner til at foretage en tovejsopslag, anvend venligst følgende formel i en tom celle, og tryk derefter på Indtast nøgle for at få resultatet.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Bemærk: I ovenstående formel:

  • H1: opslagsværdien i den kolonne, som du vil have den tilsvarende værdi baseret på;
  • A2: E6: dataområdet inklusive rækkeoverskrifter
  • H2: opslagsværdien i rækken, som du vil have den tilsvarende værdi baseret på;
  • A1: E1: cellerne i kolonneoverskrifter.

Formel 2: Brug af INDEX- og MATCH-funktioner

Her er en anden formel, der også kan hjælpe dig med at udføre et 2-dimensionelt opslag. Anvend nedenstående formel, og tryk derefter på Indtast nøgle for at få det ønskede resultat.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Bemærk: I ovenstående formel:

  • B2: E6: dataområdet for at returnere det matchede element fra;
  • H1: opslagsværdien i den kolonne, som du vil have den tilsvarende værdi baseret på;
  • A2: A6: rækkehovederne indeholder det produkt, du vil kigge efter.
  • H2: opslagsværdien i rækken, som du vil have den tilsvarende værdi baseret på;
  • B1: E1: kolonneoverskrifterne indeholder det kvartal, du vil lede efter.

2. Vlookup-matchningsværdi baseret på to eller flere kriterier

Det er let for dig at slå den matchende værdi op på baggrund af et kriterium, men hvis du har to eller flere kriterier, hvad kan du så gøre? Funktionerne LOOKUP eller MATCH og INDEX i Excel kan 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.

Formel 1: Brug af LOOKUP-funktionen

Anvend nedenstående formel i en celle, hvor du vil få resultatet, og tryk derefter på Enter-tasten, se skærmbillede:

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Bemærkninger:

  • 1. I ovenstående formel:
    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åde B2: B12;
    D2: D12: det område, som du vil returnere den tilsvarende værdi.
  • 2. Hvis du har mere end to kriterier, skal du bare slutte de andre kriterier til 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))

Formel 2: Brug af INDEXT- og MATCH-funktioner

Kombinationen af ​​indeks- og matchfunktion kan også bruges til at returnere den matchede værdi baseret på flere kriterier. Kopier eller indtast følgende formel:

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Tryk derefter på Ctrl + Shift + Enter-tasterne sammen for at få den relative værdi, som du har brug for. Se skærmbillede:

Bemærkninger:

  • 1. I ovenstående formel:
    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åde B2: B12;
    D2: D12: det område, som du vil returnere den tilsvarende værdi.
  • 2. Hvis du har mere end to kriterier, skal du bare slutte de nye kriterier til formlen, såsom: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup for at returnere flere matchende værdier med en eller flere betingelser

I Excel søger Vlookup-funktionen efter en værdi og returnerer kun den første matchende værdi, hvis der findes flere tilsvarende værdier. Nogle gange vil du muligvis returnere alle de tilsvarende værdier i en række, i en kolonne eller i en enkelt celle. Dette afsnit vil tale om, hvordan du returnerer de flere matchende værdier med en eller flere betingelser i en projektmappe.

Vlookup alle matchende værdier baseret på en eller flere betingelser vandret

Vlookup alle matchende værdier baseret på en betingelse vandret:

For at Vlookup og returnere alle matchende værdier baseret på en bestemt værdi vandret, er den generiske formel:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
Bemærk: m er række nummer for den første celle i returområdet minus 1.
      n er kolonnetallet for den første formelcelle minus 1.

1. Anvend nedenstående formel i en tom celle, og tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få den første matchede værdi, se skærmbillede:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Og vælg derefter den første formelcelle, og træk udfyldningshåndtaget til højre celler, indtil den tomme celle vises, og alle tilsvarende elementer er blevet ekstraheret, se skærmbillede:

tips:

Hvis der er duplikerede matchede værdier på den returnerede liste, skal du bruge disse formler for at ignorere duplikaterne og derefter trykke på Indtast for at få det første resultat: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Fortsæt med at indtaste denne formel: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") ind i en celle ved siden af ​​det første resultat, og tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få det andet resultat, og træk derefter denne formel til højre celler for at få alle andre matchede værdier, indtil den tomme celle vises, se skærmbillede:


Vlookup alle matchende værdier baseret på to eller flere betingelser vandret:

For at Vlookup og returnere alle matchende værdier baseret på mere specifikke værdier vandret, er den generiske formel:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
Bemærk: m er række nummer for den første celle i returområdet minus 1.
      n er kolonnetallet for den første formelcelle minus 1.

1. Anvend følgende formel i en tom celle, hvor du vil sende resultatet:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Vælg derefter formelcellen og træk udfyldningshåndtaget til højre celler, indtil den tomme celle vises, og alle matchede værdier baseret på de specifikke kriterier er returneret, se skærmbillede:

Bemærk: For flere kriterier skal du bare deltage i lookup_value og lookup_range i formlen, såsom: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Vlookup alle matchende værdier baseret på en eller flere betingelser lodret

Vlookup alle matchende værdier baseret på en betingelse lodret:

For at Vlookup og returnere alle matchende værdier baseret på en bestemt værdi lodret, er den generiske formel:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
Bemærk: m er række nummer for den første celle i returområdet minus 1.
      n er række nummer for den første formelcelle minus 1.

1. Kopier eller skriv følgende formel i en celle, hvor du vil få resultatet, og tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få den første matchede værdi, se skærmbillede:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Vælg derefter den første formelcelle, og træk udfyldningshåndtaget ned til andre celler, indtil den tomme celle vises, og alle tilsvarende elementer er angivet i en kolonne, se skærmbillede:

tips:

Hvis du vil ignorere duplikaterne i de returnerede matchende værdier, skal du bruge disse formler: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få den første matchede værdi, og træk derefter denne formelcelle ned til andre celler, indtil den tomme celle vises, og du får resultatet efter behov:


Vlookup alle matchende værdier baseret på to eller flere betingelser lodret:

For at Vlookup og returnere alle matchende værdier baseret på mere specifikke værdier lodret, er den generiske formel:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
Bemærk: m er række nummer for den første celle i returområdet minus 1.
      n er række nummer for den første formelcelle minus 1.

1. Kopier nedenstående formel til en tom celle, og tryk derefter på Ctrl + Skift + Enter tasterne sammen for at få det første matchede element.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Træk derefter formelcellen ned til andre celler, indtil den tomme celle vises, se skærmbillede:

Bemærk: For flere kriterier skal du bare deltage i lookup_value og lookup_range i formlen, såsom: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Vlookup alle matchende værdier baseret på to eller flere betingelser i en enkelt celle

Hvis du vil Vlookup og returnere flere matchede værdier i en enkelt celle med specificeret separator, kan den nye funktion af TEXTJOIN hjælpe dig med at løse dette job hurtigt og nemt.

Vlookup alle matchende værdier baseret på en betingelse i en enkelt celle:

Anvend nedenstående enkle formel i en tom celle, og tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få resultatet:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

tips:

Hvis du vil ignorere duplikaterne i de returnerede matchende værdier, skal du bruge disse formler: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Vlookup alle matchende værdier baseret på to eller flere betingelser i en enkelt celle:

For at håndtere flere betingelser, når du returnerer alle matchende værdier til en enkelt celle, skal du anvende nedenstående formel og derefter trykke på Ctrl + Skift + Enter nøgler sammen for at få resultatet:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Bemærkninger:

1. TEXTJOIN-funktionen er kun tilgængelig i Excel 2019 og Office 365.

2. Hvis du bruger Excel 2016 og tidligere versioner, skal du bruge den brugerdefinerede funktion i nedenstående artikler:


4. Vlookup for at returnere hele eller 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 hjælp af Vlookup-funktionen.

1. Kopier eller skriv nedenstående formel i en tom celle, hvor du vil sende resultatet, og tryk på Indtast tast for at få den første værdi.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

2. Træk derefter formelcellen til højre, indtil dataene for hele rækken vises, 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 bruge, A1 angiver det første kolonnetal inden for dit dataområde.

tips:

Hvis der findes flere rækker baseret på den matchede værdi, skal du anvende denne formel for at returnere alle de tilsvarende rækker: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), og tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få det første resultat, og træk derefter fyldhåndtaget lige til cellerne, se skærmbillede:

Og træk derefter fyldhåndtaget ned over cellerne for at få alle matchende rækker som vist nedenstående skærmbillede:


5. Udfør flere Vlookup-funktioner (indlejret Vlookup) i Excel

Nogle gange vil du måske slå op på værdier i flere tabeller, hvis nogen af ​​tabellerne indeholder den givne opslagsværdi som vist nedenfor, kan du i dette tilfælde kombinere en eller flere Vlookup-funktioner sammen med IFERROR-funktionen for at udføre flere opslag.

Den generiske formel for indlejret Vlookup-funktion er:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Bemærk:

  • opslagsværdi: den værdi, du leder efter;
  • Table1, Table2, Table3, ...: tabellerne, hvor opslagsværdien og returværdien findes;
  • col: kolonnenummeret i tabellen, hvorfra du vil returnere den matchende værdi.
  • 0: Dette bruges til en nøjagtig matchning.

1. Anvend følgende formel i en tom celle, hvor du vil placere resultatet:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Træk derefter fyldhåndtaget ned til de celler, som du vil anvende denne formel, og alle matchede værdier er returneret som vist nedenstående skærmbillede:

Bemærkninger:

  • 1. I ovenstående formel J3 er den værdi, du leder efter; A3: B7, D3: E7, G3: H7 er de tabelområder, hvor opslagsværdien og returværdien findes; Nummeret 2 er kolonnenummeret i det område, som matchende værdi returneres fra.
  • 2. Hvis opslagsværdien ikke kan findes, vises en fejlværdi for at erstatte fejlen med en læsbar tekst, brug denne formel: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup for at kontrollere, om der findes værdi baseret på en liste data i en anden kolonne

Vlookup-funktionen kan også hjælpe dig med at kontrollere, om der findes værdier baseret på en anden liste, for eksempel hvis du vil kigge efter navnene i kolonne C og bare returnere Ja eller Nej, hvis navnet findes eller ikke i kolonne A som nedenstående skærmbillede vist.

1. Anvend følgende formel i en tom celle:

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

2. Træk derefter fyldhåndtaget ned til de celler, som du vil udfylde denne formel, og du får resultatet efter behov, se skærmbillede:

Bemærk: I ovenstående formel C2 er den opslagsværdi, du vil kontrollere; A2: A10 er listen over intervaller, hvorfra opslagsværdier vil blive fundet; nummeret 1 er det kolonnenummer, hvorfra du vil hente værdi i dit interval.


7. Vlookup og summer alle matchede værdier i rækker eller kolonner

Hvis du arbejder med numeriske data, skal du nogle gange, når du udtrækker de matchede værdier fra tabellen, muligvis også summe tallene i flere kolonner eller rækker. Dette afsnit introducerer nogle formler til at afslutte dette job i Excel.

Vlookup og summer 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 nedenstående skærmbillede, nu skal du sammenfatte alle ordrer i alle måneder baseret på de givne produkter.

Vlookup og summer de første matchede værdier i træk:

1. Kopier eller indtast følgende formel i en tom celle, og tryk derefter på Ctrl + Skift + Enter tasterne sammen for at få det første resultat.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

2. Træk derefter fyldhåndtaget ned for at kopiere denne formel til andre celler, du har brug for, og 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), der inkluderer opslagsværdien og de matchede værdier; Nummeret 2,3,4,5,6 {} er kolonnetal, der bruges til at beregne det samlede interval.


Vlookup og summer alle matchede værdier i flere rækker:

Ovenstående formel kan kun summere værdier i en række for den første matchede værdi. Hvis du vil sammenfatte alle matches i flere rækker, skal du bruge følgende formel og trække derefter fyldhåndtaget ned til de celler, du vil anvende denne formel, og du får det ønskede resultat, du har brug for, se skærmbillede:

=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)

Bemærk: I ovenstående formel: H2 er den opslagsværdi, du leder efter; A2: A9 er rækkeoverskrifter, der indeholder opslagsværdien; B2: F9 dataområdet for de numeriske værdier, som du vil sammenfatte.


Vlookup og summer alle matchede værdier i en kolonne eller flere kolonner

Vlookup og sum de første matchede værdier i en kolonne:

Hvis du vil sammenfatte den samlede værdi for de specifikke måneder som vist på skærmbilledet nedenfor.

Anvend nedenstående formel i en tom celle, og træk derefter fyldhåndtaget ned for at kopiere denne formel til andre celler, nu er de første matchede værdier baseret på den specifikke måned i en kolonne sammenfattet, se skærmbillede:

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Bemærk: I ovenstående formel: H2 er den opslagsværdi, du leder efter; B1: F1 er kolonneoverskrifterne, der indeholder opslagsværdien; B2: F9 dataområdet for de numeriske værdier, som du vil sammenfatte.


Vlookup og summer alle matchede værdier i flere kolonner:

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))

Bemærk: I ovenstående formel: H2 er den opslagsværdi, du leder efter; B1: F1 er kolonneoverskrifterne, der indeholder opslagsværdien; B2: F9 dataområdet for de numeriske værdier, som du vil sammenfatte.


Vlookup og sum de første matchede eller alle matchede værdier med en kraftig funktion

Måske er ovenstående formler vanskelige for dig at huske, i dette tilfælde vil jeg anbefale en praktisk funktion - Opslag og sum of Kutools til Excel, med denne funktion kan du få resultatet så let som muligt.    Klik for at downloade Kutools til Excel nu!


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.

Anvend følgende formel i en celle, og tryk derefter på Enter-tasten 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 for de numeriske værdier, som du vil sammenfatte; B1: F1 is kolonneoverskrifterne indeholder den opslagsværdi, som du vil opsummere baseret på; I2 er opslagsværdien inden for de kolonneoverskrifter, du leder efter; A2: A9 er rækkehovederne indeholder den opslagsværdi, som du vil opsummere baseret på; H2 er opslagsværdien inden for de rækkehoveder, du leder efter.


8. Vlookup for at flette to tabeller baseret på en eller flere nøglekolonner

I dit daglige arbejde, når du analyserer dataene, skal du muligvis samle alle de nødvendige oplysninger i en enkelt tabel baseret på en eller flere nøglekolonner. For at løse dette job kan Vlookup-funktionen også gøre dig en tjeneste.

Vlookup for at flette to tabeller baseret på en nøglekolonne

For eksempel har du to tabeller, den første tabel indeholder data om produkter og navne, og den anden tabel indeholder produkter og ordrer. Nu vil du kombinere disse to tabeller ved at matche den fælles produktkolonne til en tabel.

Formel 1: Brug af VLOOKUP-funktionen

For at flette de to tabeller til en baseret på en nøglekolonne, skal du anvende følgende formel i en tom celle, hvor du vil få resultatet, og træk derefter fyldhåndtaget ned til de celler, du vil anvende denne formel, du vil få en flettet tabel med ordrekolonnen, der sammenføjes med de første tabeldata baseret på nøglekolonnedataene.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

Bemærk: I ovenstående formel A2 er den værdi, du leder efter, E2: F8 er tabellen, der skal søges, nummeret 2 er kolonnenummeret i tabellen, hvorfra værdien kan hentes.

Formel 2: Brug af INDEX- og MATCH-funktioner

Hvis dine almindelige data i højre side og de returnerede data i venstre kolonne i den anden tabel, for at flette ordrekolonnen, kan Vlookup-funktionen ikke udføre jobbet. For at se op fra højre til venstre kan du bruge INDEX- og MATCH-funktionerne til at erstatte Vlookup-funktionen.

Kopier eller indtast nedenstående formel i en tom celle, kopier derefter formlen ned i kolonnen, og ordrekolonnen er blevet knyttet til den første tabel, se skærmbillede:

=INDEX($E$2:$E$8, MATCH($A2, $F$2:$F$8, 0))

Bemærk: I ovenstående formel A2 er den opslagsværdi, du leder efter, E2: E8 er række af data, som du vil returnere, F2: F8 er opslagsområde, der indeholder opslagsværdien.


Vlookup for at flette to tabeller baseret på flere nøglekolonner

Hvis de to tabeller, du vil deltage i, har flere nøglekolonner, hvis du vil flette tabellerne baseret på disse almindelige kolonner, kan INDEX- og MATCH-funktionerne hjælpe dig.

Den generiske formel til fletning af to tabeller baseret på flere nøglekolonner er:

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

1. Anvend nedenstående formel i en tom celle, hvor du vil placere resultatet, og tryk derefter på 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)

Bemærk: I ovenstående formel, hvad cellereferencer repræsenterer som vist nedenstående skærmbillede:

2Vælg derefter den første formelcelle, og træk fyldhåndtaget for at kopiere denne formel til andre celler, som du har brug for:

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

9. Vlookup matchende værdier på tværs af flere regneark

Har du nogensinde prøvet at Vlookup-værdier på tværs af flere regneark? Antag, at jeg har følgende tre regneark med dataudvalg, og nu vil jeg få en del af de tilsvarende værdier baseret på kriterierne fra disse tre regneark for at få resultatet som vist nedenstående skærmbillede. I dette tilfælde er Vlookup-værdier på tværs af flere regneark tutorial kan gøre dig en tjeneste trin for trin.


VLOOKUP-matchede værdier beholder celleformatering

1. Vlookup for at få celleformatering (cellefarve, fontfarve) sammen med opslagsværdi

Som vi alle ved, kan den normale Vlookup-funktion kun hjælpe os med at returnere den matchede værdi fra et andet dataområde, men nogle gange vil du muligvis returnere den tilsvarende værdi sammen med celleformateringen, såsom fyldfarve, skriftfarve, skrifttypestil som vist nedenstående skærmbillede. Dette afsnit vil tale om, hvordan man får celleformatering med den returnerede værdi i Excel.

Gør med følgende trin for at slå op og returnere den tilsvarende værdi sammen med celleformatering:

1. I regnearket indeholder de data, du vil have til Vlookup, skal du højreklikke på arkfanen og vælge 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.

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

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

4. Når du har indsat ovenstående koder, skal du klikke på Værktøj > Referencer i Microsoft Visual Basic til applikationer vindue. Kontroller derefter Microsoft Script Runtime afkrydsningsfeltet i Referencer - VBAProject dialog boks. Se skærmbilleder:

5. Klik derefter på OK for at lukke dialogboksen og derefter gemme og lukke kodevinduet, gå nu regnearket tilbage og anvend derefter denne formel: =LookupKeepFormat(E2,$A$1:$C$10,3) ind i en tom celle, hvor du vil sende resultatet, og tryk derefter på Enter-tasten. Se skærmbillede:

Bemærk: I ovenstående formel E2 er den værdi, du vil slå op, A1: C10 er tabelområdet og antallet 3 er kolonnenummeret på den tabel, du vil have den matchede værdi returneret.

6. Vælg derefter den første resultatcelle, og træk udfyldningshåndtaget ned for at få alle resultater sammen med deres formatering. Se skærmbillede.


2. Hold datoformatet fra en Vlookup-returneret værdi

Normalt, når du bruger Vloook-funktionen til at slå op og returnere den matchede datoformatværdi, vises et talformat som vist nedenstående skærmbillede. For at holde datoformatet fra et returneret resultat, skal du vedlægge TEKST-funktionen til Vlookup-funktionen.

Anvend nedenstående formel i en tom celle, og træk derefter fyldhåndtaget for at kopiere denne formel til andre celler, og alle de matchede datoer er returneret som vist nedenstående skærmbillede:

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Bemærk: I ovenstående formel E2 er udseendet, A2: C9 er opslagsområdet, antallet 3 er det kolonnenummer, du ønsker, at værdien skal returneres, mm / dd / åååå er det datoformat, du vil beholde.


3. Vlookup og returner matchende værdi med cellekommentar

Har du nogensinde prøvet at Vlookup for ikke kun at returnere de matchede celledata, men også cellekommentaren i Excel som det følgende skærmbillede vist? For at løse denne opgave kan nedenstående brugerdefinerede funktion gøre dig en tjeneste.

1. Hold nede 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, indtast denne formel: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) ind i en tom celle for at finde resultatet, og træk derefter fyldhåndtaget for at kopiere denne formel til andre celler, nu returneres de matchede værdier såvel som kommentarerne med det samme, se skærmbillede:

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, nummeret 2 er kolonnenummeret, der indeholder den matchede værdi, du vil returnere.


4. Håndter teksten og de reelle tal i Vlookup

For eksempel har jeg en række data, ID-nummeret i den originale tabel er nummerformat, i opslagscellen, der er gemt som tekst, når der anvendes den normale Vlookup-funktion, vises et # N / A-fejlresultat som nedenstående skærmbillede vist. I dette tilfælde, hvordan kan du få de korrekte oplysninger, hvis opslagsnummeret og det oprindelige nummer i tabellen har det forskellige dataformat?

For at håndtere teksten og reelle tal i Vlookup-funktionen skal du anvende følgende formel i en tom celle, og træk derefter påfyldningshåndtaget ned for at kopiere denne formel, og du får de korrekte resultater som vist nedenstående skærmbillede:

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Bemærkninger:

  • 1. I ovenstående formel D2 er den opslagsværdi, du vil returnere dens tilsvarende værdi, A2: B8 er den datatabel, du vil bruge, nummeret 2 er kolonnenummeret, der indeholder den matchede værdi, du vil returnere.
  • 2. Denne formel fungerer også godt, hvis du ikke er sikker på, hvor du har tal, og hvor du har tekst.

Download VLOOKUP-eksempelfiler

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Super Formula Bar (let redigere flere linjer med tekst og formel); Læsning Layout (let at læse og redigere et stort antal celler); Indsæt til filtreret rækkevidde...
  • Flet celler / rækker / kolonner og opbevaring af data; Split celler indhold; Kombiner duplikatrækker og sum / gennemsnit... Forhindre duplikerede celler; Sammenlign områder...
  • Vælg Duplicate eller Unique Rækker; Vælg tomme rækker (alle celler er tomme); Super Find og Fuzzy Find i mange arbejdsbøger; Tilfældig valg ...
  • Præcis kopi Flere celler uden at ændre formelreference; Auto Opret referencer til flere ark; Indsæt kugler, Afkrydsningsfelter og mere ...
  • Foretrukne og hurtigt indsætte formler, Områder, diagrammer og billeder; Krypter celler med adgangskode Opret postliste og send e-mails ...
  • Uddrag tekst, Tilføj tekst, Fjern efter position, Fjern mellemrum; Opret og udskriv personsøgningssubtotaler; Konverter mellem celler indhold og kommentarer...
  • Superfilter (gem og anvend filterskemaer på andre ark); Avanceret sortering efter måned / uge / dag, hyppighed og mere; Specielt filter af fed, kursiv ...
  • Kombiner arbejdsbøger og arbejdsark; Fletabeller baseret på nøglekolonner; Opdel data i flere ark; Batch Konverter xls, xlsx og PDF...
  • Pivottabelgruppering efter ugenummer, ugedag og mere ... Vis ulåste, låste celler ved forskellige farver; Fremhæv celler, der har formel / navn...
kte-fane 201905
  • 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!
officetab bund
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.