Waarom INDEX en MATCH beter zijn dan VERT.ZOEKEN in Excel

We hebben eerder aan beginners uitgelegd hoe ze de basisfuncties van VERT.ZOEKEN kunnen gebruiken (Engelse VERT.ZOEKEN, de afkorting staat voor "verticale zoekfunctie"). En ervaren gebruikers kregen verschillende meer gecompliceerde formules te zien.

En in dit artikel zullen we proberen informatie te geven over een andere manier van werken met verticaal zoeken.

U vraagt ​​zich misschien af: “Waarom is dit nodig?”. En dit is nodig om alle mogelijke zoekmethoden te tonen. Bovendien verhinderen talrijke VERT.ZOEKEN-beperkingen vaak het verkrijgen van het gewenste resultaat. In dit opzicht is INDEX() MATCH() veel functioneler en diverser, en hebben ze ook minder beperkingen.

Basisprincipes INDEX MATCH

Aangezien het doel van deze handleiding is om te laten zien hoe goed deze functie is, hebben we Laten we eens kijken naar de basisinformatie over de principes van de werking ervan. En we zullen voorbeelden laten zien, en ook overwegen waarom, het is beter dan VERT.ZOEKEN ().

INDEX Functiesyntaxis en gebruik

Deze functie helpt bij het vinden van de gewenste waarde in de opgegeven zoekgebieden op basis van het kolom- of regelnummer. Syntax:

=INDEX(matrix, rijnummer, kolomnummer):

• array – het gebied waarin de zoekopdracht zal plaatsvinden;
• regelnummer – het nummer van de regel die moet worden doorzocht in de opgegeven array. Als het rijnummer niet bekend is, moet het kolomnummer worden opgegeven;
• kolomnummer – het nummer van de kolom die in de opgegeven matrix moet worden gevonden. Als de waarde onbekend is, is een regelnummer vereist.

Een voorbeeld van een eenvoudige formule:

=INDEX(A1:S10,2,3)

De functie zoekt in het bereik van A1 tot C10. De cijfers geven aan van welke rij (2) en kolom (3) de gewenste waarde moet worden weergegeven. Het resultaat is cel C2.

Vrij eenvoudig, toch? Maar als u met echte documenten werkt, heeft u waarschijnlijk geen informatie over kolomnummers of cellen. Daar is de functie MATCH() voor.

MATCH-functiesyntaxis en gebruik

De functie MATCH() zoekt naar de gewenste waarde en toont het geschatte aantal in het opgegeven zoekgebied.

De syntaxis van searchpos() ziet er als volgt uit:

=VERGELIJKEN(waarde om op te zoeken, array om op te zoeken, zoektype)

• zoekwaarde - het nummer of de tekst die moet worden gevonden;
• doorzochte array – het gebied waar de zoekopdracht zal plaatsvinden;
• match type – specificeert of er gezocht moet worden naar de exacte waarde of de waarden die er het dichtst bij liggen:
  • 1 (of geen waarde opgegeven) – retourneert de grootste waarde die gelijk is aan of kleiner is dan de waarde die is opgegeven;
  • 0 – toont een exacte overeenkomst met de gezochte waarde. In de combinatie INDEX() MATCH() heb je bijna altijd een exacte match nodig, dus schrijven we 0;
  • -1 – Toont de kleinste waarde die groter is dan of gelijk is aan de waarde die is opgegeven in de formule. Het sorteren gebeurt in aflopende volgorde.

In het bereik B1:B3 zijn bijvoorbeeld New York, Parijs, Londen geregistreerd. De onderstaande formule toont het getal 3 omdat Londen de derde is op de lijst:

=BLOOTSTELLEN(Londen,B1:B3,0)

Werken met de functie INDEX MATCH 

U bent waarschijnlijk al begonnen het principe te begrijpen waarmee het gezamenlijke werk van deze functies is opgebouwd. Kortom, dan INDEX() zoekt naar de gewenste waarde tussen de opgegeven rijen en kolommen. En MATCH() toont de nummers van deze waarden:

=INDEX(kolom waaruit de waarde wordt geretourneerd, MATCH(waarde om in te zoeken, kolom om in te zoeken, 0))

Vind je het nog steeds moeilijk om te begrijpen hoe het werkt? Misschien verduidelijkt een voorbeeld het beter. Stel je hebt een lijst met wereldhoofdsteden en hun bevolking:

Om de omvang van de bevolking van een bepaalde hoofdstad, bijvoorbeeld de hoofdstad van Japan, te achterhalen, gebruiken we de volgende formule:

=INDEX(C2:C10, VERGELIJKEN(Japan, A2:A10,0))

Uitleg:

• De functie MATCH() zoekt naar de waarde - "Japan" in de array A2:A10 en retourneert het getal 3, omdat Japan de derde waarde in de lijst is. 
• Dit cijfer gaat naarregelnummer” in de formule INDEX() en vertelt de functie om een ​​waarde uit deze rij af te drukken.

Dus de bovenstaande formule wordt de standaardformule INDEX(C2:C10,3). De formule zoekt van cellen C2 tot C10 en retourneert gegevens uit de derde cel in dit bereik, dat wil zeggen C4, omdat het aftellen begint vanaf de tweede rij.

Wilt u de naam van de stad niet voorschrijven in de formule? Schrijf het vervolgens in een willekeurige cel, zeg F1, en gebruik het als een verwijzing in de MATCH()-formule. En je krijgt een dynamische zoekformule:

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

Belangrijk! Aantal regels in reeks INDEX() moet hetzelfde zijn als het aantal rijen in beschouwd als array in MATCH(), anders krijgt u het verkeerde resultaat.

Wacht even, waarom niet gewoon de formule VERT.ZOEKEN() gebruiken?

=VERT.ZOEKEN(F1, A2:C10, 3, Onwaar)

 Wat heeft het voor zin om tijd te verspillen aan het uitzoeken van al deze complexiteiten van INDEX MATCH?

In dit geval maakt het niet uit welke functie u moet gebruiken. Dit is slechts een voorbeeld om te begrijpen hoe de functies INDEX() en MATCH() samenwerken. Andere voorbeelden laten zien waartoe deze functies in staat zijn in situaties waarin VERT.ZOEKEN machteloos is. 

INDEX OVEREENKOMST of VERT.ZOEKEN

Bij het beslissen welke zoekformule ze moeten gebruiken, zijn velen het erover eens dat INDEX() en MATCH() veel beter zijn dan VERT.ZOEKEN. Veel mensen gebruiken echter nog steeds VERT.ZOEKEN(). Ten eerste is VLOOKUP() eenvoudiger en ten tweede begrijpen gebruikers niet alle voordelen van het werken met INDEX() en MATCH(). Zonder deze kennis zal niemand zijn tijd besteden aan het bestuderen van een complex systeem.

Dit zijn de belangrijkste voordelen van INDEX() en MATCH() boven VERT.ZOEKEN():

• Zoek van rechts naar links. VERT.ZOEKEN() kan niet van rechts naar links zoeken, dus de waarden die u zoekt moeten altijd in de meest linkse kolommen van de tabel staan. Maar INDEX() en MATCH() kunnen dit probleemloos aan. In dit artikel leest u hoe het er in de praktijk uitziet: hoe u aan de linkerkant de gewenste waarde vindt.

1. Veilig toevoegen of verwijderen van kolommen. De formule VERT.ZOEKEN() geeft onjuiste resultaten weer bij het verwijderen of toevoegen van kolommen, omdat VERT.ZOEKEN() het exacte kolomnummer nodig heeft om succesvol te zijn. Wanneer kolommen worden toegevoegd of verwijderd, veranderen natuurlijk ook hun nummers. 

En in de formules INDEX() en MATCH() wordt een reeks kolommen opgegeven, geen afzonderlijke kolommen. Hierdoor kunt u veilig kolommen toevoegen en verwijderen zonder dat u de formule telkens hoeft bij te werken.

1. Geen limieten voor zoekvolumes. Als u VERT.ZOEKEN() gebruikt, mag het totale aantal zoekcriteria niet meer dan 255 tekens bevatten, anders krijgt u een #WAARDE! Dus als uw gegevens een groot aantal tekens bevatten, zijn INDEX() en MATCH() de beste optie.
2. Hoge verwerkingssnelheid. Als uw tabellen relatief klein zijn, zult u waarschijnlijk geen verschil merken. Maar als de tabel honderden of duizenden rijen bevat en er dus honderden en duizenden formules zijn, zullen INDEX () en MATCH () veel sneller werken dan VERT.ZOEKEN (). Feit is dat Excel alleen de kolommen verwerkt die in de formule zijn gespecificeerd, in plaats van de hele tabel te verwerken. 

De prestatie-impact van VERT.ZOEKEN() zal vooral merkbaar zijn als uw werkblad een groot aantal formules bevat, zoals VERT.ZOEKEN() en SOM(). Afzonderlijke controles van de functies VERT.ZOEKEN() zijn vereist om elke waarde in een array te ontleden. Excel moet dus een enorme hoeveelheid informatie verwerken en dit vertraagt ​​het werk aanzienlijk.

Formule voorbeelden 

Het nut van deze functies hebben we al bedacht, dus we kunnen overgaan tot het meest interessante deel: het toepassen van kennis in de praktijk.

Formule om van rechts naar links te zoeken

Zoals reeds vermeld, kan VERT.ZOEKEN deze vorm van zoeken niet uitvoeren. Dus als de gewenste waarden niet in de meest linkse kolom staan, zal VERT.ZOEKEN() geen resultaat opleveren. De functies INDEX() en MATCH() zijn veelzijdiger en de locatie van de waarden speelt geen grote rol voor hen om te werken.

We zullen bijvoorbeeld een rangkolom toevoegen aan de linkerkant van onze tabel en proberen te achterhalen welke rang in termen van bevolking de hoofdstad van Ons Land inneemt.

In cel G1 schrijven we de te vinden waarde en gebruiken vervolgens de volgende formule om in het bereik C1:C10 te zoeken en de overeenkomstige waarde van A2:A10 te retourneren:

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

Snel. Als u van plan bent deze formule voor meerdere cellen te gebruiken, zorg er dan voor dat u de bereiken corrigeert met absolute adressering (bijvoorbeeld $A$2: $A$10 en $C$2: 4C$10).

INDEX MEER BLOOTGESTELD MEER BLOOTGESTELD  om in kolommen en rijen te zoeken

In de bovenstaande voorbeelden hebben we deze functies gebruikt als vervanging voor VERT.ZOEKEN() om waarden uit een vooraf gedefinieerd rijbereik te retourneren. Maar wat als u een matrix of dubbelzijdig moet zoeken?

Het klinkt ingewikkeld, maar de formule voor dergelijke berekeningen is vergelijkbaar met de standaard INDEX() MATCH()-formule, met slechts één verschil: de formule MATCH() moet twee keer worden gebruikt. De eerste keer om het rijnummer te krijgen en de tweede keer om het kolomnummer te krijgen:

=INDEX(matrix, MATCH(verticale zoekwaarde, zoekkolom, 0), MATCH(horizontale zoekwaarde, zoekrij, 0))

Laten we de onderstaande tabel bekijken en proberen een formule te maken INDEX() EXPRESS() EXPRESS() om demografische gegevens in een bepaald land voor een geselecteerd jaar weer te geven.

Het doelland staat in cel G1 (verticaal opzoeken) en het doeljaar staat in cel G2 (horizontaal opzoeken). De formule ziet er als volgt uit:

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

Hoe deze formule werkt

Zoals met alle andere complexe formules, zijn ze gemakkelijker te begrijpen door ze op te splitsen in afzonderlijke vergelijkingen. En dan kunt u begrijpen wat elke afzonderlijke functie doet:

• OVEREENKOMST(G1,A2:A11,0) – zoekt naar een waarde (G1) in het bereik A2:A11 en toont het nummer van deze waarde, in ons geval is dit 2;
• ZOEKEN(G2,B1:D1,0) – zoekt naar een waarde (G2) in het bereik B1:D1. In dit geval was het resultaat 3.

De gevonden rij- en kolomnummers worden verzonden naar de corresponderende waarde in de INDEX()-formule:

=INDEX(B2:D11,2,3)

Als resultaat hebben we een waarde die zich in een cel bevindt op de kruising van 2 rijen en 3 kolommen in het bereik B2:D11. En de formule toont de gewenste waarde, die zich in cel D3 bevindt.

Zoek op meerdere voorwaarden met INDEX en MATCH

Als je onze gids voor VERT.ZOEKEN() hebt gelezen, heb je waarschijnlijk meerdere zoekformules geprobeerd. Maar deze zoekmethode heeft één belangrijke beperking: de noodzaak om een ​​hulpkolom toe te voegen.

Maar het goede nieuws is dat Met INDEX() en MATCH() kunt u zoeken naar meerdere voorwaarden zonder dat u uw werkblad hoeft te bewerken of wijzigen.

Hier is de algemene zoekformule met meerdere voorwaarden voor INDEX() MATCH():

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

De notitie: deze formule moet samen met de sneltoets worden gebruikt CTRL+SHIFT+ENTER.

Stel dat u de waarde die u zoekt moet vinden op basis van 2 voorwaarden: Koper и Artikel.

Hiervoor is de volgende formule nodig:

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

In deze formule is C2:C10 het bereik waarin de zoekopdracht zal plaatsvinden, F1 - deze voorwaarde, A2:A10 — is het bereik om de toestand te vergelijken, F2 – voorwaarde 2, V2: V10 – bereik voor vergelijking van conditie 2.

Vergeet niet op de combinatie te drukken aan het einde van het werk met de formule CTRL+SHIFT+ENTER – Excel sluit de formule automatisch af met accolades, zoals in het voorbeeld:

Als u geen matrixformule voor uw werk wilt gebruiken, voeg dan nog een INDEX() toe aan de formule en druk op ENTER, het ziet er dan uit als in het voorbeeld:

Hoe deze formules werken

Deze formule werkt op dezelfde manier als de standaard INDEX() MATCH()-formule. Om naar meerdere voorwaarden te zoeken, maakt u eenvoudig meerdere False en True voorwaarden aan die de juiste en onjuiste individuele voorwaarden vertegenwoordigen. En dan zijn deze voorwaarden van toepassing op alle corresponderende elementen van de array. De formule converteert de argumenten False en True naar respectievelijk 0 en 1, en voert een array uit waarbij 1 de overeenkomende waarden zijn die in de string zijn gevonden. MATCH() vindt de eerste waarde die overeenkomt met 1 en geeft deze door aan de formule INDEX(). En het zal op zijn beurt de reeds gewenste waarde in de opgegeven regel uit de gewenste kolom retourneren.

Een formule zonder een array hangt af van het vermogen van INDEX() om deze zelfstandig te verwerken. De tweede INDEX() in de formule komt overeen met falsy (0), dus het geeft de hele array met die waarden door aan de MATCH()-formule. 

Dit is een nogal lange uitleg van de logica achter deze formule. Lees voor meer informatie het artikel “INDEX MATCH met meerdere voorwaarden.

GEMIDDELDE, MAX en MIN in INDEX en MATCH

Excel heeft zijn eigen speciale functies voor het vinden van gemiddelden, maxima en minima. Maar wat als u gegevens wilt ophalen uit de cel die aan die waarden is gekoppeld? In dit geval AVERAGE, MAX en MIN moeten worden gebruikt in combinatie met INDEX en MATCH.

INDEX MATCH en MAX

Gebruik de formule om de grootste waarde in kolom D te vinden en weer te geven in kolom C: 

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

INDEX MATCH en MIN

Gebruik de volgende formule om de kleinste waarde in kolom D te vinden en weer te geven in kolom C:

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

ZOEKINDEX en SERPENT

Om de gemiddelde waarde in kolom D te vinden en deze waarde in C weer te geven:

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

Afhankelijk van hoe uw gegevens zijn geschreven, is het derde argument voor MATCH() 1, 0 of -1:

• als de kolommen in oplopende volgorde zijn gesorteerd, stelt u 1 in (dan berekent de formule de maximale waarde, die kleiner is dan of gelijk is aan de gemiddelde waarde);
• als de sortering aflopend is, dan -1 (de formule geeft de minimumwaarde weer die groter is dan of gelijk is aan het gemiddelde);
• als de opzoekarray een waarde bevat die exact gelijk is aan het gemiddelde, stel deze dan in op 0. 

 In ons voorbeeld is de populatie in aflopende volgorde gesorteerd, dus zetten we -1. En het resultaat is Tokio, aangezien de populatiewaarde (13,189) het dichtst bij de gemiddelde waarde (000) ligt.

VERT.ZOEKEN() kan dergelijke berekeningen ook uitvoeren, maar alleen als matrixformule: VERT.ZOEKEN met GEMIDDELDE, MIN en MAX.

INDEX MATCH en ESND/IFERROR

Je hebt waarschijnlijk al gemerkt dat als de formule de gewenste waarde niet kan vinden, er een fout optreedt # N / A. U kunt de standaardfoutmelding vervangen door iets meer informatiefs. Stel bijvoorbeeld het argument in de formule in in de XNUMXe:

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

Als u met deze formule gegevens invoert die niet in de tabel staan, geeft het formulier u het opgegeven bericht.

Als je alle fouten wilt opvangen, dan behalve in de XNUMXe kan worden gebruikt IFFOUT:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Er is iets misgegaan!")

Maar onthoud dat het op deze manier maskeren van fouten geen goed idee is, omdat standaardfouten overtredingen in de formule melden.

We hopen dat u onze handleiding voor het gebruik van de functie INDEX MATCH() nuttig vond.