Denna handledning lär dig hur du använder INDEX & MATCH -kombinationen för att utföra sökningar i Excel och Google Sheets.
INDEX & MATCH, The Perfect Pair
Låt oss titta närmare på några av de sätt du kan kombinera INDEX- och MATCH -funktionerna. MATCH -funktionen är utformad för att returnera den relativa positionen för ett objekt i en array, medan INDEX -funktionen kan hämta ett objekt från en array som ges en specifik position. Denna synergi mellan de två gör att de kan utföra nästan alla typer av sökningar du kan behöva.
INDEX / MATCH -kombinationen har historiskt sett använts som en ersättning för VLOOKUP -funktionen. En av de främsta anledningarna är möjligheten att göra en vänsterblick (se nästa avsnitt).
Obs: den nya XLOOKUP-funktionen kan nu utföra vänsterutseende.
Slå upp till vänster
Låt oss använda denna tabell med basketstatistik:
Vi vill hitta Bob’s Player #. Eftersom Player # är till vänster om namnkolumnen kan vi inte använda en VLOOKUP.
Istället kan vi göra en grundläggande MATCH -begäran för att beräkna Bobs rad
= MATCH (H2, B2: B5, 0)
Detta kommer att leta efter en exakt matchning av ordet "Bob", och så skulle vår funktion returnera siffran 2, eftersom "Bob" är i 2nd placera.
Därefter kan vi använda INDEX -funktionen för att returnera Player #, motsvarande en rad. För nu, låt oss bara ange "2" manuellt i funktionen:
= INDEX (A2: A5, 2)
Här kommer INDEX att referera till A3, eftersom det är 2nd cell inom A2: A5 -intervallet och returnera resultatet av 42. För vårt övergripande mål kan vi sedan kombinera dessa två till:
= INDEX (A2: A5, MATCH (H2, B2: B5, 0))
Fördelen här är att vi kunde returnera ett resultat från en kolumn till vänster om där vi letade.
Tvådimensionell sökning
Låt oss titta på vårt bord från tidigare:
Den här gången vill vi dock hämta en specifik statistik. Vi har uppmanat att vi vill söka efter Rebounds i cell H1. Istället för att behöva skriva flera IF -uttalanden för att avgöra vilken kolumn du vill få resultatet från, kan du använda en MATCH -funktion igen. Med INDEX -funktionen kan du ange radvärdet och kolumnvärdet. Vi kommer att lägga till en annan MATCH -funktion här för att avgöra vilken kolumn vi vill ha. Det kommer att se ut
= MATCH (H1, A1: E1, 0)
Vår cell i H1 är en rullgardinsmeny som låter oss välja vilken kategori vi vill söka efter, och sedan bestämmer vår MATCH vilken kolumn i tabellen som tillhör. Låt oss ansluta den här nya biten till vår tidigare formel. Observera att vi måste justera det första argumentet för att vara två dimensioner, eftersom vi inte längre bara vill ha ett resultat från kolumn A.
= INDEX (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
I vårt exempel vill vi hitta Rebounds för Charlie. Vår formel kommer att utvärdera detta så här:
= INDEX (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("Rebounds", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Vi har nu skapat en flexibel installation som gör att användaren kan hämta vilket värde de vill ha från vår tabell utan att behöva skriva flera formler eller förgrena IF -uttalanden.
Flera sektioner
Det används inte ofta, men INDEX har ett femte argument som kan ges för att avgöra vilket område inom argument ett att använda. Det betyder att vi behöver ett sätt att föra in flera områden i det första argumentet. Du kan göra detta genom att använda en extra uppsättning parenteser. Det här exemplet illustrerar hur du kan hämta resultat från olika tabeller i ett kalkylblad med INDEX.
Här är layouten vi kommer att använda. Vi har statistik för tre olika kvartspel.
I cellerna H1: H3 har vi skapat listrutor för datavalidering för våra olika val. Rullgardinsmenyn för kvartalet kommer från J2: J4. Vi kommer att använda detta för ett annat MATCH -uttalande för att avgöra vilket område som ska användas. Vår formel i H4 kommer att se ut så här:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Vi har redan diskuterat hur de två inre MATCH -funktionerna fungerar, så låt oss fokusera på de första och sista argumenten:
= INDEX ((A3: E6, A10: E13, A17: E20), …, MATCH (H3, J2: J4, 0))
Vi har gett INDEX -funktionen flera matriser i det första argumentet genom att omsluta dem alla inom parentes. Det andra sättet du kan göra detta är genom att använda formler - Definiera namn. Du kan definiera ett namn som heter "MyTables" med en definition av
= INDEX (MyTable, MATCH (H2, Table1347 [Namn], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Låt oss gå tillbaka till hela uttalandet. Våra olika MATCH -funktioner kommer att berätta för INDEX -funktionen exakt var de ska leta. Först bestämmer vi att "Charlie" är 3: anrd rad. Därefter vill vi ha ”Rebounds”, vilket är 4: anth kolumn. Slutligen har vi bestämt att vi vill ha resultatet från 2nd tabell. Formeln kommer att utvärdera genom detta så här:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEX ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEX (A10: E13, 3, 4) = D13 = 14
Som vi nämnde i början av detta exempel är du begränsad till att ha tabellerna i samma kalkylblad. Om du kan skriva ut rätt sätt att berätta för din INDEX vilken rad, kolumn och/eller område du vill hämta data från, kommer INDEX att tjäna dig mycket bra.
Google Kalkylark -INDEX & MATCH
Alla ovanstående exempel fungerar exakt likadant i Google Kalkylark som i Excel.
