Diejenigen unter Ihnen, die sich mit Excel auskennen , sind höchstwahrscheinlich mit der SVERWEIS-^(VLOOKUP) Funktion bestens vertraut. Die VLOOKUP- Funktion wird verwendet, um einen Wert in einer anderen Zelle basierend auf übereinstimmendem Text in derselben Zeile zu finden.

Wenn Sie die SVERWEIS-^(VLOOKUP) Funktion noch nicht kennen, können Sie sich meinen vorherigen Beitrag zur Verwendung von SVERWEIS in Excel ansehen^{(how to use VLOOKUP in Excel)} .

So leistungsfähig es auch ist, SVERWEIS^(VLOOKUP) hat eine Einschränkung, wie die übereinstimmende Referenztabelle strukturiert sein muss, damit die Formel funktioniert.

Dieser Artikel zeigt Ihnen die Einschränkung, bei der SVERWEIS^(VLOOKUP) nicht verwendet werden kann, und führt eine andere Funktion in Excel namens INDEX-MATCH ein , die das Problem lösen kann.

INDEX-VERGLEICH Excel-Beispiel

Unter Verwendung der folgenden Beispiel-Excel -Tabelle haben wir eine Liste mit dem Namen des Autobesitzers und dem Autonamen. In diesem Beispiel versuchen wir, die  Auto-ID^{(Car ID)} basierend auf dem Automodell^{(Car Model)} zu ermitteln, das unter mehreren Besitzern aufgeführt ist, wie unten gezeigt:

Auf einem separaten Blatt namens CarType haben wir eine einfache Autodatenbank mit  ID ^{(Car Model)},^(ID) Automodell und Farbe^(Color) .

Bei diesem Tabellenaufbau kann die  SVERWEIS-^(VLOOKUP) Funktion nur funktionieren, wenn sich die Daten, die wir abrufen möchten, in der Spalte rechts von dem befinden, was wir abzugleichen versuchen ( Feld „ Automodell^{(Car Model )} “ ).

Mit anderen Worten, mit dieser Tabellenstruktur, da wir versuchen, sie basierend auf dem Automodell abzugleichen^{(Car Model)} , ist die einzige Information, die wir erhalten können, die Farbe^{(Color )} (nicht ID , da sich die ID -  Spalte links von der  Spalte Automodell^{(Car Model )} befindet .)

Denn bei SVERWEIS^(VLOOKUP) muss der Nachschlagewert in der ersten Spalte erscheinen und die Nachschlagespalten müssen rechts stehen. Keine dieser Bedingungen ist in unserem Beispiel erfüllt.

Die gute Nachricht ist, dass INDEX-MATCH  uns dabei helfen kann, dies zu erreichen. In der Praxis kombiniert dies tatsächlich zwei Excel- Funktionen, die einzeln arbeiten können: INDEX- Funktion und MATCH- Funktion.

Für die Zwecke dieses Artikels werden wir jedoch nur über die Kombination der beiden sprechen, um die Funktion von SVERWEIS nachzubilden^(VLOOKUP) .

Die Formel kann zunächst etwas lang und einschüchternd wirken. Wenn Sie es jedoch mehrmals verwendet haben, werden Sie die Syntax auswendig lernen.

Dies ist die vollständige Formel in unserem Beispiel:

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

Hier ist die Aufschlüsselung für jeden Abschnitt

=INDEX( – Das “=” zeigt den Anfang der Formel in der Zelle an und INDEX ist der erste Teil der Excel - Funktion, die wir verwenden.

CarType!$A$2:$A$5 – die Spalten auf dem Blatt CarType ,  in denen die Daten enthalten sind, die wir abrufen möchten. In diesem Beispiel die ID  jedes Automodells.^{(Car Model.)}

MATCH( – Der zweite Teil der Excel- Funktion, die wir verwenden.

B4 – Die Zelle, die den ^(B4)Suchtext^{(Car Model)} enthält, den wir verwenden ( Automodell ) .

CarType!$B$2:$B$5 – Die Spalten auf dem Blatt CarType   mit den Daten, die wir verwenden, um sie mit dem Suchtext abzugleichen.

0)) – Um anzuzeigen, dass der Suchtext genau mit dem Text in der übereinstimmenden Spalte übereinstimmen muss (dh CarType!$B$2:$B$5 ). Wenn die genaue Übereinstimmung nicht gefunden wird, gibt die Formel #N/A zurück .

Hinweis: Denken Sie an die doppelte schließende Klammer am Ende dieser Funktion „))“ und die Kommas zwischen den Argumenten.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Persönlich habe ich mich von SVERWEIS^(VLOOKUP) entfernt und verwende jetzt INDEX-MATCH, da es mehr als SVERWEIS kann^(VLOOKUP) .

Die INDEX-MATCH- Funktionen haben gegenüber VLOOKUP noch weitere Vorteile :

1. Schnellere Berechnungen^{(Faster Calculations)}

Wenn wir mit großen Datensätzen arbeiten, bei denen die Berechnung selbst aufgrund vieler VLOOKUP- Funktionen lange dauern kann, werden Sie feststellen, dass die Gesamtberechnung schneller berechnet wird , sobald Sie alle diese Formeln durch INDEX-MATCH ersetzen.^{(INDEX-MATCH)}

1. Keine Notwendigkeit, relative Spalten zu zählen^{(No Need to Count Relative Columns)}

Wenn unsere Referenztabelle den Schlüsseltext enthält, den wir in Spalte C suchen möchten, und die Daten, die wir abrufen müssen, sich in Spalte AQ befinden, müssen wir wissen/zählen, wie viele Spalten zwischen Spalte C und Spalte AQ liegen, wenn Sie VLOOKUP^(VLOOKUP) verwenden .

Mit den  INDEX-MATCH- Funktionen können wir direkt die Indexspalte (dh Spalte AQ) auswählen, in der wir die Daten abrufen müssen, und die zu vergleichende Spalte auswählen (dh Spalte C).

1. Es sieht komplizierter aus^{(It Looks More Complicated)}

SVERWEIS^(VLOOKUP) ist heutzutage weit verbreitet, aber nicht viele wissen, wie man die INDEX-MATCH-Funktionen zusammen verwendet.

Die längere Zeichenfolge in der INDEX-MATCH- Funktion trägt dazu bei, dass Sie wie ein Experte im Umgang mit komplexen und erweiterten Excel - Funktionen aussehen. Genießen!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of you who are well-versed 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

• So beheben Sie #NV-Fehler in Excel-Formeln wie SVERWEIS

• So löschen Sie leere Zeilen in Excel

• So verwenden Sie die Speak Cells-Funktion von Excel

• So fügen Sie ein Excel-Arbeitsblatt in ein Word-Dokument ein

• So verwenden Sie die Was-wäre-wenn-Analyse von Excel

• Grundlegende einspaltige und mehrspaltige Datensortierung in Excel-Tabellen

• So reparieren Sie eine Zeile in Excel

• So verschieben Sie Spalten in Excel

• Ein fortgeschrittener VBA-Leitfaden für MS Excel

• So erstellen Sie eine einfache Pivot-Tabelle in Excel

• Was ist ein VBA-Array in Excel und wie man eines programmiert

• So entfernen Sie Gitternetzlinien in Excel

• Verwenden Sie dynamische Bereichsnamen in Excel für flexible Dropdowns

• Gruppieren Sie Zeilen und Spalten in einem Excel-Arbeitsblatt

• Verbinden von Excel mit MySQL

• 3 Möglichkeiten, eine Zelle in Excel zu teilen

• So schreiben Sie eine IF-Formel/Anweisung in Excel

• Warum Sie benannte Bereiche in Excel verwenden sollten

• 4 Möglichkeiten, ein Häkchen in Excel zu verwenden

• So führen Sie Zellen, Spalten und Zeilen in Excel zusammen