Hoppa till huvudinnehåll

Excel INDEX MATCH: Grundläggande och avancerade sökningar

I Excel är det ofta ofta nödvändigt att exakt hämta specifika data. Även om funktionerna INDEX och MATCH har sina egna styrkor, låser kombinationen upp en kraftfull verktygsuppsättning för datasökning. Tillsammans underlättar de en rad sökmöjligheter, från grundläggande horisontella och vertikala sökningar till mer avancerade funktioner som tvåvägssökningar, skiftlägeskänsliga sökningar och sökningar med flera kriterier. Erbjuder förbättrade möjligheter jämfört med VLOOKUP, sammankopplingen av INDEX och MATCH möjliggör ett bredare utbud av datauppslagningsalternativ. Låt oss i den här handledningen fördjupa oss i djupet av möjligheter de kan uppnå tillsammans.


Hur man använder INDEX och MATCH i Excel

Innan vi använder funktionerna INDEX och MATCH, låt oss se till att vi vet hur INDEX och MATCH kan hjälpa oss att slå upp värden först.


Hur man använder INDEX-funktionen i Excel

Smakämnen INDEX funktion i Excel returnerar värdet på en given plats i ett specifikt intervall. Syntaxen för INDEX-funktionen är följande:

=INDEX(array, row_num, [column_num])
  • array (obligatoriskt) hänvisar till intervallet varifrån du vill returnera värdet.
  • rad_nummer (obligatoriskt, om inte kolumnnummer finns) hänvisar till arrayens radnummer.
  • kolumnnummer (valfritt, men obligatoriskt om rad_nummer är utelämnad) hänvisar till arrayens kolumnnummer.

Till exempel att veta poängen av Jeff, den 6eleven på listan kan du använda INDEX-funktionen så här:

=INDEX(C2:C11,6)

Excel index match 01

√ Obs: Räckvidden C2: C11 är där poängen anges, medan antalet 6 hittar provresultatet för 6eleven.

Låt oss göra ett litet test här. För formeln =INDEX(A1:C1,2;XNUMX), vilket värde kommer det att returnera? --- Ja, det kommer tillbaka Födelsedatum, den 2andra värdet i den givna raden.

Nu borde vi veta att INDEX-funktionen kan fungera perfekt med horisontella eller vertikala intervall. Men vad händer om vi behöver det för att returnera ett värde i ett större intervall med flera rader och kolumner? Tja, i det här fallet bör vi tillämpa både ett radnummer och ett kolumnnummer. Till exempel för att ta reda på det Jeffs poäng inom tabellområdet istället för en enda kolumn, kan vi lokalisera hans poäng med en radnummer 6 och en kolumn nummer 3 i celler genom A2 till C11 så här:

=INDEX(A2:C11,6,3)

Excel index match 02

Saker vi borde veta om INDEX-funktionen i Excel:
  • INDEX-funktionen kan arbeta med vertikala och horisontella intervall.
  • Om båda rad_nummer och kolumnnummer argument används, rad_nummer går före kolumnnummer, och INDEX hämtar värdet i skärningspunkten mellan det angivna rad_nummer och kolumnnummer.

Men för en riktigt stor databas med flera rader och kolumner är det verkligen inte bekvämt för oss att tillämpa formeln med ett exakt radnummer och kolumnnummer. Och det här är när vi ska kombinera användningen av MATCH-funktionen.


Hur man använder MATCH-funktionen i Excel

MATCH-funktionen i Excel returnerar ett numeriskt värde, platsen för ett specifikt objekt i det givna intervallet. Syntaxen för MATCH-funktionen är följande:

=MATCH(lookup_value, lookup_array, [match_type])
  • letauppvärde (obligatoriskt) hänvisar till värdet som ska matchas i lookup_array.
  • lookup_array (obligatoriskt) hänvisar till intervallet av celler där du vill att MATCH ska söka.
  • matchningstyp (frivillig): 1, 0 or -1.
    • 1 (standard) hittar MATCH det största värdet som är mindre än eller lika med letauppvärde. Värdena i lookup_array måste placeras i stigande ordning.
    • 0MATCH hittar det första värdet som exakt är lika med letauppvärde. Värdena i lookup_array kan vara i vilken ordning som helst. (I de fall matchningstypen är inställd på 0 kan du använda jokertecken.)
    • -1, MATCH hittar det minsta värdet som är större än eller lika med letauppvärde. Värdena i lookup_array måste placeras i fallande ordning.

Till exempel att veta Veras position i namnlistan, kan du använda MATCH-formeln så här:

=MATCH("Vera",A2:A11,0)

Excel index match 3

√ Notera: Resultatet "4" indikerar att namnet "Vera" är på 4:e plats i listan.

Saker vi bör veta om MATCH-funktionen i Excel:
  • Funktionen MATCH returnerar positionen för uppslagsvärdet i uppslagsmatrisen, inte själva värdet.
  • MATCH-funktionen returnerar den första matchningen vid dubbletter.
  • Precis som INDEX-funktionen kan MATCH-funktionen även fungera med vertikala och horisontella intervall.
  • MATCH är inte skiftlägeskänsligt.
  • Om letauppvärde av MATCH-formeln är i form av text, omge den inom citattecken.
  • Om letauppvärde hittas inte i lookup_array, den # N / A felet returneras.

Nu när vi vet om de grundläggande användningarna av funktionerna INDEX och MATCH i Excel, låt oss kavla upp ärmarna och göra oss redo att kombinera de två funktionerna.


Hur man kombinerar INDEX och MATCH i Excel

Se exemplet nedan för att ta reda på hur vi kan kombinera funktionerna INDEX och MATCH:

Att hitta Evelyns poäng, med vetskapen om att provresultaten finns i 3rd kolumnen, vi kan använd MATCH-funktionen för att automatiskt bestämma radens position utan att behöva räkna det manuellt. Därefter kan vi använda INDEX-funktionen för att hämta värde i skärningspunkten mellan den identifierade raden och den tredje kolumnen:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Excel index match 4

Tja, eftersom formeln kan se lite komplicerad ut, låt oss gå igenom varje del av den.

Excel index match 5

Smakämnen INDEX formeln innehåller tre argument:

  • rad_nummer: MATCH("Evelyn",A2:A11,0) ger INDEX radpositionen för värdet "evelyn" innom räckhåll A2: A11, vilket är 5.
  • kolumnnummer: 3 specificerar 3rd kolumn för INDEX för att lokalisera poängen inom arrayen.
  • array: A2: C11 instruerar INDEX att returnera det matchande värdet vid skärningspunkten mellan den angivna raden och kolumnen, inom intervallet från A2 till C11. Äntligen får vi resultatet 90.

I formeln ovan använde vi ett hårdkodat värde, "Evelyn". Men i praktiken är hårdkodade värden opraktiska, eftersom de skulle kräva modifiering varje gång vi söker efter olika data, till exempel poängen för en annan elev. I sådana scenarier kan vi använda cellreferenser för att skapa dynamiska formler. Till exempel, i det här fallet kommer jag att göra det ändra "Evelyn" till F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

(AD) Förenkla uppslagningar med Kutools: Ingen formelskrivning krävs!

Kutools för Excel's Superuppslag ger en mängd olika sökverktyg skräddarsydda för att möta alla dina behov. Oavsett om du gör sökningar med flera kriterier, söker i flera ark eller gör en-till-många-sökningar, Superuppslag förenklar processen med bara några klick. Utforska dessa funktioner att se hur Superuppslag ändrar hur du interagerar med Excel-data. Säg adjö till besväret med att komma ihåg komplexa formler.

Kutools uppslagsverktyg

Kutools för Excel - Ger dig mer än 300 praktiska funktioner för enkel produktivitet. Missa inte din chans att prova det med en 30-dagars fullfjädrad provperiod! Börja nu!


Exempel på formeln INDEX och MATCH

I den här delen kommer vi att prata om olika omständigheter för att använda funktionerna INDEX och MATCH för att möta olika behov.


INDEX och MATCH för att tillämpa en tvåvägssökning

I föregående exempel kände vi till kolumnnumret och använde en MATCH-formel för att hitta radnumret. Men vad händer om vi är osäkra på kolumnnumret också?

I sådana fall kan vi utföra en tvåvägssökning, även känd som en matrissökning, genom att använda två MATCH-funktioner: en för att hitta radnumret och den andra för att bestämma kolumnnumret. Till exempel att veta Evelyns poäng, bör vi använda formeln:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

Excel index match 6

Hur denna formel fungerar:
  • Den första MATCH-formeln hittar Evelyns plats i listan A2:A11, vilket ger 5 som radnummer till INDEX.
  • Den andra MATCH-formeln bestämmer kolumnen för poängen och avkastningen 3 som kolumnnummer till INDEX.
  • Formeln förenklar till =INDEX(A2:C11,5,3;XNUMX), och INDEX returnerar 90.

INDEX och MATCH för att tillämpa en vänsteruppslagning

Låt oss nu överväga ett scenario där du måste bestämma Evelyns klass. Du kanske har märkt att klasskolumnen är placerad till vänster om namnkolumnen, en situation som överskrider kapaciteten hos en annan kraftfull Excel-uppslagsfunktion, VLOOKUP.

Faktum är att möjligheten att utföra sökningar på vänster sida är en av aspekterna där kombinationen av INDEX och MATCH överglänser VLOOKUP.

Att hitta Evelyns klass, använd följande formel för att sök efter Evelyn i B2:B11 och hämta motsvarande värde från A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

Excel index match 7

Notera: Du kan enkelt utföra en vänstersökning efter specifika värden med hjälp av SÖKNING från höger till vänster egenskap av Kutools för Excel med bara några klick. För att implementera funktionen, navigera till Kutools fliken i Excel och klicka Superuppslag > SÖKNING från höger till vänster i Formel grupp.

SÖKNING från höger till vänster

Om du inte har installerat Kutools, klicka här för att ladda ner och få en 30-dagars fullfjädrad gratis provperiod!


INDEX och MATCH för att använda en skiftlägeskänslig sökning

MATCH-funktionerna är i sig skiftlägesokänsliga. Men när du kräver att din formel skiljer mellan versaler och gemener kan du förbättra den genom att inkludera EXAKT fungera. Genom att kombinera MATCH-funktionen med EXAKT i en INDEX-formel kan du sedan effektivt utföra en skiftlägeskänslig sökning, som visas nedan:

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))
  • array hänvisar till intervallet varifrån du vill returnera värdet.
  • letauppvärde hänvisar till värdet som ska matchas, med tanke på fallet med tecken, i lookup_array.
  • lookup_array hänvisar till intervallet av celler där du vill att MATCH ska jämföras med letauppvärde.

Till exempel att veta JIMMYs provresultat, använd följande formel:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Obs! Detta är en matrisformel som kräver att du anger med ctrl + shift + ange, förutom i Excel 365 och Excel 2021.

Excel index match 8

Hur denna formel fungerar:
  • Funktionen EXAKT jämför "JIMMY" med värdena i listan A2: A11, med tanke på skiftläge för tecken: Om de två strängarna matchar exakt, med hänsyn till både versaler och gemener, returnerar EXACT SANN; annars kommer den tillbaka FALSK. Som ett resultat får vi en array som innehåller värden TRUE och FALSE.
  • MATCH-funktionen hämtar sedan positionen för det första TRUE-värdet i arrayen, vilket borde vara 10.
  • Slutligen hämtar INDEX värdet vid 10position som tillhandahålls av MATCH i arrayen.

Anmärkningar:

  • Kom ihåg att ange formeln korrekt genom att trycka Ctrl + Skift + Enter, om du inte använder excel 365 or excel 2021, i så fall trycker du bara på ange.
  • Ovanstående formel söker inom en enda lista C2: C11. Om du vill söka inom ett intervall med flera kolumner och rader, säg A2: C11, bör du erbjuda både kolumn- och radnummer till INDEX:
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • I denna reviderade formel använder vi MATCH-funktionen för att söka efter "JIMMY", med tanke på fallet med tecken, i intervallet A2: A11, och när vi väl hittar en matchning hämtar vi motsvarande värde från 3rd kolumn i intervallet A2: C11.

INDEX och MATCH för att hitta en närmast matchning

I Excel kan du stöta på situationer där du behöver hitta den närmaste eller närmaste matchningen till ett specifikt värde i en datauppsättning. I sådana scenarier kan det vara oerhört användbart att använda en kombination av INDEX- och MATCH-funktioner, tillsammans med ABS- och MIN-funktionerna.

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • array hänvisar till intervallet varifrån du vill returnera värdet.
  • lookup_array hänvisar till det värdeintervall där du vill hitta den närmaste matchningen letauppvärde.
  • letauppvärde hänvisar till värdet för att hitta dess närmaste matchning.

Till exempel för att ta reda på det vars poäng är närmast 85, använd följande formel för att sök efter närmaste poäng till 85 i C2:C11 och hämta motsvarande värde från A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Obs! Detta är en matrisformel som kräver att du anger med ctrl + shift + ange, förutom i Excel 365 och Excel 2021.

Hur denna formel fungerar:
  • ABS (C2: C11-85) beräknar den absoluta skillnaden mellan varje värde i intervallet C2: C11 och 85, vilket resulterar i en rad absoluta skillnader.
  • MIN(ABS(C2:C11-85)) hittar minimivärdet i arrayen av absoluta skillnader, som representerar den närmaste skillnaden till 85.
  • MATCH-funktionen MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) hittar sedan positionen för den minsta absoluta skillnaden i arrayen av absoluta skillnader, vilket bör vara 10.
  • Slutligen hämtar INDEX värdet vid positionen i listan A2: A11 som motsvarar poängen närmast 85 innom räckhåll C2: C11.

Anmärkningar:

  • Kom ihåg att ange formeln korrekt genom att trycka Ctrl + Skift + Enter, om du inte använder excel 365 or excel 2021, i så fall trycker du bara på ange.
  • I händelse av oavgjort, kommer denna formel att returnera den första matchen.
  • Att hitta den närmaste matchen till medelpoängen, byta ut 85 i formeln med MEDEL(C2:C11).

INDEX och MATCH för att tillämpa en sökning med flera kriterier

För att hitta ett värde som uppfyller flera villkor, som kräver att du söker i två eller flera kolumner, använd följande formel. Formeln låter dig utföra en sökning med flera kriterier genom att ange olika villkor i olika kolumner, vilket hjälper dig att hitta det önskade värdet som uppfyller alla angivna kriterier.

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…), 0))

√ Obs! Detta är en matrisformel som kräver att du anger med ctrl + shift + ange. Ett par lockiga parenteser kommer sedan att dyka upp i formelfältet.

  • array hänvisar till intervallet varifrån du vill returnera värdet.
  • (lookup_value=lookup_array) representerar ett enda tillstånd. Detta tillstånd kontrollerar om en viss letauppvärde matchar värdena i lookup_array.

Till exempel för att hitta poäng av klass A:s Coco, vars födelsedatum är 7-2-2008, kan du använda följande formel:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

Excel index match 9

Anmärkningar:

  • I den här formeln undviker vi hårdkodningsvärden, vilket gör det enkelt att få poäng med olika information genom att modifiera värdena i celler G2, G3och G4.
  • Du bör ange formeln genom att trycka på Ctrl + Skift + Enter utom i excel 365 or excel 2021, där du helt enkelt kan trycka på ange.
    Om du ständigt glömmer att använda Ctrl + Skift + Enter för att slutföra formeln och få felaktiga resultat, använd följande lite mer komplexa formel, med vilken du kan komplettera med en enkel ange nyckel:
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • Formlerna kan vara komplexa och utmanande att komma ihåg. För att förenkla sökningar med flera kriterier utan behov av manuell formelinmatning, överväg att använda Kutools för ExcelÄr Flera villkorsuppslag funktion. När du har installerat Kutools, navigera till Kutools fliken i Excel och klicka Superuppslag > Flera villkorsuppslag i Formel grupp.

    Flera villkorsuppslag

    Om du inte har installerat Kutools, klicka här för att ladda ner och få en 30-dagars fullfjädrad gratis provperiod!


INDEX och MATCH för att tillämpa en uppslagning över flera kolumner

Föreställ dig ett scenario där du har att göra med flera datakolumner. Den första kolumnen fungerar som en nyckel för att klassificera data i de andra kolumnerna. För att bestämma kategorin eller klassificeringen för en specifik post måste du göra en sökning i datakolumnerna och associera den med den relevanta nyckeln i referenskolumnen.

Till exempel, i tabellen nedan, hur kan vi matcha eleven Shawn med hans motsvarande klass med hjälp av INDEX och MATCH? Tja, du kan uppnå det med en formel, men formeln är ganska omfattande och kan vara utmanande att förstå, än mindre komma ihåg och skriva.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Det är där Kutools för Excel's Index och matcha på flera kolumner funktionen kommer väl till pass. Det förenklar processen, vilket gör det snabbt och enkelt att matcha specifika poster med deras motsvarande kategorier. För att låsa upp detta kraftfulla verktyg och enkelt matcha Shawn med sin klass ladda ner och installera Kutools for Excel-tillägget, och gör sedan så här:

  1. Välj destinationscellen där du vill visa den matchande klassen.
  2. Kutools fliken, klicka Formel Helper > Sökning och referens > Index och matcha på flera kolumner.
  3. Excel index match 11
  4. Gör så här i popup-dialogrutan:
    1. Klicka på den första Excel-indexmatchningsikon knappen bredvid Lookup_col för att välja kolumnen som innehåller nyckelinformationen du vill returnera, dvs klassnamnen. (Du kan bara välja en enda kolumn här.)
    2. Klicka på den andra Excel-indexmatchningsikon knappen bredvid Tabell_rng för att markera cellerna för att matcha värdena i de markerade Lookup_col, dvs elevens namn.
    3. Klicka på den tredje Excel-indexmatchningsikon knappen bredvid Uppslagningsvärde för att välja cellen som innehåller elevens namn som du vill matcha med deras klass, i det här fallet Shawn.
    4. Klicka OK.
    5. Excel index match 12

Resultat

Kutools har automatiskt genererat formeln, och du kommer att se Shawns klassnamn visas i målcellen omedelbart.

Notera: För att prova Index och matcha på flera kolumner funktionen behöver du Kutools för Excel installerat på din dator. Om du inte har installerat det ännu, vänta inte --- Ladda ner och installera det nu för en 30-dagars gratis provperiod utan begränsningar. Få Excel att fungera smartare idag!


INDEX och MATCH för att söka efter det första icke-blanka värdet

För att hämta det första icke-tomma värdet, ignorera fel, från en kolumn eller en rad, kan du använda en formel baserad på funktionerna INDEX och MATCH. Men om du inte vill ignorera felen från ditt sortiment, lägg till funktionen ISBLANK.

  • Hämta det första icke-tomma värdet i en kolumn eller rad och ignorera fel:
  • =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))
  • Få det första icke-tomma värdet i en kolumn eller rad inklusive fel:
  • =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

Anmärkningar:


INDEX och MATCH för att söka efter det första numeriska värdet

För att hämta det första numeriska värdet från en kolumn eller en rad, använd formeln baserad på funktionerna INDEX, MATCH och ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

Anmärkningar:


INDEX och MATCH för att söka efter MAX eller MIN associationer

Om du behöver hämta ett värde som är associerat med max- eller minimivärdet inom ett område kan du använda MAX- eller MIN-funktionen tillsammans med funktionerna INDEX och MATCH.

  • INDEX och MATCH för att hämta ett värde som är associerat med maxvärdet:
  • =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
  • INDEX och MATCH för att hämta ett värde som är associerat med minimivärdet:
  • =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
  • Det finns två argument i formlerna ovan:
    • array hänvisar till intervallet varifrån du vill returnera den relaterade informationen.
    • lookup_array representerar uppsättningen värden som ska undersökas eller sökas efter specifika kriterier, dvs. maximala eller lägsta värden.

Till exempel om du vill bestämma vem som har högst poäng, använd följande formel:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Hur denna formel fungerar:
  • MAX(C2:C11) söker efter det högsta värdet i intervallet C2: C11, vilket är 96.
  • MATCH-funktionen hittar sedan positionen för det högsta värdet i arrayen C2: C11, vilket borde vara 1.
  • Slutligen hämtar INDEX 1st värde i listan A2: A11.

Anmärkningar:

  • Om det finns fler än ett högsta eller lägsta värde, som framgår av exemplet ovan, där två elever uppnådde samma högsta poäng, kommer denna formel att returnera den första matchningen.
  • För att avgöra vem som har lägst poäng, använd följande formel:
    =INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))

Tips: Skräddarsy dina egna #N/A-felmeddelanden

När du arbetar med Excels INDEX- och MATCH-funktioner kan du stöta på #N/A-felet när det inte finns något matchande resultat. Till exempel, i tabellen nedan, när man försöker hitta poängen för en elev som heter Samantha, visas ett #N/A-fel eftersom hon inte finns i datamängden.

Excel index match 15

För att göra dina kalkylblad mer användarvänliga kan du anpassa det här felmeddelandet genom att slå in din INDEX MATCH-formel i IFNA-funktionen:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Excel index match 16

Anmärkningar:

  • Du kan anpassa dina felmeddelanden genom att ersätta "Hittades inte" med valfri text.
  • Om du vill hantera alla fel, inte bara #N/A, överväg att använda FEL funktion istället för IFNA:
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    Observera att det kanske inte är tillrådligt att undertrycka alla fel eftersom de fungerar som varningar för potentiella problem i dina formler.

Ovan är allt relevant innehåll relaterat till funktionerna INDEX och MATCH i Excel. Jag hoppas att du tycker att handledningen är till hjälp. Om du vill utforska fler Excel-tips och tricks, klicka här för att komma åt vår omfattande samling av över tusentals tutorials.