Waarom INDEX en MATCH beter is as VLOOKUP in Excel

Ons het voorheen aan beginners verduidelik hoe om die basiese funksies van VLOOKUP (Engels VLOOKUP, die afkorting staan ​​vir "vertical lookup function") te gebruik. En ervare gebruikers is verskeie meer ingewikkelde formules gewys.

En in hierdie artikel sal ons probeer om inligting te gee oor 'n ander metode om met vertikale soektog te werk.

Jy wonder dalk: "Hoekom is dit nodig?". En dit is nodig om alle moontlike soekmetodes te wys. Daarbenewens verhoed talle VLOOKUP-beperkings dikwels die verkryging van die gewenste resultaat. In hierdie verband is INDEX( ) MATCH( ) baie meer funksioneel en divers, en hulle het ook minder beperkings.

Basiese beginsels INDEX MATCH

Aangesien die doel van hierdie gids is om te wys hoe goed hierdie kenmerk is, het ons Kom ons kyk na die basiese inligting oor die beginsels van die werking daarvan. En ons sal voorbeelde wys, en ook oorweeg hoekom dit beter is as VLOOKUP ().

INDEKS Funksie Sintaksis en gebruik

Hierdie funksie help om die gewenste waarde te vind tussen die gespesifiseerde soekareas gebaseer op die kolom of reëlnommer. sintaksis:

=INDEKS(skikking, rynommer, kolomnommer):

• skikking – die area waarin die soektog sal plaasvind;
• lynnommer – die nommer van die lyn wat in die gespesifiseerde skikking gesoek moet word. Indien die rynommer onbekend is, moet die kolomnommer gespesifiseer word;
• kolomnommer – die nommer van die kolom wat in die gespesifiseerde skikking gevind moet word. As die waarde onbekend is, word 'n lynnommer vereis.

'n Voorbeeld van 'n eenvoudige formule:

=INDEKS(A1:S10,2,3;XNUMX;XNUMX)

Die funksie sal soek in die reeks van A1 tot C10. Die nommers wys uit watter ry (2) en kolom (3) om die verlangde waarde te wys. Die resultaat sal sel C2 wees.

Redelik eenvoudig, reg? Maar wanneer jy met regte dokumente werk, is dit onwaarskynlik dat jy inligting oor kolomnommers of selle sal hê. Dit is waarvoor die MATCH()-funksie is.

MATCH-funksie sintaksis en gebruik

Die MATCH()-funksie soek na die verlangde waarde en wys sy benaderde getal in die gespesifiseerde soekarea.

Die searchpos() sintaksis lyk soos volg:

=MATCH(waarde om op te soek, skikking om op te soek, passoort)

• soekwaarde – die getal of teks wat gevind moet word;
• gesoekte skikking – die area waar die soektog sal plaasvind;
• pasmaattipe – spesifiseer of die presiese waarde of die waardes naaste daaraan gesoek moet word:
  • 1 (of geen waarde gespesifiseer nie) – gee die grootste waarde terug wat gelyk is aan of minder as die waarde wat gespesifiseer is;
  • 0 – wys 'n presiese passing met die gesoekte waarde. In die kombinasie INDEX() MATCH() sal jy byna altyd 'n presiese passing nodig hê, daarom skryf ons 0;
  • -1 – Toon die kleinste waarde wat groter as of gelyk is aan die waarde gespesifiseer in die formule. Sorteer word in dalende volgorde uitgevoer.

Byvoorbeeld, in die reeks B1:B3 is New York, Parys, Londen geregistreer. Die formule hieronder sal die nommer 3 wys omdat Londen derde op die lys is:

=BLOOG (Londen,B1:B3,0)

Hoe om met die INDEX MATCH-funksie te werk 

Jy het waarskynlik reeds begin om die beginsel te verstaan ​​waarvolgens die gesamentlike werk van hierdie funksies gebou word. Kortom, dan INDEX() soek na die gewenste waarde tussen die gespesifiseerde rye en kolomme. En MATCH() wys die getalle van hierdie waardes:

=INDEKS(kolom waaruit die waarde teruggestuur word, MATCH(waarde om te soek, kolom om in te soek, 0))

Het u steeds moeilik om te verstaan ​​hoe dit werk? Miskien sal 'n voorbeeld beter verduidelik. Gestel jy het 'n lys van wêreldhoofstede en hul bevolking:

Om die grootte van die bevolking van 'n sekere hoofstad, byvoorbeeld die hoofstad van Japan, uit te vind, gebruik ons ​​die volgende formule:

=INDEKS(C2:C10, MATCH(Japan, A2:A10,0))

Verduideliking:

• Die MATCH()-funksie soek die waarde – “Japan” in die skikking A2:A10 en gee die getal 3 terug, want Japan is die derde waarde in die lys. 
• Hierdie syfer gaan nareëlnommer” in die INDEX() formule en vertel die funksie om 'n waarde uit hierdie ry te druk.

So die bogenoemde formule word die standaard formule INDEKS(C2:C10,3;XNUMX). Die formule soek vanaf selle C2 tot C10 en gee data van die derde sel in hierdie reeks terug, dit wil sê C4, omdat die aftelling vanaf die tweede ry begin.

Wil jy nie die naam van die stad in die formule voorskryf nie? Skryf dit dan in enige sel, sê F1, en gebruik dit as 'n verwysing in die MATCH() formule. En jy eindig met 'n dinamiese soekformule:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Belangrik! Aantal lyne in verskeidenheid INDEX() moet dieselfde wees as die aantal rye in geagte skikking in MATCH(), anders sal jy die verkeerde resultaat kry.

Wag 'n bietjie, hoekom gebruik nie net die VLOOKUP() formule nie?

=OPSOEK(F1; A2:C10; 3; Onwaar)

 Wat is die punt daarvan om tyd te mors om al hierdie kompleksiteite van INDEX MATCH te probeer uitvind?

In hierdie geval maak dit nie saak watter funksie om te gebruik nie. Dit is net 'n voorbeeld om te verstaan ​​hoe die INDEX() en MATCH() funksies saamwerk. Ander voorbeelde sal wys waartoe hierdie funksies in staat is in situasies waar die VLOOKUP kragteloos is. 

INDEX MATCH of VLOOKUP

Wanneer hulle besluit watter soekformule om te gebruik, stem baie saam dat INDEX() en MATCH() verreweg beter is as VLOOKUP. Baie mense gebruik egter steeds VLOOKUP(). Eerstens is VLOOKUP() eenvoudiger, en tweedens verstaan ​​gebruikers nie al die voordele van werk met INDEX() en MATCH() ten volle. Sonder hierdie kennis sal niemand instem om hul tyd te spandeer om 'n komplekse stelsel te bestudeer nie.

Hier is die belangrikste voordele van INDEX() en MATCH() bo VLOOKUP():

• Soek van regs na links. VLOOKUP() kan nie van regs na links soek nie, so die waardes waarna jy soek moet altyd in die mees linkse kolomme van die tabel wees. Maar INDEX() en MATCH() kan dit sonder 'n probleem hanteer. Hierdie artikel sal jou vertel hoe dit in die praktyk lyk: hoe om die gewenste waarde aan die linkerkant te vind.

1. Veilige byvoeging of verwydering van kolomme. Die VLOOKUP()-formule wys verkeerde resultate wanneer kolomme verwyder of bygevoeg word omdat VLOOKUP() die presiese kolomnommer nodig het om suksesvol te wees. Natuurlik, wanneer kolomme bygevoeg of verwyder word, verander hul getalle ook. 

En in die INDEX() en MATCH() formules word 'n reeks kolomme gespesifiseer, nie individuele kolomme nie. As gevolg hiervan, kan jy veilig kolomme byvoeg en verwyder sonder om die formule elke keer op te dateer.

1. Geen beperkings op soekvolumes nie. Wanneer VLOOKUP() gebruik word, moet die totale aantal soekkriteria nie 255 karakters oorskry nie, anders sal jy 'n #VALUE! As jou data dus 'n groot aantal karakters bevat, is INDEX() en MATCH() die beste opsie.
2. Hoë verwerkingspoed. As jou tafels relatief klein is, is dit onwaarskynlik dat jy enige verskil sal sien. Maar as die tabel honderde of duisende rye bevat, en daarvolgens honderde en duisende formules is, sal INDEX () en MATCH () baie vinniger hanteer as VLOOKUP (). Die feit is dat Excel slegs die kolomme sal verwerk wat in die formule gespesifiseer is, in plaas daarvan om die hele tabel te verwerk. 

Die prestasie-impak van VLOOKUP() sal veral opvallend wees as jou werkblad 'n groot aantal formules soos VLOOKUP() en SUM() bevat. Afsonderlike kontrole van die VLOOKUP() funksies word vereis om elke waarde in 'n skikking te ontleed. Excel moet dus 'n groot hoeveelheid inligting verwerk, en dit vertraag die werk aansienlik.

Formule voorbeelde 

Ons het reeds die bruikbaarheid van hierdie funksies uitgepluis, sodat ons kan oorgaan na die interessantste deel: die toepassing van kennis in die praktyk.

Formule om van regs na links te soek

Soos reeds genoem, kan VLOOKUP nie hierdie vorm van soektog uitvoer nie. Dus, as die gewenste waardes nie in die linkerkantste kolom is nie, sal VLOOKUP() nie 'n resultaat lewer nie. Die INDEX() en MATCH() funksies is meer veelsydig, en die ligging van die waardes speel nie 'n groot rol vir hulle om te werk nie.

Ons sal byvoorbeeld 'n rangkolom aan die linkerkant van ons tabel byvoeg en probeer uitvind watter rang in terme van bevolking die hoofstad van Ons Land beklee.

In sel G1 skryf ons die waarde wat gevind moet word, en gebruik dan die volgende formule om in die reeks C1:C10 te soek en die ooreenstemmende waarde van A2:A10 terug te gee:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Vinnig. As jy van plan is om hierdie formule vir veelvuldige selle te gebruik, maak seker dat jy die reekse regmaak deur absolute adressering (byvoorbeeld, $A$2: $A$10 en $C$2: 4C$10).

INDEKS MEER BLOOTSTEL MEER BLOOTSTELLING  om in kolomme en rye te soek

In die bogenoemde voorbeelde het ons hierdie funksies gebruik as 'n plaasvervanger vir VLOOKUP() om waardes uit 'n voorafbepaalde reeks rye terug te gee. Maar wat as jy 'n matriks of tweesydige soektog moet doen?

Dit klink ingewikkeld, maar die formule vir sulke berekeninge is soortgelyk aan die standaard INDEX() MATCH() formule, met net een verskil: die MATCH()-formule moet twee keer gebruik word. Die eerste keer om die rynommer te kry, en die tweede keer om die kolomnommer te kry:

=INDEKS(skikking, MATCH(vertikale soekwaarde, soekkolom, 0), MATCH(horisontale soekwaarde, soekry, 0))

Kom ons kyk na die tabel hieronder en probeer om 'n formule te maak INDEX() EXPRESS() EXPRESS() ten einde demografie in 'n spesifieke land vir 'n geselekteerde jaar te vertoon.

Die teikenland is in sel G1 (vertikale opsoek) en die teikenjaar is in sel G2 (horisontale opsoek). Die formule sal soos volg lyk:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Hoe hierdie formule werk

Soos met enige ander komplekse formules, is dit makliker om te verstaan ​​deur dit in individuele vergelykings af te breek. En dan kan jy verstaan ​​wat elke individuele funksie doen:

• MATCH(G1;A2:A11,0;XNUMX) – soek 'n waarde (G1) in die reeks A2:A11 en wys die nommer van hierdie waarde, in ons geval is dit 2;
• SOEK(G2;B1:D1,0;XNUMX) – soek 'n waarde (G2) in die reeks B1:D1. In hierdie geval was die resultaat 3.

Die gevonde ry- en kolomnommers word na die ooreenstemmende waarde in die INDEX()-formule gestuur:

=INDEKS(B2:D11,2,3;XNUMX;XNUMX)

As gevolg hiervan het ons 'n waarde wat in 'n sel is by die snypunt van 2 rye en 3 kolomme in die reeks B2:D11. En die formule toon die gewenste waarde, wat in sel D3 is.

Soek volgens verskeie toestande met INDEX en MATCH

As jy ons gids tot VLOOKUP() gelees het, het jy waarskynlik verskeie soekformules probeer. Maar hierdie soekmetode het een beduidende beperking - die behoefte om 'n hulpkolom by te voeg.

Maar die goeie nuus is dat Met INDEX() en MATCH() kan jy vir verskeie toestande soek sonder om jou werkblad te wysig of te verander.

Hier is die algemene multi-voorwaarde soekformule vir INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Die nota: hierdie formule moet saam met die sleutelbordkortpad gebruik word CTRL+SHIFT+ENTER.

Gestel jy moet die waarde vind waarna jy soek, gebaseer op 2 voorwaardes: Koper и Produk.

Dit vereis die volgende formule:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

In hierdie formule is C2:C10 die reeks waarin die soektog sal plaasvind, F1 - hierdie toestand, A2:A10 — is die reeks om die toestand te vergelyk, F2 – toestand 2, v2:v10 – reeks vir vergelyking van toestand 2.

Moenie vergeet om die kombinasie aan die einde van die werk met die formule te druk nie CTRL+SHIFT+ENTER - Excel sal die formule outomaties toemaak met krullerige hakies, soos in die voorbeeld getoon:

As jy nie 'n skikkingsformule vir jou werk wil gebruik nie, voeg dan nog 'n INDEX() by die formule en druk ENTER, dit sal soos in die voorbeeld lyk:

Hoe hierdie formules werk

Hierdie formule werk op dieselfde manier as die standaard INDEX() MATCH()-formule. Om vir veelvuldige toestande te soek, skep jy eenvoudig veelvuldige Vals en Waar toestande wat die korrekte en verkeerde individuele toestande verteenwoordig. En dan is hierdie voorwaardes van toepassing op alle ooreenstemmende elemente van die skikking. Die formule skakel die Vals en Waar argumente om na onderskeidelik 0 en 1, en voer 'n skikking uit waar 1 die ooreenstemmende waardes is wat in die string gevind is. MATCH() sal die eerste waarde vind wat by 1 pas en dit na die INDEX()-formule deurgee. En dit sal op sy beurt die reeds verlangde waarde in die gespesifiseerde reël uit die gewenste kolom terugstuur.

'n Formule sonder 'n skikking hang af van die vermoë van INDEX() om dit op sy eie te hanteer. Die tweede INDEX() in die formule stem ooreen met vals (0), dus gee dit die hele skikking met daardie waardes deur na die MATCH()-formule. 

Dit is 'n taamlik lang verduideliking van die logika agter hierdie formule. Vir meer inligting lees die artikel "INDEX MATCH met veelvuldige voorwaardes".

GEMIDDELDE, MAKS en MIN in INDEX en MATCH

Excel het sy eie spesiale funksies om gemiddeldes, maksimums en minimums te vind. Maar wat as jy data wil kry uit die sel wat met daardie waardes geassosieer word? In hierdie geval AVERAGE, MAX en MIN moet saam met INDEX en MATCH gebruik word.

INDEX MATCH en MAX

Om die grootste waarde in kolom D te vind en dit in kolom C te vertoon, gebruik die formule: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH en MIN

Om die kleinste waarde in kolom D te vind en dit in kolom C te vertoon, gebruik die volgende formule:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SOEKINDEKS en SLANG

Om die gemiddelde waarde in kolom D te vind en hierdie waarde in C te vertoon:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Afhangende van hoe jou data geskryf is, is die derde argument vir MATCH() óf 1, 0 of -1:

• as die kolomme in stygende volgorde gesorteer is, stel 1 (dan sal die formule die maksimum waarde bereken, wat minder as of gelyk is aan die gemiddelde waarde);
• as die sorteer dalende is, dan -1 (die formule sal die minimum waarde uitvoer wat groter as of gelyk aan die gemiddelde is);
• as die opsoekskikking 'n waarde bevat wat presies gelyk is aan die gemiddelde, stel dit dan op 0. 

 In ons voorbeeld word die populasie in dalende volgorde gesorteer, dus plaas ons -1. En die resultaat is Tokio, aangesien die bevolkingswaarde (13,189 000) die naaste aan die gemiddelde waarde (XNUMX) is.

VLOOKUP() kan ook sulke berekeninge uitvoer, maar slegs as 'n skikkingsformule: VLOOKUP met AVERAGE, MIN en MAX.

INDEX MATCH en ESND/IFERROR

Jy het waarskynlik reeds opgemerk dat as die formule nie die gewenste waarde kan vind nie, dit 'n fout gooi # N / A. U kan die standaardfoutboodskap vervang met iets meer insiggewend. Stel byvoorbeeld die argument in die formule In die XNUMXde:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Met hierdie formule, as jy data invoer wat nie in die tabel is nie, sal die vorm jou die gespesifiseerde boodskap gee.

As jy alle foute wil vang, behalwe vir In die XNUMXde gebruik kan word om IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Iets het verkeerd geloop!")

Maar onthou dat die maskering van foute op hierdie manier nie 'n goeie idee is nie, want standaardfoute rapporteer oortredings in die formule.

Ons hoop dat u ons gids vir die gebruik van die INDEX MATCH()-funksie nuttig gevind het.