logo

TechCodeview

INDEX och MATCH Funktion i Excel

INDEX-MATCH har blivit ett mer populärt verktyg för Excel eftersom det löser begränsningen av funktionen VLOOKUP, och det är lättare att använda. INDEX-MATCH-funktionen i Excel har ett antal fördelar jämfört med VLOOKUP-funktionen:

  1. INDEX och MATCH är mer flexibla och snabbare än Vlookup
  2. Det är möjligt att utföra horisontell sökning, vertikal sökning, 2-vägssökning, vänstersökning, skiftlägeskänslig sökning och till och med sökningar baserat på flera kriterier.
  3. I sorterade data är INDEX-MATCH 30 % snabbare än VLOOKUP. Detta innebär att i en större datauppsättning är 30 % snabbare mer meningsfullt.

Låt oss börja med de detaljerade koncepten för varje INDEX och MATCH.



INDEX Funktion

INDEX-funktionen i Excel är mycket kraftfull samtidigt ett flexibelt verktyg som hämtar värdet på en given plats i ett intervall. Med ett annat ord, det returnerar innehållet i en cell, specificerat av rad- och kolumnförskjutning.

Syntax: 

=INDEX(reference, [row], [column])>

Parametrar:



    referens: Uppsättningen av celler som ska förskjutas till. Det kan vara ett enstaka intervall eller en hel datauppsättning i en datatabell. rad [valfritt]: Antalet förskjutna rader. Det betyder att om vi väljer ett tabellreferensintervall som A1:A5 så är cellen/innehållet som vi vill extrahera på hur mycket vertikalt avstånd. Här, för A1 rad kommer att vara 1, för A2 rad = 2, och så vidare. Om vi ​​ger rad = 4 kommer den att extrahera A4. Eftersom rad är valfri så om vi inte anger något radnummer extraherar den hela rader i referensintervallet. Det är A1 till A5 i det här fallet. kolumn [valfritt]: Antalet förskjutna kolumner. Det betyder att om vi väljer ett tabellreferensintervall som A1:B5 så är cellen/innehållet vi vill extrahera på hur stort horisontellt avstånd. Här, för A1 kommer rad att vara 1 och kolumn kommer att vara 1, för B1 kommer rad att vara 1 men kolumnen kommer att vara 2 på samma sätt för A2 rad = 2 kolumn = 1, för B2 rad = 2 kolumn = 2 och så vidare. Om vi ​​ger rad = 5 och kolumn 2 kommer det att extrahera B5. Eftersom kolumnen är valfri så om vi inte anger något radnummer. då extraherar den hela kolumnen i referensintervallet. Till exempel, om vi ger rad = 2 och kolumn som tom kommer den att extrahera (A2:B2). Om vi ​​inte anger både rad och kolumn kommer det att extrahera hela referenstabellen som är (A1:B5).

Referenstabell: Följande tabell kommer att användas som referenstabell för alla exempel på INDEX-funktionen. Första cellen är på B3 (MAT) och den sista diagonala cellen är vid F10 (180).

Referenstabell

Exempel: Nedan finns några exempel på indexfunktioner.



Fall 1: Inga rader och kolumner nämns.

Inmatningskommando: =INDEX(B3:C10)

Fall 1

Fall 2: Endast rader nämns.

Inmatningskommando: =INDEX(B3:C10;2)

Fall 2

Fall 3: Både rader och kolumner nämns.

Inmatningskommando: =INDEX(B3:D10;4;2)

Fall 3

Fall 4: Endast kolumner nämns.

Inmatningskommando: =INDEX(B3 : D10 , , 2)

Fall 4

Problem med INDEX-funktionen: Problemet med INDEX-funktionen är att det finns ett behov av att specificera rader och kolumner för de data som vi letar efter. Låt oss anta att vi har att göra med en datauppsättning för maskininlärning med 10 000 rader och kolumner, då kommer det att vara mycket svårt att söka och extrahera de data vi letar efter. Här kommer konceptet Match Function, som kommer att identifiera rader och kolumner baserat på något tillstånd.

MATCH-funktion

Den hämtar positionen för ett objekt/värde i ett intervall. Det är en mindre förfinad version av en VLOOKUP eller HLOOKUP som endast returnerar platsinformationen och inte den faktiska informationen. MATCH är inte skiftlägeskänslig och bryr sig inte om om intervallet är horisontellt eller vertikalt.

Syntax: 

=MATCH(search_key, range, [search_type])>

Parametrar:

    search_key: Värdet att söka efter. Till exempel 42, Cats eller I24. intervall: Den endimensionella arrayen som ska genomsökas. Det kan antingen vara en enstaka rad eller en enda kolumn.eg->A1:A10 , A2:D2 etc. search_type [valfritt]: Sökmetoden. = 1 (standard) hittar det största värdet som är mindre än eller lika med search_key när intervallet sorteras i stigande ordning.
    • = 0 hittar det exakta värdet när intervallet är osorterat.
    • = -1 hittar det minsta värdet som är större än eller lika med search_key när intervallet sorteras i fallande ordning.

Radnummer eller kolumnnummer kan hittas med hjälp av matchningsfunktionen och kan använda det inuti indexfunktionen så om det finns någon detalj om ett objekt kan all information extraheras om objektet genom att hitta raden/kolumnen för objektet med hjälp av matchning sedan kapsla den i indexfunktionen.

Referenstabell: Följande tabell kommer att användas som referenstabell för alla exempel på MATCH-funktionen. Första cellen är vid B3 (MAT) och den sista diagonala cellen är vid F10 (180)

Referenstabell MATCH-funktion

Exempel: Nedan finns några exempel på MATCH-funktionen-

Fall 1: Söktyp 0, det betyder Exakt matchning.

Inmatningskommando: =MATCH(Sydindian,C3:C10,0)

Fall 1 MATCH

Fall 2: Söktyp 1 (standard).

Inmatningskommando: =MATCH(Sydindian,C3:C10)

Fall 2 MATCH

sdlc


Fall 3: Söktyp -1.

Inmatningskommando: =MATCH(Sydindian,C3:C10,-1)

Fall 3 MATCH

INDEX-MATCH Tillsammans

I de tidigare exemplen gavs de statiska värdena för rader och kolumner i INDEX-funktionen Låt oss anta att det inte finns någon förkunskap om raderna och kolumnpositionen, då kan rader och kolumner ges med MATCH-funktionen. Detta är ett dynamiskt sätt att söka och extrahera värde.

Syntax: 

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Referenstabell: Följande referenstabell kommer att användas. Första cellen är vid B3 (MAT) och den sista diagonala cellen är vid F10 (180)

Referenstabell INDEX-MATCH

Exempel: Låt oss säga att uppgiften är att hitta kostnaden för Masala Dosa. Det är känt att kolumn 3 representerar kostnaden för föremål, men radpositionen för Masala Dosa är inte känd. Problemet kan delas upp i två steg-

Steg 1: Hitta positionen för Masala Dosa genom att använda formeln:

 =MATCH('Masala Dosa',B3:B10,0)>

Här representerar B3:B10 Kolumn Mat och 0 betyder Exakt Matchning. Det kommer att returnera radnumret för Masala Dosa.

Steg 2: Hitta kostnaden för Masala Dosa. Använd INDEX-funktionen för att hitta kostnaden för Masala Dosa. Genom att ersätta ovanstående MATCH-funktionsfråga inuti INDEX-funktionen på den plats där den exakta positionen för Masala Dosa krävs, och kolumnnumret för kostnaden är 3, vilket redan är känt.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

INDEX-MATCH Tillsammans

Tvåvägssökning med INDEX-MATCH tillsammans

I det föregående exemplet var kolumnpositionen för attributet Cost hårdkodad. Så det var inte helt dynamiskt.

Fall 1: Låt oss anta att det inte finns någon kunskap om kolumnnumret för kostnad också, då kan den erhållas med formeln:

 =MATCH('Cost',B3:F3,0)>

Här representerar B3:F3 Header Column.

Fall 2: När rad, såväl som kolumnvärde, tillhandahålls via MATCH-funktionen (utan att ange statiskt värde) kallas det tvåvägssökning. Det kan uppnås med formeln:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Tvåvägssökning

Vänster uppslag

En av de viktigaste fördelarna med INDEX och MATCH framför VLOOKUP-funktionen är möjligheten att utföra en vänstersökning. Det betyder att det är möjligt att extrahera radpositionen för ett objekt från att använda valfritt attribut till höger och värdet av ett annat attribut till vänster kan extraheras.

Till exempel, låt oss säga köp mat vars kostnad bör vara 140 Rs. Indirekt säger vi köp Biryani. I det här exemplet är kostnaden Rs 140/- känd, det finns ett behov av att extrahera maten. Eftersom kolumnen Kostnad är placerad till höger om kolumnen Mat. Om VLOOKUP används kommer den inte att kunna söka på vänster sida av kolumnen Kostnad. Det är därför det inte går att få matnamn med hjälp av VLOOKUP.

För att övervinna denna nackdel kan INDEX-MATCH-funktionen Vänsteruppslagning användas.
Steg 1: Extrahera första radpositionen med kostnad 140 Rs med formeln:

 =MATCH(140, D3:D10,0)>

Här representerar D3: D10 kolumnen Kostnad där sökningen efter radnumret för kostnad 140 Rs görs.

Steg 2: Efter att ha fått radnumret är nästa steg att använda INDEX-funktionen för att extrahera matnamn med formeln:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Här representerar B3:B10 matkolumn och 140 är kostnaden för maten.

Vänster uppslag

Skiftlägeskänslig sökning

I sig är MATCH-funktionen inte skiftlägeskänslig. Detta betyder att om det finns ett livsmedelsnamn DHOKLA och MATCH-funktionen används med följande sökord:

  1. Dhokla
  2. dhokla
  3. DhOkLA

Alla kommer att återställa radpositionen för DHOKLA. Funktionen EXAKT kan dock användas med INDEX och MATCH för att utföra en uppslagning som respekterar versaler och gemener.

Exakt funktion: Excel-funktionen EXAKT jämför två textsträngar, med hänsyn till versaler och gemener, och returnerar TRUE om de är samma, och FALSE om inte. EXAKT är skiftlägeskänsligt.

Exempel:

    EXAKT(DHOKLA,DHOKLA): Detta returnerar True. EXAKT(DHOKLA,Dhokla): Detta kommer att returnera False. EXAKT(DHOKLA,dhokla): Detta kommer att returnera False. EXAKT(DHOKLA,DhOkLA): Detta kommer att returnera False.

Exempel: Låt säga att uppgiften är att söka efter typen av livsmedel Dhokla men på skiftlägeskänsligt sätt. Detta kan göras med formeln-

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Här kommer EXAKTA-funktionen att returnera True om värdet i kolumn B3:B10 matchar Dhokla med samma skiftläge, annars returnerar den False. Nu kommer MATCH-funktionen att tillämpas i kolumn B3:B10 och söka efter en rad med Exakt-värdet TRUE. Efter det kommer INDEX-funktionen att hämta värdet på kolumn C3:C10 (mattypskolumn) på raden som returneras av MATCH-funktionen.

Skiftlägeskänslig sökning

Sök efter flera kriterier

Ett av de svåraste problemen i Excel är en uppslagning baserad på flera kriterier. Med andra ord, en uppslagning som matchar mer än en kolumn samtidigt. I exemplet nedan används funktionen INDEX och MATCH samt boolesk logik för att matcha på 3 kolumner-

  1. Mat.
  2. Kosta.
  3. Kvantitet.

För att få ut totalkostnaden.

Exempel: Låt oss säga att uppgiften är att beräkna den totala kostnaden för Pasta var

    Mat: Pasta. Kostnad: 60. Antal: 1.

Så i det här exemplet finns det tre kriterier för att utföra en matchning. Nedan följer stegen för sökningen baserat på flera kriterier-

Steg 1: Matcha först matkolumnen (B3:B10) med pasta med hjälp av formeln:

 'PASTA' = B3:B10>

Detta kommer att konvertera B3:B10-värdena (matkolumn) till booleska. Det är sant där mat är pasta annars falskt.

Steg 2: Efter det matchar du kostnadskriterier på följande sätt:

 60 = D3:D10>

Detta kommer att ersätta D3:D10 (kostnadskolumn) värden som booleska. Det är sant där kostnad = 60 annars Falskt.

Steg 3: Nästa steg är att matcha det tredje kriteriet som är Kvantitet = 1 på följande sätt:

 1 = E3:E10>

Detta kommer att ersätta E3:E10 kolumn (kvantitet kolumn) som sant där kvantitet = 1 annars blir det falskt.

Steg 4: Multiplicera resultatet av det första, andra och tredje kriteriet. Detta kommer att vara skärningspunkten mellan alla villkor och konvertera Boolean True / False till 1/0.

Steg 5: Nu blir resultatet en kolumn med 0 och 1. Använd här MATCH-funktionen för att hitta radnumret med kolumner som innehåller 1. För om en kolumn har värdet 1, betyder det att den uppfyller alla tre kriterierna.

Steg 6: Efter att ha fått radnumret använd INDEX-funktionen för att få den totala kostnaden för den raden.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Här representerar F3:F10 kolumnen Totalkostnad.