Voor degenen onder u die goed thuis zijn in Excel , u bent waarschijnlijk zeer bekend met de functie VERT.ZOEKEN^(VLOOKUP) . De functie VERT.ZOEKEN^(VLOOKUP) wordt gebruikt om een ​​waarde in een andere cel te vinden op basis van overeenkomende tekst in dezelfde rij.

Als je nog niet bekend bent met de VERT.ZOEKEN-^(VLOOKUP) functie, kun je mijn vorige bericht lezen over het gebruik van VERT.ZOEKEN in Excel^{(how to use VLOOKUP in Excel)} .

Hoe krachtig het ook is, VERT.ZOEKEN^(VLOOKUP) heeft een beperking op hoe de overeenkomende referentietabel moet worden gestructureerd om de formule te laten werken.

Dit artikel laat u de beperking zien waar VERT.ZOEKEN^(VLOOKUP) niet kan worden gebruikt en introduceert een andere functie in Excel genaamd INDEX-MATCH die het probleem kan oplossen.

INDEX MATCH Excel Voorbeeld

Met behulp van het volgende voorbeeld Excel-spreadsheet hebben we een lijst met de naam van de autobezitter en de naam van de auto. In dit voorbeeld proberen we de  auto-ID^{(Car ID)} te pakken te krijgen op basis van het automodel^{(Car Model)} dat onder meerdere eigenaren wordt vermeld, zoals hieronder wordt weergegeven:

Op een apart blad met de naam CarType hebben we een eenvoudige autodatabase met de  ID ^{(Car Model)},^(ID) het automodel en de kleur^(Color) .

Met deze tabelconfiguratie kan de  functie VERT.ZOEKEN^(VLOOKUP) alleen werken als de gegevens die we willen ophalen zich bevinden in de kolom rechts van wat we proberen te matchen ( veld Automodel^{(Car Model )} ).

Met andere woorden, met deze tabelstructuur, aangezien we deze proberen te matchen op basis van het automodel^{(Car Model)} , is de enige informatie die we kunnen krijgen Kleur^{(Color )} (geen ID aangezien de ID -  kolom zich aan de linkerkant van de  automodel-^{(Car Model )} kolom bevindt.)

Dit komt omdat met VERT.ZOEKEN^(VLOOKUP) , de opzoekwaarde in de eerste kolom moet verschijnen en de opzoekkolommen aan de rechterkant. In ons voorbeeld wordt aan geen van deze voorwaarden voldaan.

Het goede nieuws is dat INDEX-MATCH  ons hierbij kan helpen. In de praktijk is dit eigenlijk het combineren van twee Excel - functies die afzonderlijk kunnen werken: INDEX- functie en MATCH- functie.

Voor de toepassing van dit artikel zullen we het echter alleen hebben over de combinatie van de twee met als doel de functie van VERT.ZOEKEN^(VLOOKUP) te repliceren .

De formule kan in het begin een beetje lang en intimiderend lijken. Als je het echter een paar keer hebt gebruikt, leer je de syntaxis uit je hoofd.

Dit is de volledige formule in ons voorbeeld:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Hier is de uitsplitsing voor elke sectie

=INDEX( – De “=” geeft het begin van de formule in de cel aan en INDEX is het eerste deel van de Excel - functie die we gebruiken.

CarType!$A$2:$A$5 – de kolommen op het blad CarType  waar de gegevens staan ​​die we willen ophalen. In dit voorbeeld de ID  van elk automodel.^{(Car Model.)}

MATCH( – Het tweede deel van de Excel - functie die we gebruiken.

B4 – De cel die zoektekst bevat die we gebruiken ( automodel^{(Car Model)} ) .

CarType!$B$2:$B$5 – De kolommen op het blad CarType   met de gegevens die we zullen gebruiken om te matchen met de zoektekst.

0)) – Om aan te geven dat de zoektekst exact moet overeenkomen met de tekst in de overeenkomende kolom (dwz CarType!$B$2:$B$5 ). Als de exacte overeenkomst niet wordt gevonden, retourneert de formule #N/A .

Opmerking: onthoud de dubbele sluithaak aan het einde van deze functie "))" en de komma's tussen de argumenten.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Persoonlijk ben ik weggegaan van VERT.ZOEKEN^(VLOOKUP) en gebruik nu INDEX-MATCH omdat het meer kan doen dan VERT.ZOEKEN^(VLOOKUP) .

De INDEX-MATCH- functies hebben ook andere voordelen in vergelijking met VERT.ZOEKEN^(VLOOKUP) :

1. Snellere berekeningen^{(Faster Calculations)}

Wanneer we werken met grote datasets waarbij de berekening zelf lang kan duren vanwege de vele VERT.ZOEKEN^(VLOOKUP) - functies, zult u merken dat zodra u al deze formules vervangt door INDEX-MATCH , de algehele berekening sneller wordt berekend.

1. Het is niet nodig om relatieve kolommen te tellen^{(No Need to Count Relative Columns)}

Als onze referentietabel de sleuteltekst bevat die we willen doorzoeken in kolom C en de gegevens die we nodig hebben in kolom AQ staan, moeten we weten / tellen hoeveel kolommen er tussen kolom C en kolom AQ staan ​​bij het gebruik van VERT.ZOEKEN^(VLOOKUP) .

Met de  INDEX-MATCH- functies kunnen we direct de indexkolom selecteren (dwz kolom AQ) waar we de gegevens moeten ophalen en de kolom selecteren die moet worden gekoppeld (dwz kolom C).

1. Het ziet er ingewikkelder uit^{(It Looks More Complicated)}

VERT.ZOEKEN^(VLOOKUP) is tegenwoordig heel gewoon, maar niet veel mensen weten hoe ze de INDEX-MATCH-functies samen kunnen gebruiken.

De langere tekenreeks in de INDEX-MATCH-^{(INDEX-MATCH)} functie zorgt ervoor dat u eruitziet als een expert in het omgaan met complexe en geavanceerde Excel - functies. Genieten van!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of you who are well-νersed in Excel, you are most likely very familiar with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• Hoe #N/A-fouten in Excel-formules zoals VERT.ZOEKEN op te lossen?

• Hoe lege regels in Excel te verwijderen

• De functie voor het uitspreken van cellen in Excel gebruiken

• Een Excel-werkblad invoegen in een Word-document

• De What-If-analyse van Excel gebruiken

• Basis gegevenssortering in één kolom en meerdere kolommen in Excel-spreadsheets

• Een Excel-bestand delen voor eenvoudige samenwerking

• CSV of TSV invoegen in een Excel-werkblad

• Snel meerdere rijen invoegen in Excel

• Afhankelijkheden traceren in Excel

• Centreer uw werkbladgegevens in Excel om af te drukken

• Hoe een rij in Excel te repareren

• Hoe de Z-score in Excel te berekenen

• Waarom u benoemde bereiken in Excel zou moeten gebruiken?

• Hoe gebruik je Flash Fill in Excel?

• 5 manieren om tekst naar getallen in Excel te converteren

• Wat is een VBA-array in Excel en hoe programmeer je er een?

• Hoe rasterlijnen in Excel te verwijderen

• 2 manieren om de transponeerfunctie van Excel te gebruiken

• Excel verbinden met MySQL