INDEX-MATCH er blevet et mere populært værktøj til Excel, da det løser begrænsningen af ​​VLOOKUP-funktionen, og det er lettere at bruge. INDEX-MATCH-funktionen i Excel har en række fordele i forhold til VLOOKUP-funktionen:

1. INDEX og MATCH er mere fleksible og hurtigere end Vlookup
2. Det er muligt at udføre horisontalt opslag, lodret opslag, 2-vejs opslag, venstre opslag, store og små bogstaver, og endda opslag baseret på flere kriterier.
3. I sorterede data er INDEX-MATCH 30 % hurtigere end VLOOKUP. Det betyder, at i et større datasæt giver 30% hurtigere mere mening.

Lad os begynde med de detaljerede koncepter for hver INDEX og MATCH.

INDEX Funktion

INDEX-funktionen i Excel er meget kraftfuld og samtidig et fleksibelt værktøj, der henter værdien på en given placering i et interval. Med et andet ord returnerer den indholdet af en celle, specificeret ved række- og kolonneforskydning.

Syntaks:

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

Parametre:

reference: Det array af celler, der skal forskydes til. Det kan være et enkelt område eller et helt datasæt i en datatabel. række [valgfrit]: Antallet af forskudte rækker. Det betyder, at hvis vi vælger et tabelreferenceområde som A1:A5, så er cellen/indholdet, som vi ønsker at udtrække, i hvor meget lodret afstand. Her vil for A1 række være 1, for A2 række = 2, og så videre. Hvis vi giver række = 4, vil den udtrække A4. Da række er valgfri, så hvis vi ikke angiver noget rækkenummer, udtrækker den hele rækker i referenceområdet. Det er A1 til A5 i dette tilfælde. kolonne [valgfrit]: Antallet af forskudte kolonner. Det betyder, at hvis vi vælger et tabelreferenceområde som A1:B5, så er cellen/indholdet, vi ønsker at udtrække, i hvor stor vandret afstand. Her vil for A1 række være 1, og kolonne vil være 1, for B1 vil række være 1, men kolonnen vil være 2 på samme måde for A2 række = 2 kolonne = 1, for B2 række = 2 kolonne = 2 og så videre. Hvis vi giver række = 5 og kolonne 2, vil det udtrække B5. Da kolonnen er valgfri, så hvis vi ikke angiver noget rækkenr. så vil den udtrække hele kolonnen i referenceområdet. For eksempel, hvis vi giver række = 2 og kolonne som tom, vil den ekstrahere (A2:B2). Hvis vi ikke angiver både række og kolonne, vil det udtrække hele referencetabellen, dvs. (A1:B5).

Referencetabel: Følgende tabel vil blive brugt som referencetabel for alle eksemplerne på INDEX-funktionen. Første celle er på B3 (FOOD) og den sidste diagonale celle er på F10 (180).

Eksempler: Nedenfor er nogle eksempler på indeksfunktioner.

Case 1: Ingen rækker og kolonner er nævnt.

Indtast kommando: =INDEKS(B3:C10)

Tilfælde 2: Kun Rækker nævnes.

Indtast kommando: =INDEKS(B3:C10;2)

Tilfælde 3: Både rækker og kolonner er nævnt.

Indtast kommando: =INDEKS(B3:D10;4;2)

Case 4: Kun kolonner er nævnt.

Indtast kommando: =INDEKS(B3 : D10 , , 2)

Problem med INDEX-funktionen: Problemet med INDEX-funktionen er, at der er behov for at specificere rækker og kolonner for de data, vi leder efter. Lad os antage, at vi har at gøre med et maskinlæringsdatasæt på 10000 rækker og kolonner, så vil det være meget vanskeligt at søge og udtrække de data, vi leder efter. Her kommer konceptet Match Function, som vil identificere rækker og kolonner baseret på en eller anden betingelse.

MATCH funktion

Den henter positionen af ​​en vare/værdi i et interval. Det er en mindre raffineret version af en VLOOKUP eller HLOOKUP, der kun returnerer placeringsoplysningerne og ikke de faktiske data. MATCH skelner ikke mellem store og små bogstaver og er ligeglad med, om intervallet er vandret eller lodret.

Syntaks:

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

Parametre:

search_key: Værdien, der skal søges efter. For eksempel 42, Cats eller I24. interval: Det endimensionelle array, der skal søges i. Det kan enten være en enkelt række eller en enkelt column.eg->A1:A10 , A2:D2 etc. search_type [valgfrit]: Søgemetoden. = 1 (standard) finder den største værdi mindre end eller lig med search_key, når området er sorteret i stigende rækkefølge.
• = 0 finder den nøjagtige værdi, når området er usorteret.
• = -1 finder den mindste værdi, der er større end eller lig med search_key, når området er sorteret i faldende rækkefølge.

Rækkenummer eller kolonnenummer kan findes ved hjælp af matchfunktionen og kan bruge det inde i indeksfunktionen, så hvis der er nogen detaljer om en vare, så kan al information udtrækkes om varen ved at finde rækken/kolonnen for varen ved hjælp af match derefter indlejre den i indeksfunktionen.

Referencetabel: Følgende tabel vil blive brugt som referencetabel for alle eksemplerne på MATCH-funktionen. Første celle er ved B3 (MAD) og den sidste diagonale celle er ved F10 (180)

Eksempler: Nedenfor er nogle eksempler på MATCH-funktionen-

Case 1: Søgetype 0, det betyder eksakt match.

Indtast kommando: =MATCH(Sydindisk,C3:C10,0)

Tilfælde 2: Søgetype 1 (standard).

Indtast kommando: =MATCH(Sydindisk,C3:C10)

download youtube videoer vlc

Case 3: Søgetype -1.

Indtast kommando: =MATCH(Sydindisk,C3:C10,-1)

INDEX-MATCH Sammen

I de foregående eksempler blev de statiske værdier af rækker og kolonner angivet i INDEX-funktionen Lad os antage, at der ikke er nogen forudgående viden om rækkerne og kolonnepositionen, så kan rækker og kolonners position leveres ved hjælp af MATCH-funktionen. Dette er en dynamisk måde at søge og udtrække værdi på.

Syntaks:

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

Referencetabel: Følgende referencetabel vil blive brugt. Første celle er på B3 (MAD) og den sidste diagonale celle er på F10 (180)

Eksempel: Lad os sige, at opgaven er at finde omkostningerne ved Masala Dosa. Det er kendt, at kolonne 3 repræsenterer prisen på varer, men rækkepositionen for Masala Dosa kendes ikke. Problemet kan opdeles i to trin-

Trin 1: Find positionen for Masala Dosa ved at bruge formlen:

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

Her repræsenterer B3:B10 Kolonnemad, og 0 betyder Præcis Match. Det vil returnere rækkenummeret for Masala Dosa.

Trin 2: Find prisen på Masala Dosa. Brug INDEX-funktionen til at finde prisen på Masala Dosa. Ved at erstatte ovenstående MATCH-funktionsforespørgsel inde i INDEX-funktionen på det sted, hvor den nøjagtige position af Masala Dosa er påkrævet, og kolonnenummeret for omkostningerne er 3, som allerede er kendt.

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

Opslag på to måder med INDEX-MATCH sammen

I det foregående eksempel blev kolonnepositionen af ​​Cost-attributten hårdkodet. Så det var ikke helt dynamisk.

Case 1: Lad os antage, at der ikke er nogen viden om kolonnenummeret for omkostninger også, så kan det opnås ved hjælp af formlen:

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

Her repræsenterer B3:F3 Header Column.

Tilfælde 2: Når række, såvel som kolonneværdi, leveres via MATCH-funktionen (uden at give statisk værdi), så kaldes det Two-Way Lookup. Det kan opnås ved hjælp af formlen:

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

Venstre opslag

En af de vigtigste fordele ved INDEX og MATCH i forhold til VLOOKUP-funktionen er muligheden for at udføre et venstreopslag. Det betyder, at det er muligt at udtrække rækkepositionen for en vare fra at bruge en hvilken som helst attribut til højre, og værdien af ​​en anden attribut til venstre kan udtrækkes.

Lad os for eksempel sige køb mad, hvis pris skal være 140 Rs. Indirekte siger vi køb Biryani. I dette eksempel er prisen Rs 140/- kendt, der er behov for at udvinde maden. Da kolonnen Pris er placeret til højre for kolonnen Mad. Hvis VLOOKUP anvendes, vil det ikke være i stand til at søge i venstre side af priskolonnen. Derfor er det ikke muligt at få Food Name ved hjælp af VLOOKUP.

For at overvinde denne ulempe kan INDEX-MATCH funktion Venstre opslag bruges.
Trin 1: Udtræk først rækkepositionen på 140 Rs ved hjælp af formlen:

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

Her repræsenterer D3: D10 den priskolonne, hvor søgningen efter rækkenummeret på pris 140 Rs udføres.

Trin 2: Efter at have fået rækkenummeret, er det næste trin at bruge INDEX-funktionen til at udtrække fødevarenavn ved hjælp af formlen:

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

Her repræsenterer B3:B10 Madkolonne, og 140 er prisen på fødevaren.

Opslag på store og små bogstaver

I sig selv skelner MATCH-funktionen ikke mellem store og små bogstaver. Det betyder, at hvis der er et fødevarenavn DHOKLA, og MATCH-funktionen bruges med følgende søgeord:

1. Dhokla
2. dhokla
3. DhOkLA

Alle vil returnere rækkepositionen for DHOKLA. EXACT-funktionen kan dog bruges sammen med INDEX og MATCH til at udføre et opslag, der respekterer store og små bogstaver.

Præcis funktion: Excel EXACT-funktionen sammenligner to tekststrenge, idet der tages højde for store og små bogstaver, og returnerer SAND, hvis de er ens, og FALSK, hvis ikke. EXACT skelner mellem store og små bogstaver.

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: Lad os sige, at opgaven er at søge efter fødevaretypen Dhokla, men på en case-sensitiv måde. Dette kan gøres ved hjælp af formlen-

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

Her vil EXACT-funktionen returnere True, hvis værdien i kolonne B3:B10 matcher Dhokla med samme store og små bogstaver, ellers returnerer den False. Nu vil MATCH-funktionen anvendes i kolonne B3:B10 og søge efter en række med den nøjagtige værdi TRUE. Derefter henter INDEX-funktionen værdien af ​​kolonne C3:C10 (Food Type Column) i rækken, der returneres af MATCH-funktionen.

Opslag på flere kriterier

Et af de sværeste problemer i Excel er et opslag baseret på flere kriterier. Med andre ord et opslag, der matcher mere end én kolonne på samme tid. I eksemplet nedenfor bruges INDEX og MATCH funktionen og boolesk logik til at matche på 3 kolonner-

1. Mad.
2. Koste.
3. Antal.

For at udtrække de samlede omkostninger.

Eksempel: Lad os sige, at opgaven er at beregne de samlede omkostninger for Pasta hvor

Mad: Pasta. Pris: 60. Antal: 1.

Så i dette eksempel er der tre kriterier for at udføre et match. Nedenfor er trinene til søgning baseret på flere kriterier-

Trin 1: Match først madkolonne (B3:B10) med pasta ved hjælp af formlen:

 'PASTA' = B3:B10>

Dette vil konvertere B3:B10 (fødevarekolonne) værdier til boolesk. Det er rigtigt, hvor mad er pasta ellers falsk.

Trin 2: Derefter matcher omkostningskriterierne på følgende måde:

 60 = D3:D10>

Dette vil erstatte D3:D10 (Cost Column) værdier som Boolean. Det er sandt, hvor pris = 60 ellers falsk.

Trin 3: Næste trin er at matche det tredje kriterium, der er Kvantitet = 1 på følgende måde:

 1 = E3:E10>

Dette vil erstatte E3:E10 Kolonne (Mængde Kolonne) som Sand, hvor Antal = 1 ellers vil den være Falsk.

Trin 4: Multiplicer resultatet af det første, andet og tredje kriterium. Dette vil være skæringspunktet mellem alle betingelser og konvertere Boolean Sand / Falsk til 1/0.

Trin 5: Nu bliver resultatet en kolonne med 0 og 1. Brug her MATCH-funktionen til at finde rækkeantallet af kolonner, der indeholder 1. For hvis en kolonne har værdien 1, så betyder det, at den opfylder alle tre kriterier.

Trin 6: Når du har fået rækkenummeret, skal du bruge INDEX-funktionen for at få de samlede omkostninger for den række.

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

Her repræsenterer F3:F10 kolonnen samlede omkostninger.