Vad du ska veta
- INDEX-funktionen kan användas ensam, men genom att bygga in MATCH-funktionen i den skapas en avancerad sökning.
- Denna kapslade funktion är mer flexibel än VLOOKUP och kan ge resultat snabbare.
Den här artikeln förklarar hur man använder funktionerna INDEX och MATCH tillsammans i alla versioner av Excel, inklusive Excel 2019 och Microsoft 365.
Vad är funktionerna INDEX och MATCH?
INDEX och MATCH är Excel-sökfunktioner. Även om de är två helt separata funktioner som kan användas på egen hand, kan de också kombineras för att skapa avancerade formler. INDEX-funktionen returnerar ett värde eller referensen till ett värde från ett visst val. Det kan till exempel användas för att hitta värdet i den andra raden i en datamängd, eller i den femte raden och tredje kolumnen. Medan INDEX mycket väl skulle kunna användas ensam, gör nestning MATCH i formeln det lite mer användbart. MATCH-funktionen söker efter ett angivet objekt i ett cellområde och returnerar sedan den relativa positionen för objektet i intervallet. Det kan till exempel användas för att bestämma att ett specifikt namn är det tredje objektet i en namnlista.
INDEX och MATCH Syntax och argument
Så här behöver båda funktionerna skrivas för att Excel ska förstå dem:
= INDEX(array, rad_nummer, [column_num])
- array är det cellområde som formeln kommer att använda. Det kan vara en eller flera rader och kolumner, till exempel A1: D5. Det krävs.
- rad_nummer är raden i matrisen från vilken ett värde ska returneras, till exempel 2 eller 18. Det krävs om inte kolumn_nummer är närvarande.
- kolumn_nummer är kolumnen i matrisen från vilken ett värde ska returneras, till exempel 1 eller 9. Det är valfritt.
= MATCH(lookup_value, lookup_array, [match_type])
- lookup_value är det värde du vill matcha i lookup_array. Det kan vara ett nummer, text eller logiskt värde som skrivs manuellt eller hänvisas till via en cellreferens. Detta krävs.
- lookup_array är cellintervallet att titta igenom. Det kan vara en enda rad eller en enda kolumn, till exempel A2: D2 eller G1: G45. Detta krävs.
- match_type kan vara -1, 0, eller 1. Det specificerar hur lookup_value matchas med värdena i lookup_array (se nedan). 1 är standardvärdet om detta argument utelämnas.
| Vilken matchningstyp som ska användas | |||
|---|---|---|---|
| Matchningstyp | Vad den gör | Regel | Exempel |
| 1 | Hitta det största värdet som är mindre än eller lika med lookup_value. |
De lookup_array värden måste placeras i stigande ordning (t.ex. -2, -1, 0, 1, 2; eller AZ ;, eller FALSE, TRUE. |
lookup_value är 25 men det saknas lookup_array, så positionen för nästa minsta nummer, som 22, returneras istället. |
| 0 | Hitta det första värdet som är exakt lika med lookup_value. |
De lookup_array värden kan vara i valfri ordning. |
lookup_value är 25, så det returnerar positionen 25. |
| -1 | Hitta det minsta värdet som är större eller lika med lookup_value. |
De lookup_array värden måste placeras i fallande ordning (t.ex. 2, 1, 0, -1, -2). |
lookup_value är 25 men det saknas lookup_array, så positionen för nästa största nummer, som 34, returneras istället. |
Använda sig av 1 eller -1 för tider när du behöver köra en ungefärlig uppslagning längs en skala, som när du hanterar siffror och när approximationer är okej. Men kom ihåg att om du inte anger match_type, 1 kommer att vara standard, vilket kan vrida resultaten om du verkligen vill ha en exakt matchning.
Exempel på INDEX- och MATCH-formler
Innan vi tittar på hur man kombinerar INDEX och MATCH till en formel, måste vi förstå hur dessa funktioner fungerar på egen hand.
INDEX Exempel
= INDEX (A1: B2,2,2)
= INDEX (A1: B1,1)
= INDEX (2: 2,1)
= INDEX (B1: B2,1)
I det här första exemplet finns det fyra INDEX-formler som vi kan använda för att få olika värden:
- = INDEX (A1: B2,2,2) tittar genom A1: B2 för att hitta värdet i andra kolumnen och andra raden, vilket är Stacy.
- = INDEX (A1: B1,1) tittar igenom A1: B1 för att hitta värdet i den första kolumnen, vilket är Jon.
- = INDEX (2: 2,1) tittar igenom allt i andra raden för att hitta värdet i den första kolumnen, vilket är Tim.
- = INDEX (B1: B2,1) tittar igenom B1: B2 för att hitta värdet i första raden, vilket är Amy.
MATCH Exempel
= MATCH («Stacy», A2: D2,0)
= MATCH (14, D1: D2)
= MATCH (14, D1: D2, -1)
= MATCH (13, A1: D1,0)
Här är fyra enkla exempel på MATCH-funktionen:
- = MATCH («Stacy», A2: D2,0) söker efter Stacy innom räckhåll A2: D2 och återvänder 3 som resultat.
- = MATCH (14, D1: D2) söker efter 14 innom räckhåll D1: D2, men eftersom det inte finns i tabellen hittar MATCH nästa största värdet som är mindre än eller lika med 14, vilket i detta fall är 13, som är på plats 1 av lookup_array.
- = MATCH (14, D1: D2, -1) är identisk med formeln ovanför, men eftersom matrisen inte är i fallande ordning som -1 kräver, vi får ett fel.
- = MATCH (13, A1: D1,0) letar efter 13 i den första raden på arket, som returnerar 4 eftersom det är det fjärde objektet i denna matris.
INDEX-MATCH Exempel
Här är två exempel där vi kan kombinera INDEX och MATCH i en formel:
Hitta cellreferens i tabell
= INDEX (B2: B5, MATCH (F1, A2: A5))
Detta exempel kapslar MATCH-formeln i INDEX-formeln. Målet är att identifiera artikelns färg med artikelnumret. Om du tittar på bilden kan du se i «Separerade» rader hur formlerna skulle skrivas på egen hand, men eftersom vi häckar dem är det här som händer:
- MATCH (F1, A2: A5) letar efter F1 värde (8795) i datamängden A2: A5. Om vi räknar ner kolumnen kan vi se att den är 2, så det är vad MATCH-funktionen just har kommit fram till.
- INDEX-matrisen är B2: B5 eftersom vi i slutändan letar efter värdet i den kolumnen.
- INDEX-funktionen kan nu skrivas om så här sedan dess 2 är vad MATCH hittade: INDEX (B2: B5, 2, [column_num]).
- Eftersom kolumn_nummer är valfritt kan vi ta bort det som ska vara kvar med detta: INDEX (B2: B5,2).
- Så nu är det som en vanlig INDEX-formel där vi hittar värdet på det andra objektet i B2: B5, vilket är röd.
Sökning efter rad- och kolumnrubriker
= INDEX (B2: E13, MATCH (G1, A2: A13,0), MATCH (G2, B1: E1,0))
I det här exemplet med MATCH och INDEX gör vi en tvåvägssökning. Tanken är att se hur mycket pengar vi tjänade på Grön artiklar i Maj. Detta liknar verkligen exemplet ovan, men en extra MATCH-formel är kapslad i INDEX.
- MATCH (G1, A2: A13,0) är den första artikeln som löses i denna formel. Det letar efter G1 (ordet «maj») i A2: A13 för att få ett visst värde. Vi ser det inte här, men det är det 5.
- MATCH (G2, B1: E1,0) är den andra MATCH-formeln, och den liknar den första men letar istället efter G2 (ordet «grön») i kolumnrubrikerna vid B1: E1. Den här löser sig att 3.
- Vi kan nu skriva om INDEX-formeln så här för att visualisera vad som händer: = INDEX (B2: E13,5,3). Detta ser i hela tabellen, B2: E13, för den femte raden och tredje kolumnen, som returnerar 180 dollar.
MATCH- och INDEX-regler
Det finns flera saker att tänka på när du skriver formler med dessa funktioner:
- MATCH är inte skiftlägeskänslig, så stora och små bokstäver behandlas på samma sätt när de matchar textvärden.
- MATCH återvänder # Ej tillämpligt av flera skäl: om match_type är 0 och lookup_value finns inte om match_type är -1 och lookup_array är inte i fallande ordning, om match_type är 1 och lookup_array är inte i stigande ordning, och om lookup_array är inte en enda rad eller kolumn.
- Du kan använda ett jokertecken i lookup_value argument om match_type är 0 och lookup_value är en textsträng. Ett frågetecken matchar varje enskilt tecken och en asterisk matchar varje sekvens av tecken (t.ex. = MATCH («Jo *», 1: 1,0)). För att använda MATCH för att hitta ett faktiskt frågetecken eller asterisk, skriv ~ först.
- INDEX returnerar #REF! om rad_nummer och kolumn_nummer peka inte på en cell i matrisen.
Relaterade Excel-funktioner
MATCH-funktionen liknar LOOKUP, men MATCH returnerar placera i stället för själva artikeln. VLOOKUP är en annan uppslagsfunktion som du kan använda i Excel, men till skillnad från MATCH som kräver INDEX för avancerade uppslag behöver VLOOKUP-formler bara den ena funktionen.