Hoppa till huvudinnehåll

20+ VLOOKUP-exempel för Excel-nybörjare och avancerade användare

VLOOKUP-funktionen är en av de mest populära funktionerna i Excel. Denna handledning introducerar hur du använder VLOOKUP-funktionen i Excel med dussintals grundläggande och avancerade exempel steg för steg.


Introduktion av VLOOKUP-funktionen - syntax och argument

I Excel är VLOOKUP-funktionen en kraftfull funktion för de flesta Excel-användare, den låter dig leta efter ett värde längst till vänster i dataintervallet och returnera ett matchande värde i samma rad från en kolumn som du angav som följande skärmbild .

Syntaxen för VLOOKUP-funktionen:

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

argument:

Uppslagningsvärde (obligatoriskt): Värdet som du vill söka efter. Det kan vara ett värde (nummer, datum eller text) eller cellreferens. Det måste finnas i den första kolumnen i intervallet table_array. 

Tabell_array (obligatoriskt): Dataintervallet eller tabellen där kolumnen för uppslagsvärde och kolumnen för resultatvärde finns.

Col_index_num (obligatoriskt): Kolumnnumret som innehåller returvärdena. Det börjar med 1 från kolumnen längst till vänster i tabellmatrisen.

Range_lookup (valfritt): Ett logiskt värde som avgör om den här VLOOKUP-funktionen kommer att returnera en exakt matchning eller en ungefärlig matchning.

  • Ungefärlig matchning – 1 / SANT / utelämnad (standard): Om en exakt matchning inte hittas, söker formeln efter den närmaste matchningen - det största värdet som är mindre än uppslagsvärdet.
    Lägga märke till: I det här fallet måste du sortera uppslagskolumnen (kolumnen längst till vänster i dataintervallet) i stigande ordning, annars kommer den att returnera ett felaktigt eller ett #N/A-felresultat.
  • Exakt matchning – 0 / FALSK: Detta används för att söka efter ett värde exakt lika med uppslagsvärdet. Om en exakt matchning inte hittas returneras felvärdet # N / A.

Funktionsanmärkningar:

  • Vlookup-funktionen letar bara efter ett värde från vänster till höger.
  • Funktionen Vlookup utför en skiftlägeskänslig uppslagning.
  • Om det finns flera matchande värden baserat på uppslagsvärdet, kommer endast det första matchade att returneras genom att använda Vlookup-funktionen.

Grundläggande VLOOKUP-exempel

I det här avsnittet kommer vi att prata om några Vlookup-formler som du använt ofta.

2.1 Exakt matchning och ungefärlig match VLOOKUP

 2.1.1 Gör en exakt matchning VLOOKUP

Normalt, om du letar efter en exakt matchning med VLOOKUP-funktionen behöver du bara använda FALSE som sista argument.

För att till exempel få motsvarande matematiska poäng baserat på specifika ID-nummer, gör så här:

Kopiera och klistra in formeln nedan i en tom cell (här väljer jag G2) och tryck ange nyckel för att få resultatet:

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

Obs: I formeln ovan finns det fyra argument:

  • F2 är cellen som innehåller värdet C1005 som du vill slå upp;
  • A2: D7 är den tabelluppsättning där du utför sökningen;
  • 3 är kolumnnumret som ditt matchade värde returneras från; (När funktionen upptäcker ID - C1005, kommer den att gå till den tredje kolumnen i tabellmatrisen och returnera värdena i samma rad som ID - C1005. )
  • FALSK hänvisar till den exakta matchningen.

Hur fungerar formeln VLOOKUP?

Först letar den efter ID - C1005 i kolumnen längst till vänster i tabellen. Den går uppifrån och ner och hittar värdet i cell A6.

Så snart den hittar värdet går den till höger i den tredje kolumnen och extraherar värdet i den.

Så du kommer att få resultatet enligt nedanstående skärmdump:

Notera: Om uppslagsvärdet inte hittas i kolumnen längst till vänster returnerar det ett #N/A-fel.
🤖 Kutools AI Aide: Revolutionera dataanalys baserat på: Intelligent utförande   |  Generera kod  |  Skapa anpassade formler  |  Analysera data och generera diagram  |  Anropa Kutools funktioner.
Populära funktioner: Hitta, markera eller identifiera dubbletter   |  Ta bort tomma rader   |  Kombinera kolumner eller celler utan att förlora data   |   Rund utan formel ...
Superuppslag: Flera kriterier VLookup  |   VLookup med flera värden  |   VSök över flera ark   |   Fuzzy Lookup ...
Avancerad rullgardinslista: Skapa snabbt en rullgardinslista   |  Beroende rullgardinslista   |  Flervals-rullgardinslista ...
Kolumnhanterare: Lägg till specifikt antal kolumner  |  Flytta kolumner   |  Visa kolumner  |  Jämför intervall och kolumner ...
Utvalda funktioner: Rutnätsfokus   |  Designvy   |   Stor formelbar   |  Arbetsbok & Bladhanterare  |  Resursbibliotek   |  Datumväljare  |  Kombinera arbetsblad   |  Kryptera/Dekryptera celler    Skicka e-postmeddelanden efter lista   |  Superfilter   |   Specialfilter (med fetstil/kursiv...) ...
Topp 15 verktygssats12 text verktyg (lägga till text, Ta bort tecken, ...)   |   50+ Diagram Typer (Gantt Chart, ...)   |   40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag, ...)   |   19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg, ...)   |   12 Konvertering verktyg (Siffror till ord, Valutaväxling, ...)   |   7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela celler, ...)   |   Många fler...

Kutools för Excel har över 300 funktioner, Se till att det du behöver bara är ett klick bort...

 
 2.1.2 Gör en ungefärlig matchning VLOOKUP

Den ungefärliga matchningen är användbar för att söka värden mellan dataintervall. Om den exakta matchningen inte hittas kommer den ungefärliga VLOOKUP att returnera det största värdet som är mindre än uppslagsvärdet.

Till exempel, om du har följande dataintervall och de angivna orderna inte finns i kolumnen Orders, hur får du närmaste rabatt i kolumn B?

Steg 1: Använd formeln VLOOKUP och fyll den i andra celler

Kopiera och klistra in följande formel i en cell där du vill placera resultatet och dra sedan fyllningshandtaget nedåt för att tillämpa denna formel på andra celler.

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

Resultat:

Nu kommer du att få de ungefärliga matchningarna baserat på de givna värdena, se skärmdump:

Anmärkningar:

  • I ovanstående formel:
    • D2 är värdet som du vill returnera dess relativa information;
    • A2: B9 är dataintervallet;
    • 2 anger kolumnnumret som ditt matchade värde returneras;
    • SANN hänvisar till den ungefärliga matchningen.
  • Den ungefärliga matchningen returnerar det största värdet som är mindre än ditt specifika uppslagsvärde om ingen exakt matchning hittas.
  • För att använda UPPSÖKNINGSfunktionen för att få ett ungefärligt matchningsvärde måste du sortera kolumnen längst till vänster i dataintervallet i stigande ordning, annars kommer det att returnera ett felaktigt resultat.

2.2 Gör en skiftlägeskänslig VLOOKUP i Excel

Som standard utför VLOOKUP-funktionen en skiftlägesokänslig uppslagning, vilket innebär att den behandlar gemener och versaler som identiska. Ibland kan du behöva göra en skiftlägeskänslig uppslagning i Excel, den normala VLOOKUP-funktionen kanske inte löser det. I det här fallet kan du använda alternativa funktioner som INDEX och MATCH med EXAKT-funktionen eller funktionerna LOOKUP och EXACT.

Till exempel har jag följande dataintervall vilken ID-kolumn innehåller textsträng med versaler eller gemener, nu vill jag returnera motsvarande Math-poäng för det angivna ID-numret.

Steg 1: Använd valfri formel och fyll den i andra celler

Vänligen kopiera och klistra in någon av formlerna nedan i en tom cell där du vill få resultatet. Välj sedan formelcellen, dra fyllningshandtaget ner till cellerna där du vill fylla den här formeln.

Formel 1: När du har klistrat in formeln trycker du på Ctrl + Skift + Enter nycklar.

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

Formel 2: När du har klistrat in formeln trycker du på ange nyckel.

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

Resultat:

Då får du rätt resultat du behöver. Se skärmdump:

Anmärkningar:

  • I ovanstående formel:
    • A2: A10 är kolumnen som innehåller de specifika värden du vill slå upp i;
    • F2 är uppslagsvärdet;
    • C2: C10 är den kolumn där resultatet kommer att returneras.
  • Om flera matchningar hittas kommer denna formel alltid att returnera den senaste matchningen.

2.3 VLOOKUP-värden från höger till vänster i Excel

Funktionen VLOOKUP söker alltid efter ett värde i kolumnen längst till vänster i ett dataintervall och returnerar motsvarande värde från en kolumn till höger. Om du vill utföra en omvänd VLOOKUP vilket innebär att slå upp ett specifikt värde i den högra kolumnen och returnera dess motsvarande värde i den vänstra kolumnen enligt nedanstående skärmbild:

Klicka för att veta detaljerna steg för steg om den här uppgiften ...


2.4 VLOOKUP det andra, n:e eller sista matchande värdet i Excel

Normalt, om flera matchande värden hittas när Vlookup-funktionen används, kommer endast den första matchade posten att returneras. I det här avsnittet kommer jag att prata om hur man får det andra, n:te eller sista matchningsvärdet i ett dataintervall.

 2.4.1 VLOOKUP och returnera det andra eller n:e matchningsvärdet

Anta att du har en lista med namn i kolumn A, utbildningskursen de köpte i kolumn B. Nu letar du efter den andra eller n:e utbildningen som köpts av den givna kunden. Se skärmdump:

Här kanske funktionen VLOOKUP inte löser denna uppgift direkt. Men du kan använda INDEX-funktionen som ett alternativ.

Steg 1: Använd och fyll formeln på andra celler

Till exempel, för att få det andra matchande värdet baserat på de givna kriterierna, använd följande formel i en tom cell och tryck på Ctrl + Skift + Enter nycklarna tillsammans för att få det första resultatet. Och välj sedan formelcellen, dra fyllningshandtaget ner till cellerna där du vill fylla den här formeln.

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

Resultat:

Nu har alla andra matchade värden baserade på de givna namnen visats på en gång.

Notera: I ovanstående formel:

  • A2: A14 är intervallet med alla värden för uppslagning;
  • B2: B14 är intervallet för matchande värden du vill återvända från;
  • E2 är uppslagsvärdet;
  • 2 indikerar det andra matchade värdet du vill få, för att returnera det tredje matchande värdet behöver du bara ändra det till 3.
 2.4.2 VLOOKUP och returnera det sista matchande värdet

Om du vill söka efter och returnera det senaste matchande värdet enligt nedanstående skärmdump, detta VLOOKUP och returnera det senaste matchande värdet handledning kan hjälpa dig att få det sista matchande värdet i detaljer.


2.5 VLOOKUP matchande värden mellan två givna värden eller datum

Ibland kanske du vill slå upp värden mellan två värden eller datum och returnera motsvarande resultat som visas i skärmdumpen nedan. I sådana fall kan du använda UPPSÖKNING-funktionen istället för SLÖKUP-funktionen med en sorterad tabell.

 2.5.1 VLOOKUP matchande värden mellan två givna värden eller datum med formel

Steg 1: Ordna data och tillämpa följande formel

Din ursprungliga tabell bör vara ett sorterat dataintervall. Och kopiera eller skriv in följande formel i en tom cell. Dra sedan i fyllningshandtaget för att fylla formeln till andra celler du behöver.

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

Resultat:

Och nu kommer du att få alla matchade poster baserat på det givna värdet, se skärmdump:

Anmärkningar:

  • I ovanstående formel:
    • A2: A6 är intervallet för mindre värden;
    • B2: B6 är intervallet för större tal;
    • E2 är det uppslagsvärde som du vill ska få motsvarande värde;
    • C2: C6 är den kolumn från vilken du vill returnera ett motsvarande värde.
  • Den här formeln kan också användas för att extrahera matchade värden mellan två datum enligt skärmbilden nedan:
 2.5.2 VLOOKUP matchar värden mellan två givna värden eller datum med en praktisk funktion

Om du tycker att det är svårt att komma ihåg och förstå formeln ovan, här kommer jag att presentera ett enkelt verktyg – Kutools för Excel, med dess LOOKUP mellan två värden funktionen kan du returnera motsvarande artikel baserat på det specifika värdet eller datumet mellan två värden eller datum med lätthet.

  1. Klicka Kutools > Superuppslagning > LOOKUP mellan två värden för att aktivera den här funktionen.
  2. Ange sedan operationerna från dialogrutan baserat på dina data.
Anmärkningar: För att använda den här funktionen bör du ladda ner Kutools för Excel med 30 dagars gratis provperiod för det första.


2.6 Använda jokertecken för partiella matchningar i VLOOKUP-funktionen

I Excel kan jokertecken användas inom VLOOKUP-funktionen, som låter dig utföra en partiell matchning av ett uppslagsvärde. Till exempel kan du använda VLOOKUP för att returnera matchat värde från en tabell baserat på en del av ett uppslagsvärde.

Om jag antar att jag har en rad data som nedan visas skärmdump, nu vill jag extrahera poängen baserat på förnamnet (inte fullständigt namn). Hur kan man lösa denna uppgift i Excel?

Steg 1: Använd och fyll formeln på andra celler

Kopiera eller skriv in följande formel i en tom cell och dra sedan i fyllningshandtaget för att fylla formeln till andra celler du behöver:

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

Resultat:

Och alla matchade poäng har returnerats enligt nedanstående skärmdump:

Notera: I ovanstående formel:

  • E2 & ”*” är kriterierna för den partiella matematiken. Det betyder att du letar efter alla värden som börjar med värdet i cell E2. (Jokertecken "*” indikerar ett tecken eller alla tecken)
  • A2: C11 är det dataintervall där du vill söka efter det matchade värdet;
  • 3 medel för att returnera det matchande värdet från den tredje kolumnen i dataintervallet;
  • Falsk indikerar den exakta matematiken. (När du använder jokertecken måste du ställa in det sista argumentet i funktionen som FALSK eller 0 för att aktivera exakt matchningsläge i UPPLÖKNINGSfunktionen.)
tips:
  • För att hitta och returnera matchande värden som slutar med ett specifikt värde, bör du sätta jokertecknet "*" framför värdet. Vänligen använd denna formel:
  • =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

  • För att slå upp och returnera det matchade värdet baserat på en del av textsträngen, oavsett om den angivna texten är i bebinning, slutet eller i mitten av textsträngen, behöver du bara omge cellreferensen eller texten med två asterisker (*) på båda sidor. Vänligen gör med denna formel
  • =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


2.7 VLOOKUP-värden från ett annat kalkylblad

Vanligtvis kan du behöva arbeta med mer än ett kalkylblad, funktionen VLOOKUP kan användas för att slå upp data från ett annat ark på samma sätt som i ett kalkylblad.

Till exempel har du två kalkylblad som visas nedan i skärmdumpen, för att leta upp och returnera motsvarande data från kalkylbladet du angav, gör med följande steg:

Steg 1: Använd och fyll formeln på andra celler

Ange eller kopiera formeln nedan till en tom cell där du vill få de matchade objekten. Dra sedan fyllningshandtaget ner till de celler som du vill använda den här formeln.

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

Resultat:

Du kommer att få motsvarande resultat som du behöver, se skärmdump:

Notera: I ovanstående formel:

  • A2 representerar uppslagsvärdet;
  • 'Datablad'!A2:C15 indikerar att söka efter värden från intervallet A2:C15 på kalkylbladet med namnet Datablad; (Om arknamnet innehåller mellanslag eller skiljetecken, bör du omge arknamnet med enkla citattecken, annars kan du direkt använda arknamnet som =SÖKUP(A2,Datablad!$A$2:$C$15,3,0) ).
  • 3 är kolumnnumret som innehåller matchade data som du vill returnera från;
  • 0 innebär att utföra en exakt matchning.

2.8 VLOOKUP-värden från en annan arbetsbok

Det här avsnittet kommer att tala om uppslagning och returnera de matchande värdena från en annan arbetsbok genom att använda funktionen VLOOKUP.

Låt oss till exempel säga att du har två arbetsböcker. Den första arbetsboken innehåller en lista över produkter och deras respektive kostnader. I den andra arbetsboken vill du extrahera motsvarande kostnad för varje produktartikel enligt skärmbilden nedan.

Steg 1: Applicera och fyll i formeln

Öppna båda arbetsböckerna du vill använda och använd sedan följande formel i en cell där du vill lägga resultatet i den andra arbetsboken. Dra sedan och kopiera den här formeln till andra celler du behöver

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

Resultat:

Anmärkningar:

  • I ovanstående formel:
    • B2 representerar uppslagsvärdet;
    • '[Produktlista.xlsx]Sheet1'!A2:B6 indikerar att söka från intervallet A2:B6 på bladet som heter Sheet1 från arbetsbokens Produktlista; (Referensen till arbetsboken är omgiven av hakparenteser, och hela arbetsboken + arket omges av enkla citattecken.)
    • 2 är kolumnnumret som innehåller matchade data som du vill returnera från;
    • 0 indikerar att returnera en exakt matchning.
  • Om uppslagsarbetsboken är stängd, kommer den fullständiga sökvägen för sökarbetsboken att visas i formeln enligt följande skärmbild:

2.9 Returnera tom eller specifik text istället för 0 eller #N/A fel

Vanligtvis, när du använder SLÖKNINGSfunktionen för att returnera ett motsvarande värde, om den matchande cellen är tom, returnerar den 0. Och om det matchande värdet inte hittas, kommer du att få ett felvärde på #N/A som visas i skärmdump nedan. Om du vill visa en tom cell eller ett specifikt värde istället för 0 eller #N/A, detta VLOOKUP För att returnera tomt eller specifikt värde istället för 0 eller N/A handledning kan göra dig en tjänst.


Avancerade exempel på VLOOKUP

3.1 Tvåvägssökning (VLOOKUP i rad och kolumn)

Ibland kan du behöva utföra en 2-dimensionell uppslagning, vilket innebär att söka efter ett värde i både rad och kolumn samtidigt. Till exempel, om du har följande dataintervall och du kan behöva få värdet för en viss produkt under ett angivet kvartal. Det här avsnittet kommer att introducera en formel för att hantera detta jobb i Excel.

I Excel kan du använda en kombination av VLOOKUP och MATCH-funktionerna för att göra en tvåvägssökning.

Använd följande formel i en tom cell och tryck sedan på ange för att få resultatet.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

Notera: I ovanstående formel:

  • G2 är uppslagsvärdet i kolumnen som du vill få motsvarande värde baserat på;
  • A2: E7 är datatabellen du kommer att titta från;
  • H1 är uppslagsvärdet i raden som du vill få motsvarande värde baserat på;
  • A2: E2 är cellerna i kolumnrubriker;
  • FALSK indikerar för att få en exakt matchning.

3.2 VLOOKUP matchande värde baserat på två eller flera kriterier

Det är lätt för dig att slå upp matchningsvärdet utifrån ett kriterium, men om du har två eller flera kriterier, vad kan du göra?

 3.2.1 VLOOKUP matchande värde baserat på två eller flera kriterier med formler

I det här fallet kan LOOKUP eller MATCH och INDEX-funktionerna i Excel hjälpa dig att lösa det här jobbet snabbt och enkelt.

Till exempel har jag nedanstående datatabell, för att returnera det matchade priset baserat på den specifika produkten och storleken kan följande formler hjälpa dig.

Steg 1: Använd valfri formel

Formel 1: När du har klistrat in formeln trycker du på ange nyckel.

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

Formel 2: När du har klistrat in formeln trycker du på Ctrl + Skift + Enter nycklar.

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

Resultat:

Anmärkningar:

  • I ovanstående formler:
    • A2: A12 = G1 betyder att söka efter kriterierna för G1 i intervallet A2:A12;
    • B2: B12 = G2 betyder att söka efter kriterierna för G2 i intervallet B2:B12;
    • D2: D12 is intervallet som du vill returnera motsvarande värde från.
  • Om du har fler än två kriterier behöver du bara slå ihop de andra kriterierna i formeln, till exempel:
    =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 matchande värde baserat på två eller flera kriterier med en smart funktion

Det kan vara utmanande att komma ihåg ovanstående komplexa formler som måste tillämpas upprepade gånger, vilket kan bromsa din arbetseffektivitet. Dock, Kutools för Excel erbjuder en Flera villkorsuppslag funktion som låter dig returnera motsvarande resultat baserat på ett eller flera villkor med bara flera klick.

  1. Klicka Kutools > Superuppslagning > Flera villkorsuppslag för att aktivera den här funktionen.
  2. Ange sedan operationerna från dialogrutan baserat på dina data.
Anmärkningar: För att använda den här funktionen bör du ladda ner Kutools för Excel med 30 dagars gratis provperiod för det första.


3.3 VLOOKUP för att returnera flera värden med ett eller flera kriterier

I Excel söker funktionen VLOOKUP efter ett värde och returnerar endast det första matchande värdet om det finns flera motsvarande värden. Ibland kanske du vill returnera alla motsvarande värden i en rad, i en kolumn eller i en enda cell. Det här avsnittet kommer att tala om hur man returnerar de multipla matchande värdena med ett eller flera villkor i en arbetsbok.

 3.3.1 VLOOKUP alla matchande värden baserat på ett eller flera villkor horisontellt

Om du antar att du har en tabell med data som innehåller land, stad och namn i intervallet A1:C14, och nu vill du returnera alla namn horisontellt som är från "USA" enligt skärmbilden nedan. För att lösa denna uppgift, tack klicka här för att få resultatet steg för steg.

 3.3.2 VLOOKUP alla matchande värden baserat på ett eller flera villkor vertikalt

Om du behöver Vlookup och returnera alla matchande värden vertikalt baserat på specifika kriterier enligt skärmbilden nedan, klicka här för att få information om lösningen.

 3.3.3 VLOOKUP alla matchande värden baserat på ett eller flera villkor till en enda cell

Om du vill Vlookup och returnera flera matchade värden till en enda cell med specificerad separator, den nya funktionen i TEXTJOIN kan hjälpa dig att lösa detta jobb snabbt och enkelt.

Anmärkningar:


3.4 VLOOKUP för att returnera hela raden i en matchad cell

I det här avsnittet kommer jag att prata om hur man hämtar hela raden med ett matchat värde genom att använda funktionen VLOOKUP.

Steg 1: Applicera och fyll i följande formel

Kopiera eller skriv in formeln nedan i en tom cell där du vill mata ut resultatet och tryck ange nyckel för att få det första värdet. Dra sedan formelcellen åt höger tills hela radens data visas.

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

Resultat:

Nu kan du se att hela raddata returneras. Se skärmdump:
doc vlookup funktion 50 1

Notera: i ovanstående formel:

  • F2 är uppslagsvärdet du vill returnera hela raden baserat på;
  • A1: D12 är dataintervallet du vill söka efter uppslagsvärdet från;
  • A1 indikerar det första kolumnnumret inom ditt dataintervall;
  • FALSK indikerar exakt uppslag.

Tips:

  • Om flera rader hittas baserat på det matchade värdet, för att returnera alla motsvarande rader, använd formeln nedan och tryck sedan på Ctrl + Skift + Enter nycklarna tillsammans för att få det första resultatet. Dra sedan fyllningshandtaget åt höger. Och fortsätt sedan att dra fyllningshandtaget ner över cellerna för att få alla matchande rader. Se demon nedan:
    =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 Kapslad VLOOKUP i Excel

Ibland kan du behöva slå upp värden som är sammanlänkade över flera tabeller. I det här fallet kan du kapsla flera VLOOKUP-funktioner tillsammans för att få det slutliga värdet.

Till exempel har jag ett kalkylblad som innehåller två separata tabeller. Den första tabellen listar alla produktnamn tillsammans med deras motsvarande säljare. Den andra tabellen visar den totala försäljningen för varje säljare. Nu, om du vill hitta försäljningen av varje produkt, som visas i följande skärmdump, kan du kapsla funktionen VLOOKUP för att utföra denna uppgift.
doc vlookup funktion 53 1

Den generiska formeln för kapslad VLOOKUP-funktion är:

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

Notera:

  • letauppvärde är det värde du letar efter;
  • Tabell_array1, Tabell_array2 är tabellerna där uppslagsvärdet och returvärdet finns;
  • col_index_num1 indikerar kolumnnumret i den första tabellen för att hitta de mellanliggande gemensamma data;
  • col_index_num2 anger kolumnnumret i den andra tabellen som du vill returnera det matchande värdet;
  • 0 används för en exakt matchning.

Steg 1: Applicera och fyll i följande formel

Använd följande formel i en tom cell och dra sedan fyllningshandtaget ner till de celler som du vill använda den här formeln.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Resultat:

Nu kommer du att få resultatet som visas i följande skärmdump:

Notera: i ovanstående formel:

  • G3 innehåller värdet du letar efter;
  • A3: B7, D3: E7 är de tabellintervall i vilka uppslagsvärdet och returvärdet finns;
  • 2 är kolumnnumret i intervallet att returnera det matchande värdet från.
  • 0 indikerar VLOOKUP exakt matematik.

3.6 Kontrollera om ett värde finns baserat på en listdata i en annan kolumn

VLOOKUP-funktionen kan också hjälpa dig att kontrollera om värden finns baserat på datalistan i en annan kolumn. Till exempel, om du vill leta efter namnen i kolumn C och bara returnera Ja eller Nej om namnet hittas eller inte i kolumn A enligt skärmbilden nedan.
doc vlookup funktion 56 1

Steg 1: Applicera och fyll i följande formel

Använd följande formel i en tom cell och dra sedan fyllningshandtaget ner till de celler som du vill ska fylla den här formeln.

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

Resultat:

Och du får resultatet som du behöver, se skärmdump:

Notera: i ovanstående formel:

  • C2 är uppslagsvärdet du vill kontrollera;
  • A2: A10 är listan över intervall varifrån man kan kontrollera om uppslagsvärdena kommer att hittas eller inte;
  • FALSK indikerar för att få en exakt matchning.

3.7 VLOOKUP och summera alla matchade värden i rader eller kolumner

När du arbetar med numeriska data kan du behöva extrahera matchade värden från en tabell och summera talen i flera kolumner eller rader. Det här avsnittet kommer att introducera några formler som kan hjälpa dig att utföra denna uppgift.

 3.7.1 VLOOKUP och summera alla matchade värden i en rad eller flera rader

Anta att du har en produktlista med försäljning i flera månader, som visas i följande skärmdump. Nu måste du summera alla beställningar under alla månader baserat på de givna produkterna.

Steg 1: Applicera och fyll i följande formel

Kopiera eller skriv in följande formel i en tom cell och tryck sedan på Ctrl + Skift + Enter nycklarna tillsammans för att få det första resultatet. Dra sedan fyllningshandtaget nedåt för att kopiera den här formeln till andra celler du behöver.

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

Resultat:

Alla värden i en rad av det första matchande värdet har summerats, se skärmdump:

Notera: i ovanstående formel:

  • H2 är cellen som innehåller det värde du letar efter;
  • A2: F9 är dataintervallet (utan kolumnrubriker) som inkluderar uppslagsvärdet och de matchade värdena;
  • 2,3,4,5,6 {} är kolumnnummer som används för att beräkna summan av intervallet;
  • FALSK indikerar en exakt matchning.

Tips: Om du vill summera alla matchningar i flera rader, använd följande formel:

  • =SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
 3.7.2 VLOOKUP och summera alla matchade värden i en kolumn eller flera kolumner

Om du vill summera det totala värdet för de specifika månaderna som visas i skärmdumpen nedan. Den vanliga VLOOKUP-funktionen kanske inte hjälper dig, här bör du använda funktionerna SUMMA, INDEX och MATCH tillsammans för att skapa en formel.

Steg 1: Använd följande formel

Använd formeln nedan i en tom cell och dra sedan fyllningshandtaget nedåt för att kopiera den här formeln till andra celler.

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

Resultat:

Nu har de första matchade värdena baserade på den specifika månaden i en kolumn summerats, se skärmdump:

Notera: i ovanstående formel:

  • H2 är cellen som innehåller det värde du letar efter;
  • B1: F1 är kolumnrubrikerna som innehåller sökningsvärdet;
  • B2: F9 är dataintervallet som innehåller de numeriska värden som du vill summera.

Tips: För att VLOOKUP och summera alla matchade värden i flera kolumner bör du använda följande formel:

  • =SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
 3.7.3 VLOOKUP och summera de första matchade eller alla matchade värden med en kraftfull funktion

Kanske ovanstående formler är svåra för dig att komma ihåg, i det här fallet kommer jag att rekommendera en kraftfull funktion - Sökning och summa of Kutools för Excel, med den här funktionen kan du Vlookup och summera de första matchande eller alla matchande värden i rader eller kolumner så enkelt som möjligt.

  1. Klicka Kutools > Superuppslagning > UTSÖKNING och summa för att aktivera den här funktionen.
  2. Ange sedan operationerna från dialogrutan baserat på ditt behov.
Anmärkningar: För att använda den här funktionen bör du ladda ner Kutools för Excel med 30 dagars gratis provperiod för det första.
 3.7.4 VLOOKUP och summera alla matchade värden både i rader och kolumner

Om du vill summera värdena när du till exempel måste matcha både kolumn och rad för att få det totala värdet av produkten Tröja i mars som visas nedan.

Här kan du använda SUMPRODCT-funktionen för att utföra denna uppgift.

Använd följande formel i en cell och tryck sedan på ange nyckel för att få resultatet, se skärmdump:

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

Notera: I ovanstående formel:

  • B2: F9 är dataintervallet som innehåller de numeriska värdena som du vill summera;
  • B1: F1 är kolumnrubriken som innehåller uppslagsvärdet som du vill summera baserat på;
  • I2 är uppslagsvärdet i kolumnrubrikerna du letar efter;
  • A2: A9 är radrubrikerna som innehåller uppslagsvärdet som du vill summera baserat på;
  • H2 är uppslagsvärdet inom radrubrikerna du letar efter.

3.8 VLOOKUP för att slå samman två tabeller baserat på nyckelkolumner

I ditt dagliga arbete, när du analyserar data, kan du behöva samla all nödvändig information i en enda tabell baserad på en eller flera nyckelkolumner. För att utföra denna uppgift kan du använda funktionerna INDEX och MATCH istället för UPPSÖKNING-funktionen.

 3.8.1 VLOOKUP för att slå samman två tabeller baserat på en nyckelkolumn

Du har till exempel två tabeller, den första tabellen innehåller produkt- och namndata, och den andra tabellen innehåller produkt- och orderdata, nu vill du kombinera dessa två tabeller genom att matcha den gemensamma produktkolumnen till en tabell.

Steg 1: Applicera och fyll i följande formel

Använd följande formel i en tom cell. Dra sedan fyllningshandtaget ner till de celler som du vill använda den här formeln

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

Resultat:

Nu kommer du att få en sammanslagen tabell med ordningskolumnen ansluten till den första tabellen baserat på nyckelkolumndata.

Notera: I ovanstående formel:

  • A2 är det sökvärde du letar efter;
  • F2: F8 är dataintervallet som du vill returnera de matchande värdena;
  • E2: E8 är sökningsområde som innehåller sökningsvärdet.
 3.8.2 VLOOKUP för att slå samman två tabeller baserade på flera nyckelkolumner

Om de två tabellerna du vill gå med i har flera nyckelkolumner, följ stegen nedan för att slå samman tabellerna baserat på dessa vanliga kolumner.

Den generiska formeln är:

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

Notera:

  • uppslagstabell är dataintervallet innehåller uppslagsdata och matchande poster;
  • lookup_value1 är det första kriteriet du letar efter;
  • lookup_range1 är datalistan innehåller de första kriterierna;
  • lookup_value2 är det andra kriteriet du letar efter;
  • lookup_range2 är datalistan innehåller det andra kriteriet;
  • return_column_number anger kolumnnumret i lookup_table som du vill returnera det matchande värdet.

Steg 1: Använd följande formel

Använd formeln nedan i en tom cell där du vill placera resultatet och tryck sedan på Ctrl + Skift + Enter knappar tillsammans för att få det första matchade värdet, se skärmdump:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Steg 2: Fyll formeln till andra celler

Välj sedan den första formelcellen och dra i fyllningshandtaget för att kopiera den här formeln till andra celler efter behov:

tips: I Excel 2016 eller senare versioner kan du också använda Power Query funktion för att slå samman två eller flera tabeller till en baserad på nyckelkolumner. Klicka för att veta detaljerna steg för steg.

3.9 VLOOKUP matchande värden över flera kalkylblad

Har du någonsin behövt utföra en VLOOKUP över flera kalkylblad i Excel? Om du till exempel har tre kalkylblad med dataintervall och du vill hämta specifika värden baserade på kriterier från dessa blad, kan du följa den steg-för-steg handledning VLOOKUP-värden över flera kalkylblad att utföra denna uppgift.


VLOOKUP-matchade värden behåller cellformatering

När du letar upp matchade värden kommer den ursprungliga cellformateringen som teckensnittsfärg, bakgrundsfärg, dataformat etc. inte att behållas. För att behålla cellen eller dataformateringen kommer det här avsnittet att introducera några knep för att lösa jobben.

4.1 VLOOKUP matchande värde och behåll cellfärg, teckensnittsformatering

Som vi alla vet kan den normala VLOOKUP-funktionen bara hämta det matchande värdet från ett annat dataområde. Det kan dock finnas tillfällen där du vill få motsvarande värde tillsammans med cellformateringen, som fyllningsfärg, teckensnittsfärg och teckensnittsstil. I det här avsnittet kommer vi att diskutera hur man hämtar matchande värden samtidigt som källformateringen bevaras i Excel.

Gör med följande steg för att leta upp och returnera motsvarande värde tillsammans med cellformatering:

Steg 1: Kopiera koden 1 till Sheet Code Module

  1. I kalkylbladet innehåller de data du vill VLOOKUP, högerklicka på arkfliken och välj Visa kod från snabbmenyn. Se skärmdump:
  2. I det öppnade Microsoft Visual Basic för applikationer kopiera nedanstående VBA-kod till kodfönstret.
  3. VBA-kod 1: VLOOKUP för att få cellformatering tillsammans med uppslagsvärde
  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
    

Steg 2: Kopiera koden 2 till modulfönstret

  1. Fortfarande i Microsoft Visual Basic för applikationer fönstret klickar Insert > Modulernaoch kopiera sedan nedanstående VBA-kod 2 till modulfönstret.
  2. VBA-kod 2: VLOOKUP för att få cellformatering tillsammans med uppslagsvärde
  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
    

Steg 3: Välj alternativet för VBAproject

  1. Efter att ha infogat ovanstående koder, klicka sedan verktyg > Referensprojekt i Microsoft Visual Basic för applikationer fönster. Kontrollera sedan Microsoft Scripting Runtime kryssrutan i rutan Referenser - VBAProject dialog ruta. Se skärmdumpar:
  2. Klicka sedan OK för att stänga dialogrutan och sedan spara och stänga kodfönstret.

Steg 4: Skriv formeln för att få resultatet

  1. Gå nu tillbaka till kalkylbladet, använd följande formel. Och dra sedan fyllningshandtaget nedåt för att få alla resultat tillsammans med deras formatering. Se skärmdump:
    =LookupKeepFormat(E2,$A$1:$C$10,3)

Notera: i ovanstående formel:

  • E2 är värdet du kommer att slå upp;
  • A1: C10 är bordssortimentet;
  • 3 är kolumnnumret för tabellen från vilken du vill hämta det matchade värdet.

4.2 Behåll datumformatet från ett VLOOKUP returnerat värde

När du använder funktionen VLOOKUP för att slå upp och returnera ett värde med datumformat, kan det returnerade resultatet visas som ett tal. För att behålla datumformatet i det returnerade resultatet, bör du bifoga funktionen VLOOKUP i TEXT-funktionen.

Steg 1: Applicera och fyll i följande formel

Använd formeln nedan i en tom cell. Dra sedan i fyllningshandtaget för att kopiera den här formeln till andra celler.

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

Resultat:

Alla matchade datum har returnerats enligt nedanstående skärmdump:

Notera: I ovanstående formel:

  • E2 är uppslagsvärdet;
  • A2: C9 är uppslagsintervallet;
  • 3 är kolumnnumret du vill att värdet ska returneras;
  • FALSK indikerar att få en exakt matchning;
  • mm / dd / åååå är det datumformat du vill behålla.

4.3 Returnera cellkommentar från VLOOKUP

Har du någonsin behövt hämta både matchande celldata och dess associerade kommentar med VLOOKUP i Excel, som visas i följande skärmdump? Om så är fallet kan den användardefinierade funktionen nedan hjälpa dig att utföra denna uppgift.

Steg 1: Kopiera koden till en modul

  1. Håll ner ALT + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.
  2. Klicka Insert > Modulerna, kopiera och klistra sedan in följande kod i modulfönstret.
    VBA-kod: Vlookup och returnera matchande värde med cellkommentar:
    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. Spara och stäng sedan kodfönstret.

Steg 2: Skriv in formeln för att få resultatet

  1. Ange nu följande formel och dra i fyllningshandtaget för att kopiera denna formel till andra celler. Det kommer att returnera både matchade värden och kommentarer samtidigt, se skärmdump:
    =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

Notera: I ovanstående formel:

  • D2 är det uppslagsvärde du vill returnera dess motsvarande värde;
  • A2: B9 är datatabellen du vill använda;
  • 2 är kolumnnumret som innehåller det matchade värdet du vill returnera;
  • FALSK indikerar för att få en exakt matchning.

4.4 VLOOKUP nummer lagrade som text

Till exempel har jag ett dataintervall där ID-numret i den ursprungliga tabellen är i nummerformat och ID-numret i uppslagscellerna lagras som text, du kan stöta på ett #N/A-fel när du använder den normala SÖKUPPSÖKNING-funktionen. I det här fallet, för att hämta den korrekta informationen, kan du slå in funktionerna TEXT och VÄRDE i UPPSÖKNINGSfunktionen. Nedan är formeln för att uppnå detta:

Steg 1: Applicera och fyll i följande formel

Använd följande formel i en tom cell och dra sedan fyllningshandtaget nedåt för att kopiera den här formeln.

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

Resultat:

Nu kommer du att få rätt resultat enligt nedanstående skärmdump:

Anmärkningar:

  • I ovanstående formel:
    • D2 är det uppslagsvärde du vill returnera dess motsvarande värde;
    • A2: B8 är datatabellen du vill använda;
    • 2 är kolumnnumret som innehåller det matchade värdet du vill returnera;
    • 0 indikerar för att få en exakt matchning.
  • Denna formel fungerar också bra om du inte är säker på var du har siffror och var du har text.
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