INDEX & MATCH Funktionskombination i Excel (10 enkla exempel)

Excel har många funktioner - cirka 450+ av dem.

Och många av dessa är helt enkelt fantastiska. Mängden arbete du kan få gjort med några formler förvånar mig fortfarande (även efter att ha använt Excel i 10+ år).

Och bland alla dessa fantastiska funktioner sticker kombinationen INDEX MATCH -funktioner ut.

Jag är ett stort fan av INDEX MATCH combo och jag har gjort det ganska tydligt många gånger.

Jag skrev till och med en artikel om Index Match Vs VLOOKUP som väckte lite debatt (du kan kolla kommentarfältet för lite fyrverkeri).

Och idag skriver jag den här artikeln enbart fokuserad på Index Match för att visa dig några enkla och avancerade scenarier där du kan använda denna kraftfulla formelkombination och få jobbet gjort.

Notera: Det finns andra uppslagsformler i Excel - som VLOOKUP och HLOOKUP och dessa är bra. Många tycker att VLOOKUP är lättare att använda (och det är också sant). Jag tror att INDEX MATCH är ett bättre alternativ i många fall. Men eftersom människor tycker att det är svårt, blir det mindre använt. Så jag försöker förenkla det med denna handledning.

Nu innan jag visar dig hur kombinationen av INDEX MATCH förändrar analytiker och datavetenskapares värld, låt mig först presentera dig för de enskilda delarna - INDEX och MATCH -funktioner.

INDEX-funktion: Hittar värdet baserat på koordinater

Det enklaste sättet att förstå hur indexfunktionen fungerar är att tänka på det som en GPS -satellit.

Så fort du berättar för satelliten latitud- och longitudkoordinaterna kommer den att veta exakt var du ska gå och hitta den platsen.

Så trots att det har ett häpnadsväckande antal latlånga kombinationer, skulle satelliten veta exakt var den ska leta.

Jag sökte snabbt efter min arbetsplats och det här är vad jag fick.

Hur som helst, nog med geografi.

Precis som en satellit behöver latitud- och longitudkoordinater, skulle INDEX -funktionen i Excel behöva rad- och kolumnnumret för att veta vilken cell du syftar på.

Och det är Excel INDEX-funktionen i ett nötskal.

Så låt mig definiera det i enkla ord för dig.

INDEX -funktionen använder radnumret och kolumnnumret för att hitta en cell i det angivna intervallet och returnera värdet i den.

I sig självt är INDEX en mycket enkel funktion, utan nytta. När allt kommer omkring är det troligt att du inte känner till rad- och kolumnnumren.

Men…

Det faktum att du kan använda den med andra funktioner (ledtråd: MATCH) som kan hitta radnumret och kolumnnumret gör INDEX till en extremt kraftfull Excel -funktion.

Nedan är syntaxen för INDEX -funktionen:

= INDEX (array, row_num, [col_num]) = INDEX (array, row_num, [col_num], [area_num])

• array - a cellintervall eller en arraykonstant.
• radnummer - radnumret från vilket värdet ska hämtas.
• [col_num] - kolumnnumret från vilket värdet ska hämtas. Även om detta är ett valfritt argument, men om radnummer inte tillhandahålls, måste det anges.
• [area_num] - (Valfritt) Om array -argument består av flera intervall, skulle detta nummer användas för att välja referens från alla intervall.

INDEX -funktionen har 2 syntaxer (bara FYI).

Den första används i de flesta fall. Den andra används endast i avancerade fall (som att göra en trevägsuppslagning) som vi kommer att täcka i ett av exemplen senare i denna handledning.

Men om du är ny på den här funktionen, kom bara ihåg den första syntaxen.

Nedan finns en video som förklarar hur du använder INDEX -funktionen

MATCH -funktion: Hittar positionen baserad på ett uppslagsvärde

För att gå tillbaka till mitt tidigare exempel på longitud och latitud, är MATCH funktionen som kan hitta dessa positioner (i Excel -kalkylbladets värld).

I ett enkelt språk kan Excel MATCH -funktionen hitta positionen för en cell i ett område.

Och på vilken grund skulle den hitta en cellposition?

Baserat på uppslagsvärdet.

Om du till exempel har en lista som visas nedan och du vill hitta positionen för namnet "Mark" i den, kan du använda MATCH -funktionen.

Funktionen returnerar 3, eftersom det är cellens position med namnet Mark i.

MATCH -funktionen börjar leta uppifrån och ner efter uppslagsvärdet (vilket är "Mark") i det angivna intervallet (vilket är A1: A9 i detta exempel). Så snart det hittar namnet returnerar det positionen i det specifika intervallet.

Nedan visas syntaxen för funktionen MATCH i Excel.

= MATCH (lookup_value, lookup_array, [match_type])

• lookup_value - Värdet som du letar efter en matchning i lookup_array.
• lookup_array - Cellerna där du söker efter lookup_value.
• [match_type] - (Valfritt) Detta anger hur excel ska se ut för ett matchande värde. Det kan ta tre värden -1, 0 eller 1.

Förstå matchningstypargument i MATCH -funktionen

Det finns ytterligare en sak du behöver veta om MATCH -funktionen, och den handlar om hur den går igenom data och hittar cellens position.

MATCH -funktionens tredje argument kan vara 0, 1 eller -1.

Nedan följer en förklaring av hur dessa argument fungerar:

• 0 - detta kommer att leta efter en exakt matchning av värdet. Om en exakt matchning hittas returnerar MATCH -funktionen cellpositionen. Annars kommer det att returnera ett fel.
• 1 - detta hittar det största värdet som är mindre än eller lika med uppslagsvärdet. För att detta ska fungera måste ditt dataintervall sorteras i stigande ordning.
• -1 - detta hittar det minsta värdet som är större än eller lika med uppslagsvärdet. För att detta ska fungera måste ditt dataintervall sorteras i fallande ordning.

Nedan finns en video som förklarar hur du använder MATCH -funktionen (tillsammans med matchningstypargumentet)

För att sammanfatta och uttrycka det i enkla ord:

• INDEX behöver cellpositionen (rad och kolumnnummer) och ger cellvärdet.
• MATCH hittar positionen med hjälp av ett uppslagsvärde.

Låt oss kombinera dem för att skapa ett kraftverk (INDEX + MATCH)

Nu när du har en grundläggande förståelse för hur INDEX och MATCH -funktioner fungerar individuellt, låt oss kombinera dessa två och lära oss om alla underbara saker det kan göra.

För att förstå detta bättre har jag några exempel som använder INDEX MATCH -kombinationen.

Jag börjar med ett enkelt exempel och visar dig också några avancerade användningsfall.

Klicka här för att ladda ner exempelfilen

Exempel 1: En enkel uppslagning med hjälp av kombinationen INDEX MATCH

Låt oss göra en enkel sökning med INDEX/MATCH.

Nedan är en tabell där jag har betygen för tio elever.

Från den här tabellen vill jag hitta märkena för Jim.

Nedan är formeln som enkelt kan göra detta:

= INDEX ($ A $ 2: $ B $ 11, MATCH ("Jim", $ A $ 2: $ A $ 11,0), 2)

Om du nu tror att detta enkelt kan göras med en VLOOKUP -funktion har du rätt! Detta är inte den bästa användningen av INDEX MATCH awesomeness. Trots att jag är ett fan av INDEX MATCH är det lite svårare än VLOOKUP. Om du bara vill hämta data från en kolumn till höger rekommenderar jag att du använder VLOOKUP.

Anledningen till att jag har visat detta exempel, som också enkelt kan göras med VLOOKUP är att visa dig hur INDEX MATCH fungerar i en enkel inställning.

Låt mig nu visa en fördel med INDEX MATCH.

Anta att du har samma data, men istället för att ha den i kolumner har du den i rader (som visas nedan).

Vet du vad, du kan fortfarande använda kombinationen INDEX MATCH för att få Jims betyg.

Nedan är formeln som ger dig resultatet:

= INDEX ($ B $ 1: $ K $ 2,2, MATCH ("Jim", $ B $ 1: $ K $ 1,0))

Observera att du måste ändra intervallet och byta rad/kolumndelar för att få denna formel att fungera även för horisontella data.

Detta kan inte göras med VLOOKUP, men du kan fortfarande göra det enkelt med HLOOKUP.

INDEX MATCH -kombinationen kan enkelt hantera horisontella såväl som vertikala data.

Klicka här för att ladda ner exempelfilen

Exempel 2: Slå upp till vänster

Det är vanligare än du tror.

Många gånger kan du behöva hämta data från en kolumn till vänster om kolumnen som har uppslagsvärdet.

Något som visas nedan:

För att ta reda på Michaels försäljning måste du leta till vänster.

Om du tänker på VLOOKUP, låt mig sluta där.

VLOOKUP är inte gjord för att leta efter och hämta värdena till vänster.

Kan du fortfarande göra det med VLOOKUP?

Jo det kan du!

Men det kan bli en lång och ful formel.

Så om du vill göra en sökning och hämta data från kolumnerna till vänster, är det bättre att använda kombinationen INDEX MATCH.

Nedan är formeln som får Michaels försäljningsnummer:

= INDEX ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)

En annan punkt här för INDEX MATCH. VLOOKUP kan hämta data endast från kolumnerna till höger om kolumnen som har uppslagsvärdet.

Exempel 3: Tvåvägsuppslag

Hittills har vi sett exemplen där vi ville hämta data från kolumnen intill kolumnen som har uppslagsvärdet.

Men i verkligheten sträcker sig data ofta genom flera kolumner.

INDEX MATCH kan enkelt hantera en tvåvägsuppslagning.

Nedan finns en dataset med elevens betyg i tre olika ämnen.

Om du snabbt vill hämta betyg för en elev i alla tre ämnena kan du göra det med INDEX MATCH.

Nedanstående formel ger dig märkena för Jim för alla tre ämnena (kopiera och klistra in i en cell och dra för att fylla andra celler eller kopiera och klistra in på andra celler).

= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))

Låt mig snabbt också förklara denna formel.

INDEX -formeln använder B2: D11 som intervall.

Den första MATCH använder namnet (Jim i cell F3) och hämtar positionen i namnkolumnen (A2: A11). Detta blir det radnummer som data måste hämtas från.

Den andra MATCH -formeln använder ämnesnamnet (i cell G2) för att få positionen för det specifika ämnesnamnet i B1: D1. Till exempel är matematik 1, fysik 2 och kemi 3.

Eftersom dessa MATCH -positioner matas in i INDEX -funktionen returnerar poängen baserat på studentens namn och ämnesnamn.

Denna formel är dynamisk, vilket innebär att om du ändrar elevens namn eller ämnesnamn, skulle det fortfarande fungera och hämta rätt data.

En bra sak med att använda INDEX/MATCH är att även om du byter ämnesnamn kommer det att fortsätta att ge dig rätt resultat.

Exempel 4: Sökvärde från flera kolumner/kriterier

Anta att du har en dataset som visas nedan och du vill hämta märkena för "Mark Long".

Eftersom data finns i två kolumner kan jag inte leta efter Mark och få data.

Om jag gör det på det sättet kommer jag att få betygsdata för Mark Frost och inte Mark Long (eftersom MATCH -funktionen ger mig resultatet för MARKET det uppfyller).

Ett sätt att göra detta är att skapa en hjälparkolumn och kombinera namnen. När du väl har hjälpkolumnen kan du använda VLOOKUP och få betygsdata.

Om du är intresserad av att lära dig hur du gör detta, läs den här självstudien om hur du använder VLOOKUP med flera kriterier.

Men med kombinationen INDEX/MATCH behöver du inte en hjälparkolumn. Du kan skapa en formel som hanterar flera kriterier i själva formeln.

Nedanstående formel ger resultatet.

= INDEX ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))

Låt mig snabbt förklara vad denna formel gör.

MATCH -delen av formeln kombinerar uppslagsvärdet (Mark och Long) samt hela uppslagningsarrayen. När $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 används som uppslagsmatris, kontrollerar det faktiskt uppslagsvärdet mot den kombinerade strängen med för- och efternamn (åtskilda av rörsymbolen).

Detta säkerställer att du får rätt resultat utan att använda några hjälpkolumner.

Du kan göra den här typen av sökningar (där det finns flera kolumner/kriterier) med VLOOKUP också, men du måste använda en hjälparkolumn. INDEX MATCH -kombination gör det lätt att göra detta utan hjälparkolumner.

Exempel 5: Hämta värden från hela raden/kolumnen

I exemplen ovan har vi använt INDEX -funktionen för att få värde från en specifik cell. Du anger rad- och kolumnnumret, och det returnerar värdet i den specifika cellen.

Men du kan göra mer.

Du kan också använda INDEX -funktionen för att hämta värdena från en hel rad eller kolumn.

Och hur kan detta vara användbart frågar du!

Antag att du vill veta Jims totala poäng i alla tre ämnena.

Du kan använda INDEX -funktionen för att först få alla märken från Jim och sedan använda SUM -funktionen för att få en summa.

Låt oss se hur du gör detta.

Nedan har jag poängen för alla elever i tre ämnen.

Nedanstående formel ger mig den totala poängen för Jim i alla tre ämnena.

= SUMMA (INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))

Låt mig förklara hur denna formel fungerar.

Tricket här är att använda 0 som kolumnnummer.

När du använder 0 som kolumnnummer i INDEX -funktionen returnerar det alla radvärden. På samma sätt returnerar du alla värden i kolumnen om du använder 0 som radnummer.

Så nedanstående del av formeln returnerar en rad värden - {97, 70, 73}

INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)

Om du bara anger den ovanstående formeln i en cell i Excel och trycker på enter, ser du ett #VÄRDE! fel. Detta beror på att det inte returnerar ett enda värde, utan en rad värden.

Men oroa dig inte, värdena finns fortfarande kvar. Du kan kontrollera detta genom att välja formeln och trycka på F9 -tangenten. Det visar resultatet av formeln som i detta fall är en array med tre värden - {97, 70, 73}

Om du nu packar in denna INDEX -formel i SUMM -funktionen, kommer den att ge dig summan av alla poäng som Jim fick.

Du kan också använda samma för att få de högsta, lägsta och genomsnittliga betygen för Jim.

Precis som vi har gjort detta för en student kan du också göra detta för ett ämne. Om du till exempel vill ha medelpoängen i ett ämne kan du behålla radnumret som 0 i INDEX -formeln och det kommer att ge dig alla kolumnvärden för det ämnet.

Klicka här för att ladda ner exempelfilen

Exempel 6: Hitta elevens betyg (ungefärlig matchningsteknik)

Hittills har vi använt MATCH -formeln för att få exakt matchning av uppslagsvärdet.

Men du kan också använda den för att göra en ungefärlig matchning.

Vad fan är ungefärlig matchning?

Låt mig förklara.

När du letar efter saker som namn eller id söker du efter en exakt matchning. Men ibland måste du veta i vilket intervall dina sökvärden ligger. Detta är vanligtvis fallet med siffror.

Till exempel, som klasslärare, kanske du vill veta vad betyget för varje elev i ett ämne är, och betyget bestäms utifrån poängen.

Nedan följer ett exempel, där jag vill ha betyget för alla elever och betyget bestäms utifrån tabellen till höger.

Så om en elev får mindre än 33 är betyget F och om han/hon får mindre än 50 men mer än 33 är det E, och så vidare.

Nedan är formeln som gör detta.

= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)

Låt mig förklara hur denna formel fungerar.

I MATCH -funktionen har vi använt 1 som [match_type] -argumentet. Detta argument returnerar det största värdet som är mindre än eller lika med uppslagsvärdet.

Det betyder att MATCH -formeln går igenom märkesintervallet, och så snart den hittar ett märkesintervall som är lika med eller mindre än uppslagsteckenvärdet, kommer det att stanna där och återföra sin position.

Så om uppslagningsvärdet är 20 skulle MATCH -funktionen returnera 1 och om det är 85 skulle det returnera 5.

Och INDEX -funktionen använder detta positionsvärde för att få betyget.

VIKTIGT: För att detta ska fungera måste dina data sorteras i stigande ordning. Om det inte är det kan du få fel resultat.

Observera att ovanstående också kan göras med hjälp av nedanstående VLOOKUP -formel:

= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, TRUE)

Men MATCH -funktionen kan gå ett steg längre när det gäller ungefärlig matchning.

Du kan också ha en fallande data och använda kombinationen INDEX MATCH för att hitta resultatet. Om jag till exempel ändrar betygstabellens ordning (som visas nedan) kan jag fortfarande hitta betygen för eleverna.

För att göra detta behöver jag bara ändra [match_type] -argumentet till -1.

Nedan är formeln som jag har använt:

= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)

VLOOKUP kan också göra en ungefärlig matchning men bara när data sorteras i stigande ordning (men det fungerar inte om data sorteras i fallande ordning).

Exempel 7: Fallkänsliga sökningar

Hittills har alla de sökningar som vi gjort gjort inte varit skiftlägeskänsliga.

Det betyder att det inte spelade någon roll om sökvärdet var Jim eller JIM eller jim. Du får samma resultat.

Men vad händer om du vill att sökningen ska vara skiftlägeskänslig.

Detta är vanligtvis fallet när du har stora datamängder och möjlighet till upprepning eller distinkta namn/id (med den enda skillnaden)

Anta till exempel att jag har följande datauppsättning av elever där det finns två elever med namnet Jim (den enda skillnaden är att en är inmatad som Jim och en annan som jim).

Observera att det finns två elever med samma namn - Jim (cell A2 och A5).

Eftersom en normal sökning inte skulle fungera måste du göra en skiftlägeskänslig sökning.

Nedan är formeln som ger dig rätt resultat. Eftersom detta är en matrisformel måste du använda Ctrl + Skift + Retur.

= INDEX ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)

Låt mig förklara hur denna formel fungerar.

EXAKT -funktionen söker efter en exakt matchning av uppslagsvärdet (vilket är "jim" i det här fallet). Det går igenom alla namn och returnerar FALSKT om det inte är en matchning och SANT om det är en matchning.

Så utsignalen från EXAKT -funktionen i detta exempel är - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Observera att det bara finns en SANN, det är då EXAKT -funktionen hittade en perfekt matchning.

MATCH -funktionen hittar sedan positionen TRUE i matrisen som returneras av EXACT -funktionen, vilket är 4 i detta exempel.

När vi väl har positionen använder INDEX -funktionen den för att hitta märkena.

Exempel 8: Hitta den närmaste matchningen

Låt oss bli lite avancerade nu.

Anta att du har en datamängd där du vill hitta den person som har arbetserfarenheten närmast erforderlig erfarenhet (nämns i cell D2).

Även om uppslagsformler inte är gjorda för att göra detta, kan du kombinera det med andra funktioner (som MIN och ABS) för att få detta gjort.

Nedan finns formeln som hittar personen med erfarenheten närmast den som krävs och returnerar personens namn. Observera att upplevelsen måste vara närmast (vilket kan vara antingen mindre eller mer).

= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))

Eftersom detta är en matrisformel måste du använda Ctrl + Skift + Retur.

Tricket i den här formeln är att ändra uppslagsvärdet och uppslagningsarrayen för att hitta den minsta erfarenhetsskillnaden i erforderliga och faktiska värden.

Innan jag förklarar formeln, låt oss förstå hur du skulle göra det manuellt.

Du kommer att gå igenom varje cell i kolumn B och hitta skillnaden i erfarenheten mellan vad som krävs och det som en person har. När du har alla skillnader hittar du den som är minst och hämtar namnet på den personen.

Detta är exakt vad vi gör med denna formel.

Låt mig förklara.

Sökvärdet i MATCH-formeln är MIN (ABS (D2-B2: B15)).

Denna del ger dig den minsta skillnaden mellan den givna erfarenheten (som är 2,5 år) och alla andra erfarenheter. I det här exemplet returnerar det 0,3

Observera att jag har använt ABS för att se till att jag letar efter det närmaste (vilket kan vara mer eller mindre än den givna erfarenheten).

Nu blir detta minimivärde vårt uppslagsvärde.

Lookup-arrayen i MATCH-funktionen är ABS (D2- $ B $ 2: $ B $ 15).

Detta ger oss en rad siffror från vilka 2,5 (erforderlig erfarenhet) har subtraherats.

Så nu har vi ett uppslagsvärde (0,3) och en uppslagsmatris ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

MATCH -funktionen hittar positionen 0,3 i denna array, vilket också är positionen för personens namn som har den närmaste erfarenheten.

Detta positionsnummer används sedan av INDEX -funktionen för att returnera personens namn.

Relaterad läsning: Hitta den närmaste matchningen i Excel (exempel med hjälp av uppslagsformler)

Klicka här för att ladda ner exempelfilen

Exempel 9: Använd INDEX MATCH med jokertecken

Om du vill slå upp ett värde när det är en partiell matchning måste du använda jokertecken.

Till exempel nedan är en datamängd med företagsnamn och deras börsvärde och du vill få marknadsvärdet. data för de tre företagen till höger.

Eftersom det inte är exakta matchningar kan du inte göra en vanlig sökning i det här fallet.

Men du kan fortfarande få rätt data genom att använda en asterisk (*), som är ett jokertecken.

Nedan är formeln som ger dig data genom att matcha företagsnamnen från huvudkolumnen och hämta marknadsvärdet för den.

= INDEX ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)

Låt mig förklara hur denna formel fungerar.

Eftersom det inte finns någon exakt matchning av uppslagsvärden har jag använt D2 & ”*” som uppslagsvärde i MATCH -funktionen.

En asterisk är ett jokertecken som representerar valfritt antal tecken. Det betyder att Apple* i formeln skulle betyda alla textsträngar som börjar med ordet Apple och kan ha valfritt antal tecken efter det.

Så när Äpple* används som uppslagsvärde och MATCH -formeln letar efter det i kolumn A, returnerar det positionen "Apple Inc.", eftersom det börjar med ordet Apple.

Du kan också använda jokertecken för att hitta textsträngar där uppslagsvärdet ligger mellan. Om du till exempel använder * Apple * som uppslagsvärde, kommer det att hitta någon sträng som har ordet apple var som helst i den.

Obs: Den här tekniken fungerar bra när du bara har en förekomst av matchning. Men om du har flera förekomster av matchning (till exempel Apple Inc och Apple Corporation, då skulle MATCH -funktionen endast returnera positionen för den första matchande instansen.

Exempel 10: Trevägsuppslag

Detta är en avancerad användning av INDEX MATCH, men jag kommer fortfarande att täcka det för att visa kraften i denna kombination.

Kom ihåg att jag sa att INDEX -funktionen har två syntaxer:

= INDEX (array, row_num, [col_num]) = INDEX (array, row_num, [col_num], [area_num])

Hittills i alla våra exempel har vi bara använt det första.

Men för en trevägsuppslagning måste du använda den andra syntaxen.

Låt mig först förklara vad ett trevägsuttryck betyder.

I en tvåvägssökning använder vi INDEX MATCH-formeln för att få betyg när vi har elevens namn och ämnesnamnet. Till exempel, att hämta märkena till Jim i matematik är en tvåvägsuppslagning.

En trevägs look skulle lägga till en annan dimension till den. Anta till exempel att du har en datauppsättning som visas nedan och du vill veta poängen för Jim i matematik vid halvtentamen, då skulle detta vara trevägsuppslagning.

Nedan är formeln som ger resultatet.

= INDEX (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Unit Test", 1, IF (G $ 3 = "Mid Term", 2,3))))

Ovanstående formel kontrollerade tre saker - studentens namn, ämnet och tentamen. När det har hittat rätt värde returnerar det det i cellen.

Låt mig förklara hur denna formel fungerar genom att dela upp formeln i delar.

• array - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): I stället för att använda en enda array, i det här fallet, har jag använt tre matriser inom parentes.
• row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): MATCH -funktionen används för att hitta positionen för elevens namn i cell $ F $ 5 i listan med elevens namn.
• col_num - MATCH (G $ 4, $ B $ 2: $ D $ 2,0): MATCH -funktionen används för att hitta positionen för ämnesnamnet i cell $ B $ 2 i listan över ämnesnamn.
• [area_num] - IF (G $ 3 = ”Unit Test”, 1, IF (G $ 3 = ”Mid Term”, 2,3)): Områdesnummervärdet berättar för INDEX -funktionen vilken av de tre matriserna som ska användas för att hämta värdet. Om undersökningen är Unit Term, skulle IF -funktionen returnera 1 och INDEX -funktionen skulle använda den första matrisen för att hämta värdet. Om tentamen är Halvtid skulle IF-formeln returnera 2, annars kommer den att returnera 3.

Detta är ett avancerat exempel på att använda INDEX MATCH, och det är osannolikt att du hittar en situation när du måste använda detta. Men det är fortfarande bra att veta vad Excel -formler kan göra.

Klicka här för att ladda ner exempelfilen

Varför är INDEX/MATCH bättre än VLOOKUP?

Eller är det?

Ja, det är det - i de flesta fall.

Jag kommer att presentera mitt ärende om ett tag.

Men innan jag gör det, låt mig säga detta - VLOOKUP är en extremt användbar funktion och jag älskar den. Det kan göra många saker i Excel och jag använder det då och då själv. Med det sagt betyder det inte att det inte kan finnas något bättre, och INDEX/MATCH (med mer flexibilitet och funktioner) är bättre.

Så om du vill göra en grundläggande sökning är det bättre att använda VLOOKUP.

INDEX/MATCH är VLOOKUP på steroider. Och när du väl har lärt dig INDEX/MATCH kanske du alltid föredrar att använda den (särskilt på grund av den flexibilitet den har).

Utan att sträcka det för långt, låt mig snabbt ge dig anledningarna till att INDEX/MATCH är bättre än VLOOKUP.

INDEX/MATCH kan titta åt vänster (såväl som till höger) för uppslagsvärdet

Jag täckte det i ett av exemplet ovan.

Om du har ett värde till vänster om uppslagsvärdet kan du inte göra det med VLOOKUP

Åtminstone inte med bara VLOOKUP.

Ja, du kan kombinera VLOOKUP med andra formler och få det gjort, men det blir komplicerat och rörigt.

INDEX/MATCH, å andra sidan, görs för att leta överallt (vare sig det är vänster, höger, upp eller ner)

INDEX/MATCH kan arbeta med vertikala och horisontella intervall

Återigen, med full respekt för VLOOKUP, det är inte tänkt att göra detta.

När allt kommer omkring står V i VLOOKUP för vertikal.

VLOOKUP kan bara gå igenom data som är vertikala, medan INDEX/MATCH kan gå igenom data både vertikalt och horisontellt.

Naturligtvis finns det HLOOKUP -funktionen för att ta hand om horisontell sökning, men det är inte VLOOKUP då … eller hur?

Jag gillar det faktum att INDEX MATCH -kombinationen är tillräckligt flexibel för att fungera med både vertikal och horisontell data.

VLOOKUP kan inte fungera med fallande data

När det gäller den ungefärliga matchen är VLOOKUP och INDEX/MATCH på samma nivå.

Men INDEX MATCH tar poängen eftersom den också kan hantera data som är i fallande ordning.

Jag visar detta i ett av exemplen i denna handledning där vi måste hitta betyget på eleverna baserat på betygstabellen. Om tabellen sorteras i fallande ordning skulle VLOOKUP inte fungera (men INDEX MATCH skulle).

INDEX/MATCH kan vara något snabbare

Jag kommer att vara ärlig. Jag körde inte detta test själv.

Jag förlitar mig på den visdom en Excel -mästare är - Charley Kyd.

Skillnaden i hastighet i VLOOKUP och INDEX/MATCH märks knappast när du har små datamängder. Men om du har tusentals rader och många kolumner kan detta vara en avgörande faktor.

I sin artikel säger Charley Kyd:

”I värsta fall är INDEX-MATCH-metoden ungefär lika snabb som VLOOKUP; när det är som bäst är det mycket snabbare. ”

INDEX/MATCH är oberoende av den faktiska kolumnpositionen

Om du har en dataset som visas nedan när du hämtar poängen för Jim i fysik kan du göra det med VLOOKUP.

Och för att göra det kan du ange kolumnnumret som 3 i VLOOKUP.

Allt är bra.

Men tänk om jag tar bort kolumnen Math.

I så fall går VLOOKUP -formeln sönder.

Varför? - Eftersom det var hårdkodat att använda den tredje kolumnen, och när jag tar bort en kolumn däremellan, blir den tredje kolumnen den andra kolumnen.

Att använda INDEX/MATCH, i det här fallet, är bättre eftersom du kan göra kolumnnumret dynamiskt genom att använda MATCH. Så istället för ett kolumnnummer söker det efter ämnesnamnet och använder det för att returnera kolumnnumret.

Visst kan du göra det genom att kombinera VLOOKUP med MATCH, men om du ändå kombinerar, varför inte göra det med INDEX vilket är mycket mer flexibelt.

När du använder INDEX/MATCH kan du säkert infoga/ta bort kolumner i din datamängd.

Trots alla dessa faktorer finns det en anledning till att VLOOKUP är så populärt.

Och det är en stor anledning.

VLOOKUP är lättare att använda

VLOOKUP tar bara högst fyra argument. Om du kan linda huvudet runt dessa fyra är du klar att gå.

Och eftersom de flesta av de grundläggande uppslagsärendena också hanteras av VLOOKUP har det snabbt blivit den mest populära Excel -funktionen.

Jag kallar det King of Excel -funktioner.

INDEX/MATCH är å andra sidan lite svårare att använda. Du kanske hänger på det när du börjar använda det, men för en nybörjare är VLOOKUP mycket lättare att förklara och lära sig.

Och detta är inte ett nollsummespel.

Så om du är ny i uppslagningsvärlden och inte vet hur du använder VLOOKUP, lär dig bättre det.

Jag har en detaljerad guide om hur du använder VLOOKUP i Excel (med många exempel)

Min avsikt med den här artikeln är inte att ställa två fantastiska funktioner mot varandra. Jag ville visa kraften i kombinationen INDEX MATCH och allt det fantastiska den kan göra.

Hoppas du tyckte att den här artikeln var användbar.

Låt mig veta dina tankar i kommentarfältet, och om du hittar något misstag i denna handledning, vänligen meddela mig.