Så här söker du upp flera värden i Excel: steg-för-steg

När du arbetar med stora uppsättningar data i Microsoft Excel kan det vara utmanande att hitta flera värden som matchar flera villkor. Vissa inbyggda funktioner, t.ex. VLOOKUP, designades ursprungligen för att fungera med enskilda värden.

Flera kan kombineras för att slå upp flera värden. De inkluderar funktionerna VLOOKUP, INDEX, MATCH och IF. Aktuella versioner av Excel kan använda dynamiska arrayer medan äldre versioner använder arrayformler.

Den här artikeln visar dig exakt hur du använder dessa funktioner i formler som hittar flera värden i din data.

Nu går vi!

Innehållsförteckning

• Hur man använder VLOOKUP med flera värden
  • Vad är matrisformler?
  • VLOOKUP Exempel
  • Senaste versionerna av Excel
  • Äldre versioner
  • Exakt matchning vs ungefärlig matchning
  • Mer om VLOOKUP
• Hur man använder INDEX och MATCH för att slå upp flera värden
  • Hur man använder INDEX och MATCH tillsammans i Excel 365
  • Arbeta med äldre versioner av Excel
• Hur man använder IF-funktionen för att hitta flera värden
  • Aktuella versioner av Excel
  • Äldre versioner av Excel
• Hur man använder SUMPRODUCT för flera kriterier
• 3 vanliga fel med uppslagsfunktioner
  • 1. #N/A Fel
  • 2. #REF! Fel
  • 3. Cirkulära fel
• Hur man använder IFERROR med uppslagsfunktioner
• 7 avancerade uppslagstekniker
  • 1. Relativ positionssökning
  • 2. LITEN Funktion
  • 3. ROW-funktion
  • 4. FILTER Funktion
  • 5. Hjälpkolumner
  • 6. Dynamiska arrayer
  • 7. Power Query
• Slutgiltiga tankar

Hur man använder VLOOKUP med flera värden

Funktionen används ofta för att hitta enskilda värden i en rad data. Du kan dock även slå upp flera matchningar i Excel med denna uppslagsformel.

Som standard returnerar den bara det första matchande värdet den hittar. Du kan dock ändra funktionen för att returnera flera värden genom att använda en matrisformel.

Vad är matrisformler?

En matrisformel är en formel som kan utföra beräkningar på matriser av data. Det kallas en arrayformel eftersom den kan returnera en array av resultat snarare än ett enda värde.

Det finns flera enkla steg när du skapar en matrisformel:

1. Välj ett cellområde att söka efter.

2. Ange formeln i formelfältet.

3. Tryck på Ctrl + Shift + Enter för att slutföra det.

Syntaxen för en matrisformel liknar den för en vanlig formel, men den innehåller klammerparenteser {} runt formeln. De lockiga klammerparenteserna indikerar att formeln är en matrisformel och att den kommer att returnera en matris med värden.

Exemplen i den här artikeln visar hur du använder matrisformler korrekt.

VLOOKUP Exempel

Vårt exempel har fem objekt i kolumn A i ett kalkylblad:

• Äpple

• Banan

• Bröd

• Morot

• Körsbär

Uppgiften är att kontrollera om tre specifika frukter finns i denna lista: äpple, banan och körsbär.

Senaste versionerna av Excel

VLOOKUP-syntaxen beror på vilken version av Excel du använder.

De senaste versionerna ( Excel 365 eller Excel 2021 ) stöder dynamiska arrayer. Den här funktionen tillåter formler att returnera flera resultat som "spill" in i angränsande celler.

Detta är syntaxen (likhetstecknet startar formeln):

=SÖKUP(uppslagsvärde, tabellmatris, kolindex_nummer, [intervallsökning])

• lookup_value: Värdet som du vill slå upp.

• table_array: Hela datatabellen som du vill söka i.

• col_index_num: Tabellens kolumnnummer i table_array som innehåller de data du vill returnera.

• range_lookup: Valfritt. Anger om du vill ha en exakt matchning eller en ungefärlig matchning.

Vårt specifika exempel använder denna formel:

=VLOOKUP({“äpple”,,”banan”,,”körsbär”},A1:A5,1,FALSK)

När formeln skrivs in i cell B1 rinner resultaten in i cellerna C1 och D1. Den här bilden visar exemplet i aktion:

Äldre versioner

Om du använder en äldre version av Excel som inte stöder dynamiska arrayer (t.ex. Excel 2019 eller tidigare), måste du använda ett lite annorlunda tillvägagångssätt med en arrayformel.

Följ dessa steg:

1. Klicka på cellen där du vill visa resultaten för det första objektet (t.ex. kolumn B).

2. Skriv följande formel utan att trycka på Retur ännu: =VLOOKUP({“apple”,,”banana”,,”cherry”}, A1:A5, 1, FALSE)

3. Tryck på Ctrl + Skift + Enter för att omvandla detta till en matrisformel.

4. Kopiera cell B1 och klistra in den i cellen nedan (eller använd fyllningshandtagen).

När du använder Ctrl + Skift + Enter lägger Excel till klammerparenteser runt formeln. Detta indikerar att det är en matrisformel.

Exakt matchning vs ungefärlig matchning

Som standard använder VLOOKUP-funktionen en ungefärlig matchning. Det betyder att den kommer att returnera den närmaste matchningen den kan hitta, även om cellvärdena inte är en exakt matchning.

Om du vill utföra en exakt matchning kan du ställa in range_lookup-argumentet till FALSE.

Tänk på att ungefärliga matchningar fungerar bäst med ordnade numeriska värden. Det är vanligtvis inte lämpligt när cellvärdet är text.

Mer om VLOOKUP

Om du vill lära dig mer om denna mångsidiga funktion, kolla in dessa artiklar:

Nu när du är inställd med VLOOKUP-funktionen, låt oss ta en titt på två andra funktioner som kan göra vad den gör på ett annat sätt: INDEX och MATCH.

Hur man använder INDEX och MATCH för att slå upp flera värden

Du kan kombinera funktionerna INDEX och MATCH tillsammans för att hitta flera värden i flera rader.

Funktionen INDEX i Excel returnerar ett värde eller referens till en cell inom ett angivet intervall.

=INDEX(matris; radnummer; [kolumnnummer])

• array: Området av celler som ska sökas efter värdet.

• row_num: Radnumret inom arrayen från vilket ett värde ska returneras.

• column_num: (Valfritt) Kolumnnumret inom arrayen från vilket ett värde ska returneras. Om den utelämnas returnerar funktionen hela raden.

MATCH-funktionen i Excel returnerar positionen för ett värde inom ett angivet intervall.

=MATCH(uppslagsvärde, uppslagsmatris, [matchningstyp])

• lookup_value: Värdet som ska sökas efter inom lookup_arrayen.

• lookup_array: Området av celler som ska sökas efter lookup_value.

• match_type: (Valfritt) Typen av matchning som ska utföras. Om den utelämnas utför funktionen en exakt matchning.

Hur man använder INDEX och MATCH tillsammans i Excel 365

För att använda INDEX och MATCH tillsammans för att slå upp flera värden i Excel, måste du använda en matrisformel.

När du arbetar med tidigare exempeldata är det här formeln i Excel 365:

=INDEX(A1:A5, MATCH({"äpple","banan","körsbär"}, A1:A5, 0))

Exemplet ovan delas upp så här:

• INDEX: detta returnerar värdet av en cell i ett specificerat intervall baserat på ett givet rad- och kolumnnummer. I det här fallet kommer det att returnera värdet från intervallet A1:A5.

• A1:A5: Detta är det definierade tabellintervallet där du söker efter värdet och från vilket resultatet kommer att returneras.

• MATCH: detta söker efter ett specificerat objekt i ett cellintervall och returnerar den relativa positionen för objektet i intervallet.

• {“apple”,,”banana”,,”cherry”}: detta är arraykonstanten som innehåller de värden du vill slå upp.

• A1:A5: detta är intervallet där MATCH söker efter värden från arraykonstanten.

• 0: detta är matchningstypen för MATCH-funktionen. I det här fallet är det 0, vilket betyder att du letar efter en exakt matchning istället för en nära matchning.

 Den här bilden visar formeln i aktion:

Arbeta med äldre versioner av Excel

Om du använder en äldre Excel-fil som inte stöder dynamiska arrayer (t.ex. Excel 2019 eller tidigare), måste du använda ett annat tillvägagångssätt.

Eftersom äldre versioner inte stöder formler som "spills" in i angränsande celler, måste du dela upp användningen i tre separata formler.

Följ dessa steg:

1. Klicka på cellen där du vill ha resultatet för det första objektet (t.ex. cell B1)

2. Skriv in formeln nedan:

3. =INDEX(A1:A5, MATCH("äpple", A1:A5, 0))

4. Tryck på Enter för att utföra formeln.

5. Skriv den här formeln i cell B2: =INDEX(A1:A5, MATCH("banan", A1:A5, 0))

6. Skriv denna formel i cell B3: =INDEX(A1:A5, MATCH("körsbär", A1:A5, 0))

Den här bilden visar cellreferens B3:

Funktionerna INDEX och MATCH är inte de enda som kan göra det möjligt för dig att hitta flera värden. I nästa avsnitt tittar vi på hur du kan använda IF-funktionen som ett alternativ.

Hur man använder IF-funktionen för att hitta flera värden

Ett annat sätt att slå upp flera cellvärden baserat på vissa kriterier är att använda IF-funktionen med andra funktioner.

IF-funktionen låter dig testa flera tillstånd och returnera olika resultat beroende på resultatet av dessa tester.

Låt oss till exempel säga att du har en tabell med försäljningsdata med kolumner för Produkt och Försäljning. Du vill slå upp och summera försäljningsbeloppet för två av de tre produkterna.

Aktuella versioner av Excel

För att hitta summan av kolumnen Försäljning där produkten är antingen "Äppel" eller "Banan" med hjälp av OM-funktionen, kan du använda en matrisformel med funktionerna OM, SUMMA och ELLER.

Förutsatt att dina data börjar i cell A1, använd följande formel:

=SUMMA(OM((A2:A4=”Äpple”)+(A2:A4=”Banan”), B2:B4, 0))

Sektionen (A2:A4=”Apple”)+(A2:A4=”Banana”) skapar en array som har värdet 1 om cellen i intervallet A2:A4 innehåller ”Apple” eller ”Banana” och 0 annars .

IF-satsen kontrollerar varje element i arrayargumentet. Om värdet är 1 (dvs produkten är antingen "Apple" eller "Banana"), tar den motsvarande värde i kolumnen Försäljning (intervall B2:B4); annars tar det 0.

SUM-funktionen summerar värdena från IF-funktionen och summerar effektivt försäljningsvärdena för både "Apple" och "Banana".

Den här bilden visar formeln i aktion på sökintervallet:

Äldre versioner av Excel

I Excel 2019 eller tidigare måste du använda en matrisformel. Följ dessa steg:

1. Skriv formeln men tryck inte på Retur.

2. Tryck på Ctrl + Shift + Enter för att göra det till en matrisformel.

Excel lägger till krulliga parenteser {} runt formeln, vilket indikerar att det är en matrisformel.

Därefter tittar vi på hur du kan använda SUMPRODUCT för att slå upp flera värden baserat på dina kriterier. Nu går vi!

Hur man använder SUMPRODUCT för flera kriterier

SUMPRODUCT-funktionen låter dig också slå upp flera värden baserat på flera kriterier.

Eftersom det inte kräver användning av en matrisformel är syntaxen densamma oavsett version av Excel.

Med samma data som i föregående exempel ser formeln ut så här:

=SUMMAPRODUKT((A2:A4=”Äpple”)+(A2:A4=”Banan”), B2:B4)

Sektionen (A2:A4=”Apple”)+(A2:A4=”Banana”) skapar en array som har värdet 1 om cellen i intervallet A2:A4 innehåller ”Apple” eller ”Banana” och 0 annars .

SUMMAPRODUKT-funktionen multiplicerar elementen i arrayen med motsvarande element i kolumnen Försäljning (intervall B2:B4). Den summerar sedan de resulterande värdena och summerar effektivt försäljningsvärdena för både "Apple" och "Banana".

Formeln nedan visar hur det fungerar:

Excel-funktioner är fantastiska när de fungerar som förväntat, men ibland kan du stöta på fel. I nästa avsnitt tar vi upp några av de vanligaste felen och hur du kan hantera dem.

3 vanliga fel med uppslagsfunktioner

Uppslagsfunktioner kan ibland returnera fel som kan vara frustrerande och tidskrävande att felsöka. De tre vanligaste felen du kommer att stöta på är:

1. #N/A Fel

2. #REF! Fel

3. Cirkulära fel

1. #N/A Fel

#N/A-felet uppstår när uppslagsvärdet inte kan hittas i uppslagsmatrisen.

Det finns flera anledningar till att det här felet kan uppstå, inklusive:

• uppslagsvärdet är felstavat eller felaktigt.

• uppslagsmatrisen är inte sorterad i stigande ordning.

• uppslagsvärdet finns inte i datamängden.

När uppslagsvärdet inte finns i datamängden är detta användbar information. Men oerfarna Excel-användare kan tro att #N/A betyder att något har gått fel med formeln. Nästa avsnitt visar hur man gör detta mer användarvänligt.

2. #REF! Fel

Domaren! fel uppstår när uppslagsmatrisen eller returmatrisen tas bort eller flyttas.

Detta fel kan åtgärdas genom att uppdatera cellreferenserna i uppslagsfunktionen.

3. Cirkulära fel

När du kombinerar funktioner i komplexa formler kan Excel berätta att du har en cirkulär referens.

Du kommer att finna dessa lättare att undersöka genom att använda vår guide till .

Hur man använder IFERROR med uppslagsfunktioner

IFERROR-funktionen är ett användbart verktyg för att hantera fel i uppslagsfunktioner. Den låter dig ange ett värde eller en formel som ska returneras om uppslagsfunktionen returnerar ett fel.

Syntaxen för IFERROR-funktionen är följande:

=IFERROR(värde, värde_om_fel)

· värde: värde eller formel som du vill utvärdera.

· Value_if_error: värdet eller formeln som ska returneras om det första argumentet returnerar ett fel.

Låt oss till exempel säga att du har en VLOOKUP-funktion som letar upp flera värden i en tabell. På bilden nedan finns ett av värdena inte i det sökta dataområdet.

Som du kan se visas #N/A-felet, vilket kan vara förvirrande för oerfarna Excel-användare.

Istället kan du använda IFERROR för att visa en tom cell eller ett meddelande som säger "Not found" med denna syntax:

=IFERROR(VLOOKUP(uppslagsvärde, tabellmatris, kolumnindex, FALSK), “hittades inte”)

I det här exemplet, om funktionen VLOOKUP returnerar ett fel, returnerar IFERROR-funktionen meddelandet "Not found" istället.

Den här bilden visar formeln i aktion. Kolumn B har det saknade värdet, medan kolumn C och kolumn D har hittat matchningar.

Vi har täckt mycket mark hittills, och du är äntligen redo att lära dig mer avancerade tekniker för uppslagningar, vilket är ämnet i nästa avsnitt.

7 avancerade uppslagstekniker

Att slå upp flera värden i Excel kan vara en utmanande uppgift, särskilt när man hanterar stora datamängder. Du kan stöta på prestandaproblem med långsam bearbetning.

Det finns sju avancerade uppslagstekniker som du kan använda för att göra processen enklare och mer effektiv.

• Relativ positionssökning

• LITEN funktion

• ROW-funktion

• FILTER Funktion

• Hjälparkolumner

• Dynamiska arrayer

• Power Query

1. Relativ positionssökning

En av de enklaste är att använda relativ positionssökning. Detta innebär att specificera rad- och kolumnförskjutningar från en given cell för att hitta det önskade värdet.

Om du till exempel har en datatabell och vill slå upp ett värde som är två rader nedåt och tre kolumner till höger om en given cell, kan du använda följande formel:

=OFFSET(cell; 2; 3)

2. LITEN Funktion

En annan användbar teknik för uppslagning av flera värden är att använda SMALL-funktionen. Denna funktion returnerar det n:te minsta värdet i ett cellintervall.

Genom att kombinera den här funktionen med andra uppslagsfunktioner som INDEX och MATCH kan du slå upp flera värden baserat på specifika kriterier.

Till exempel söker följande formel upp det näst minsta värdet i ett cellintervall som uppfyller ett visst villkor:

=INDEX(data, MATCH(LITEN(OM(kriterier, intervall), 2), intervall, 0))

3. ROW-funktion

ROW-funktionen kan också användas för flera värdeuppslagningar i Excel. Denna funktion returnerar radnumret för en given cell, som kan användas för att referera till celler i en datatabell.

Till exempel, följande formel slår upp ett värde i en tabell baserat på en unik identifierare:

=INDEX(data, MATCH(unik_identifierare, data[unik_identifierare], 0), kolumn_indexnummer)

4. FILTER Funktion

FILTER-funktionen är inte tillgänglig i äldre versioner av Excel.

I Excel 365 kan du använda den för att filtrera ett cellintervall baserat på vissa kriterier och endast returnera de värden som uppfyller dessa kriterier. Detta är syntaxen och tre argument:

=FILTER(matris, inkludera, [om_tom])

• array: Den specifika data du vill filtrera.

• include: Argumentet include är de kriterier eller villkor som du vill tillämpa på arrayen.

• [if_empty] (valfritt): Värdet som ska returneras om inga rader eller kolumner uppfyller kriterierna som anges i include-argumentet.

Till exempel fungerar följande formel på exempeldata för att hitta matchningar för två av de tre objekten i den första kolumnen och summera deras motsvarande värden i den andra kolumnen.

=SUMMA(FILTER(B2:B4, (A2:A4=”Äpple”)+(A2:A4=”Banan”)))

Den här bilden visar hur många rader som matchas och summan av de olika värdena:

5. Hjälpkolumner

Du kan använda en hjälpkolumn för att sammanfoga flera fält inom en funktion som VLOOKUP.

Anta att du arbetar med för- och efternamn i ett separat blad. Du kan sammanfoga dem i en hjälpkolumn som hänvisas till i den slutliga formeln.

6. Dynamiska arrayer

Som du har lärt dig i tidigare exempel kan Microsoft 365-användare dra fördel av dynamiska arrayer för uppslagning av flera värden i Excel.

Dynamiska arrayer låter dig returnera flera värden från en enda formel, vilket gör det lättare att slå upp stora mängder data.

7. Power Query

Power Query är ett kraftfullt verktyg i Excel som kan användas för att returnera värden baserat på flera kriterier.

Till exempel hittar den här videon rörig data i ett kalkylblad och rensar upp det.

Slutgiltiga tankar

Det avslutar vår djupdykning i konsten att slå upp flera värden i Excel! När du har bemästrat formlerna VLOOKUP, INDEX, MATCH och array, kommer du att finna dig själv genom att gå igenom komplexa datamängder som en het kniv genom smör.

Nyckeln är att förstå syntaxen och logiken bakom varje formel. Tänk på att dessa formler kan vara komplexa, så det är viktigt att du tar dig tid och testar dina formler noggrant innan du förlitar dig på dem för viktig dataanalys.

Excel är ett kraftfullt verktyg, men det är bara så bra som användaren. Så fortsätt finslipa dessa färdigheter, fortsätt experimentera, och du kommer snart att bli mästaren på flervärdessökningar. Tills nästa gång, fortsätt att knäppa de siffrorna och få Excel att göra det hårda arbetet åt dig!