INDEX-MATCH har blitt et mer populært verktøy for Excel ettersom det løser begrensningen til VLOOKUP-funksjonen, og det er enklere å bruke. INDEX-MATCH-funksjonen i Excel har en rekke fordeler i forhold til VLOOKUP-funksjonen:

1. INDEX og MATCH er mer fleksible og raskere enn Vlookup
2. Det er mulig å utføre horisontalt oppslag, vertikalt oppslag, 2-veis oppslag, venstre oppslag, store og små bokstaver, og til og med oppslag basert på flere kriterier.
3. I sorterte data er INDEX-MATCH 30 % raskere enn VLOOKUP. Dette betyr at i et større datasett gir 30 % raskere mer mening.

La oss begynne med de detaljerte konseptene for hver INDEX og MATCH.

INDEX-funksjon

INDEX-funksjonen i Excel er veldig kraftig samtidig et fleksibelt verktøy som henter verdien på et gitt sted i et område. Med et annet ord, det returnerer innholdet i en celle, spesifisert av rad- og kolonneforskyvning.

Syntaks:

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

Parametere:

referanse: Arrayen av celler som skal forskyves inn i. Det kan være et enkelt område eller et helt datasett i en datatabell. rad [valgfritt]: Antall forskyvningsrader. Det betyr at hvis vi velger et tabellreferanseområde som A1:A5, er cellen/innholdet som vi ønsker å trekke ut i hvor stor vertikal avstand. Her vil for A1 rad være 1, for A2 rad = 2, og så videre. Hvis vi gir rad = 4, vil den trekke ut A4. Siden rad er valgfri, så trekker den ut hele rader i referanseområdet hvis vi ikke spesifiserer noe radnummer. Det er A1 til A5 i dette tilfellet. kolonne [valgfritt]: Antall forskyvningskolonner. Det betyr at hvis vi velger et tabellreferanseområde som A1:B5, er cellen/innholdet vi ønsker å trekke ut i hvor stor horisontal avstand. Her vil rad for A1 være 1 og kolonne 1, for B1 vil rad være 1, men kolonnen vil være 2 på samme måte for A2 rad = 2 kolonne = 1, for B2 rad = 2 kolonne = 2 og så videre. Hvis vi gir rad = 5 og kolonne 2, vil den trekke ut B5. Siden kolonnen er valgfri, så hvis vi ikke spesifiserer noen radnr. da vil den trekke ut hele kolonnen i referanseområdet. For eksempel, hvis vi gir rad = 2 og kolonne som tom, vil den trekke ut (A2:B2). Hvis vi ikke spesifiserer både rad og kolonne, vil den trekke ut hele referansetabellen som er (A1:B5).

Referansetabell: Følgende tabell vil bli brukt som en referansetabell for alle eksemplene på INDEX-funksjonen. Første celle er på B3 (MAT) og den siste diagonale cellen er på F10 (180).

Eksempler: Nedenfor er noen eksempler på indeksfunksjoner.

Tilfelle 1: Ingen rader og kolonner er nevnt.

Inndatakommando: =INDEKS(B3:C10)

Tilfelle 2: Kun rader er nevnt.

Inndatakommando: =INDEKS(B3:C10;2)

Tilfelle 3: Både rader og kolonner er nevnt.

Inndatakommando: =INDEKS(B3:D10;4;2)

Tilfelle 4: Kun kolonner er nevnt.

Inndatakommando: =INDEKS(B3 : D10 , , 2)

Problem med INDEX-funksjonen: Problemet med INDEX-funksjonen er at det er behov for å spesifisere rader og kolonner for dataene vi leter etter. La oss anta at vi har å gjøre med et maskinlæringsdatasett med 10 000 rader og kolonner, så vil det være veldig vanskelig å søke og trekke ut dataene vi leter etter. Her kommer konseptet Match Function, som vil identifisere rader og kolonner basert på en eller annen tilstand.

MATCH funksjon

Den henter posisjonen til en vare/verdi i et område. Det er en mindre raffinert versjon av en VLOOKUP eller HLOOKUP som bare returnerer plasseringsinformasjonen og ikke de faktiske dataene. MATCH skiller ikke mellom store og små bokstaver og bryr seg ikke om området er horisontalt eller vertikalt.

Syntaks:

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

Parametere:

search_key: Verdien som skal søkes etter. For eksempel 42, Cats eller I24. område: Den endimensjonale matrisen som skal søkes i. Det kan enten være en enkelt rad eller en enkelt kolonne.f.eks->A1:A10 , A2:D2 osv. search_type [valgfritt]: Søkemetoden. = 1 (standard) finner den største verdien mindre enn eller lik search_key når området er sortert i stigende rekkefølge.
• = 0 finner den nøyaktige verdien når området er usortert.
• = -1 finner den minste verdien større enn eller lik søkenøkkel når området er sortert i synkende rekkefølge.

Radnummer eller kolonnenummer kan bli funnet ved å bruke matchfunksjonen og kan bruke det inne i indeksfunksjonen, så hvis det er noen detaljer om en vare, kan all informasjon trekkes ut om varen ved å finne raden/kolonnen til varen ved å bruke match deretter neste den inn i indeksfunksjonen.

Referansetabell: Følgende tabell vil bli brukt som en referansetabell for alle eksemplene på MATCH-funksjonen. Første celle er på B3 (MAT) og den siste diagonale cellen er på F10 (180)

Eksempler: Nedenfor er noen eksempler på MATCH-funksjonen-

Tilfelle 1: Søketype 0, det betyr eksakt samsvar.

Inndatakommando: =MATCH(Sør-indisk,C3:C10,0)

Tilfelle 2: Søketype 1 (standard).

Inndatakommando: =MATCH(Sør-indisk,C3:C10)

sletting fra et binært søketre

Tilfelle 3: Søketype -1.

Inndatakommando: =MATCH(Sør-indisk,C3:C10,-1)

INDEKS-MATCH Sammen

I de forrige eksemplene ble de statiske verdiene til rader og kolonner gitt i INDEX-funksjonen La oss anta at det ikke er noen forkunnskap om radene og kolonneposisjonen, så kan rader og kolonner posisjoneres ved å bruke MATCH-funksjonen. Dette er en dynamisk måte å søke og trekke ut verdier på.

Syntaks:

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

Referansetabell: Følgende referansetabell vil bli brukt. Første celle er på B3 (MAT) og den siste diagonale cellen er på F10 (180)

Eksempel: La oss si at oppgaven er å finne kostnadene for Masala Dosa. Det er kjent at kolonne 3 representerer kostnaden for varer, men radposisjonen til Masala Dosa er ikke kjent. Problemet kan deles inn i to trinn-

Trinn 1: Finn posisjonen til Masala Dosa ved å bruke formelen:

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

Her representerer B3:B10 kolonnemat og 0 betyr eksakt samsvar. Det vil returnere radnummeret til Masala Dosa.

Steg 2: Finn kostnadene for Masala Dosa. Bruk INDEX-funksjonen for å finne kostnadene for Masala Dosa. Ved å erstatte MATCH-funksjonen ovenfor i INDEX-funksjonen på stedet der den nøyaktige posisjonen til Masala Dosa kreves, og kolonnenummeret for kostnaden er 3 som allerede er kjent.

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

Toveisoppslag med INDEX-MATCH sammen

I forrige eksempel ble kolonneposisjonen til kostnadsattributtet hardkodet. Så det var ikke helt dynamisk.

Tilfelle 1: La oss anta at det ikke er kunnskap om kolonnenummeret til kostnad også, så kan det oppnås ved å bruke formelen:

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

Her representerer B3:F3 Header Column.

Tilfelle 2: Når rad, så vel som kolonneverdi, er gitt via MATCH-funksjonen (uten å gi statisk verdi), kalles det toveis oppslag. Det kan oppnås ved å bruke formelen:

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

Venstre oppslag

En av de viktigste fordelene med INDEX og MATCH fremfor VLOOKUP-funksjonen er muligheten til å utføre et venstreoppslag. Det betyr at det er mulig å trekke ut radposisjonen til et element fra å bruke et hvilket som helst attributt til høyre, og verdien til et annet attributt til venstre kan trekkes ut.

For eksempel, la oss si kjøp mat som koster 140 Rs. Indirekte sier vi kjøp Biryani. I dette eksemplet er kostnaden Rs 140/- kjent, det er behov for å trekke ut maten. Siden Kostnad-kolonnen er plassert til høyre for Mat-kolonnen. Hvis VLOOKUP brukes, vil den ikke kunne søke på venstre side av kostnadskolonnen. Derfor er det ikke mulig å få matnavn ved å bruke VLOOKUP.

For å overvinne denne ulempen INDEX-MATCH funksjonen Venstre oppslag kan brukes.
Trinn 1: Trekk først ut radposisjonen med kostnad 140 Rs ved å bruke formelen:

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

Her representerer D3: D10 kostnadskolonnen der søket etter radnummeret for kostnad 140 Rs gjøres.

Steg 2: Etter å ha fått radnummeret, er neste trinn å bruke INDEX-funksjonen til å trekke ut matnavn ved å bruke formelen:

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

Her representerer B3:B10 matkolonne og 140 er kostnaden for matvaren.

Oppslag for store og små bokstaver

I seg selv skiller ikke MATCH-funksjonen mellom store og små bokstaver. Dette betyr at hvis det er et matnavn DHOKLA og MATCH-funksjonen brukes med følgende søkeord:

1. Dhokla
2. dhokla
3. DhOkLA

Alle vil returnere radposisjonen til DHOKLA. EKSAKT-funksjonen kan imidlertid brukes med INDEX og MATCH for å utføre et oppslag som respekterer store og små bokstaver.

Nøyaktig funksjon: Excel EXACT-funksjonen sammenligner to tekststrenger, tar hensyn til store og små bokstaver, og returnerer TRUE hvis de er like, og FALSE hvis ikke. EXACT skiller mellom store og små bokstaver.

Eksempler:

EXACT(DHOKLA,DHOKLA): Dette vil returnere True. EXACT(DHOKLA,Dhokla): Dette vil returnere False. EXACT(DHOKLA,dhokla): Dette vil returnere False. EXACT(DHOKLA,DhOkLA): Dette vil returnere False.

Eksempel: La oss si at oppgaven er å søke etter mattypen Dhokla, men på en måte som skiller mellom store og små bokstaver. Dette kan gjøres ved å bruke formelen-

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

Her vil EKSAKT-funksjonen returnere True hvis verdien i Kolonne B3:B10 samsvarer med Dhokla med samme store og små bokstaver, ellers vil den returnere False. Nå vil MATCH-funksjonen gjelde i kolonne B3:B10 og søke etter en rad med eksakt verdi TRUE. Deretter vil INDEX-funksjonen hente verdien av kolonne C3:C10 (mattypekolonne) i raden som returneres av MATCH-funksjonen.

Oppslag med flere kriterier

Et av de vanskeligste problemene i Excel er et oppslag basert på flere kriterier. Med andre ord, et oppslag som samsvarer på mer enn én kolonne samtidig. I eksemplet nedenfor brukes INDEX- og MATCH-funksjonen og boolsk logikk for å matche på 3 kolonner-

1. Mat.
2. Koste.
3. Mengde.

For å trekke ut totalkostnaden.

Eksempel: La oss si at oppgaven er å beregne den totale kostnaden for Pasta hvor

Mat: Pasta. Pris: 60. Antall: 1.

Så i dette eksemplet er det tre kriterier for å utføre en match. Nedenfor er trinnene for søket basert på flere kriterier-

Trinn 1: Match først matkolonne (B3:B10) med pasta ved å bruke formelen:

 'PASTA' = B3:B10>

Dette vil konvertere B3:B10 (matkolonne)-verdier til boolske. Det er sant der mat er pasta ellers usant.

Steg 2: Etter det, match kostnadskriterier på følgende måte:

 60 = D3:D10>

Dette vil erstatte D3:D10 (kostnadskolonne)-verdier som boolske. Det er sant der Cost=60 ellers False.

Trinn 3: Neste trinn er å matche det tredje kriteriet som er Kvantitet = 1 på følgende måte:

 1 = E3:E10>

Dette vil erstatte E3:E10-kolonne (Antallskolonne) som Sant der Antall = 1 ellers vil det være usann.

Trinn 4: Multipliser resultatet av det første, andre og tredje kriteriet. Dette vil være skjæringspunktet mellom alle forhold og konvertere boolsk sann / usann til 1/0.

Trinn 5: Nå blir resultatet en Kolonne med 0 Og 1. Her bruker du MATCH-funksjonen for å finne radtallet med kolonner som inneholder 1. For hvis en kolonne har verdien 1, betyr det at den tilfredsstiller alle tre kriteriene.

Trinn 6: Etter å ha fått radnummeret, bruk INDEKS-funksjonen for å få den totale kostnaden for den raden.

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

Her representerer F3:F10 totalkostnadskolonnen.