Say Goodbye to VLOOKUP und Hallo zu INDEX-MATCH

VLOOKUP ist eine leistungsfähige und vielgenutzte Excel-Funktion. Wenn Sie nicht vertraut sind, findet es einen Lookup-Wert in einer Tabelle und gibt den entsprechenden Wert einer anderen Spalte. Dies ist für die Suche nach Informationen Bezug genommen wird. Im Beispielbild oben, Name ist der Lookup-Wert, und es gibt den passenden Gehalt. Wenn Sie ein Power-Excel-Benutzer sind, sind Sie wahrscheinlich sehr vertraut mit dieser Funktion. Wenn nicht, ist hier, wie es funktioniert:

• = VLOOKUP (Such. Table_array. Spaltenindex, Bereich_Verweis))
  • Lookup_value - Der Wert, den Sie in der Lookup-Wert Spalte finden wollen
  • Lookup_value - Die Bereichstabelle Spalten sowohl für den Nachschlag enthalten und Werte zurückgeben
  • Spaltenindex - Die Indexnummer für die Spalte der Rückgabewert enthält,
  • Bereich_Verweis - Die Art des Spiels: Nächste Less Than (TRUE) oder Exact (FALSCH) [optional]

VLOOKUP, obwohl mächtig und nützlich, ist nicht ohne Einschränkungen. In der Tat kann es wegen einiger unerwarteter Effekte und Verhaltensweisen gefährlich sein. Wenn Sie SVERWEIS verwenden, lesen Sie bitte auf über diese Themen zu lernen. Dies gilt gleichermaßen für SVERWEIS Schwester Funktion HLOOKUP (die anstelle der vertikalen Lookups horizontalen Lookups) und andere ähnliche Funktion, LOOKUP. Ich habe ein sichereres und flexiblere Alternative Lookup-Verfahren zur Verfügung gestellt, die ich anstelle von SVERWEIS praktisch überall benutzen.

Zuerst werde ich einige VLOOKUP Limitierungen erklären:

Nun, hier sind einige Gefahren von SVERWEIS:

1. Standardmäßig verwendet SVERWEIS ungefähre Übereinstimmung. Wenn dies ist, wie Sie es funktionieren soll, dann groß ... Doch in vielen Fällen möchten Sie eine genaue Wert zurückgegeben. Es gibt keinen Hinweis darauf, es wird eine nächste Übereinstimmung Ergebnis Kommissionierung. Wenn Sie dieses Verhalten nicht wollen (die meiste Zeit ist, habe ich gefunden, ...), Sie erinnern sich das Bereich_Verweis Argument in der Formel auf FALSCH explizit zu setzen. Bereich_Verweis ist optional und nicht ein sehr descrive Namen dieser Funktion, so ist es oft übersehen. Diese Marotte wird durch die zweite Gefahr erbittert ...
2. SVERWEIS kann falsche Ergebnisse liefern, wenn die Tabelle nicht in aufsteigender Reihenfolge sortiert ist! Dies ist ein Problem, wenn Sie die ungefähre Übereinstimmung Funktion zu verwenden, die standardmäßig TRUE ist. Grundsätzlich beginnt VLOOKUP an der Spitze der Tabelle und geht nach unten Zeile für Zeile, bis zu bekommt auf eine valie weniger als oder gleich den Referenzwert. Wenn Ihre Tabelle in aufsteigender Reihenfolge nicht sortiert wird, kann dies zu falschen Ergebnissen führen, da die Formel Verarbeitung stoppt Zeilen unmittelbar nach einem Suche nach „Spiel.“

Hinreichend Angst? Überprüfen Sie Ihre Tabellen für Probleme jetzt. Die Antwort auf diese Probleme und Einschränkungen ist die INDEX-MATCH-Lookup-Methode. Dieses Verfahren verwendet zwei Funktionen zusammen eine sichere und flexible Lookup-Funktion zur Verfügung zu stellen. Hier ist, wie jede Funktion funktioniert unabhängig:

• INDEX gibt den Wert an dem Schnittpunkt einer Zeile und eine Spalte in einem gegebenen Bereich.
  • Formel: = INDEX (.. Array row_num Column_num)
    • Array - Der Bereich von Zellen
    • Row_num - die Zeilendaten zurückzukehren, aus
    • Column_num - Die Spalte, die Daten von [optional] zurückzukehren
• MATCH gibt eine Position eines Elements in einem Array, die einen Wert übereinstimmt.
  • Formel: = MATCH (.. Suchkriterium Suchmatrix match_type)
    • Lookup_value - Der Wert, den Sie in dem Lookup-Wert Array finden wollen
    • Suchmatrix - Der Bereich enthält Nachschlagewerte
    • Match_type - Exact (0), die Nähe Weniger als (-1) oder Nächster Größer als (1) [optional]

die beiden Funktionen vereint sind Sie effektiv in der Lage, um die Grenzen und Gefahren von SVERWEIS zu schlagen. Sie können einen Nachschlag erstellen, die Sie auf die Nachschlag-Spalte und Rückgabewert Spalt völlig unabhängig, und auch die Kontrolle über die Natur der ungefähren Übereinstimmung angeben kann, wenn nicht exakt. Die Arrays sind Bereiche und Sie können spezifische Column_num anstelle von row_num, was bedeuten Sie Spalten mit nur nicht darauf beschränkt sind; dies kann anstelle von HLOOKUP verwendet werden. Es hat immer noch die ungefähre Übereinstimmung Funktion als nächste „kleiner als“ standardmäßig und trägt die gleiche Art Warnungen wie VLOOKUP. Hier ist, wie die kombinierte Funktion funktioniert:

• = INDEX (Return_value_range. MATCH (Such. Lookup_value_range. Match_type))
  • Return_value_range - Der Bereich, der die Rückgabewerte hält
  • Lookup_value - Der Wert, den Sie in dem Lookup-Wert Array finden wollen
  • Lookup_value_range - Der Bereich enthält Nachschlagewerte
  • Match_type - Exact (0), Nächster Größer als (-1) oder die Nähe Weniger als (1)

Genießen Sie, und verwenden Sie bei guter Gesundheit!

Beispiel einer Formel INDEX-MATCH in einer Excel-Tabelle exponiert

Wie dieser Beitrag? Teilt es!

Hallo Ben. Sehr informativ. Vielen Dank.
obwohl Frage. Ich habe eine Tabelle mit einer Spalte von Zip-Codes (CB2: cb71). Dies sind die Postleitzahlen für ein Paket Abholort. Lieferort Postleitzahlen sind (KK1: dv2).
Die Gebühr für die Abholung und Zustellung (zip zur PLZ) ist in der Tabelle.
Ich habe versucht, dies zu nutzen vlookup tun, auch Index und Spiel. Ich erhalte # N / A Fehler. Wie kann ich die Ladung an den PU-Reißverschluss und del Reißverschluss matchup? Danke, Rick

Große Lösung!
Ben für MVP!

Es gibt einen weiteren großen Vorteil zum Index-Spiel verwendet wird.

Wenn Sie mehrere Abfragen über Tausende von Zeilen von Daten zu tun.

IE - wenn Sie eine Tabelle mit 20 Spalten von Daten hatten und man wollte 5 von ihnen abzurufen, ein gemeinsamer Weg ist 5 VLOOKUPs zu verwenden (und das leicht 10 oder 15 oder 20 sein kann, wenn Sie bedingte Lookups tun) - multiplizieren Sie diese mit 20.000 Datenzeilen und Sie viele Berechnungen tun.

Index / Spiel ermöglicht es Ihnen, eine Lookup (die Zeile) zu tun und dann die Wiederverwendung für alle anderen abgeleiteten Werte. Dies kann erhebliche Performance-Vorteile mit großen Datensatz bringen, da die Lookups die langsamste Berechnung, vor allem mit unsortierten Daten sind.

Row / Col A B
3 ApplePie $ 3.00
4 Heidelbeerkuchen $ 3.00
5 StrawberryPie $ 3.00
6
7 AppleTart $ 4.50
8 BlueberryTart $ 4.50

Bitte um Hilfe!
David

Ich versuche, einen einfachen Index / Spiel zu tun (ich glaube nicht, SVERWEIS kann dies tun) auf eine Reihe von Quardarmetermaße mit einem zugehörigen Preis. Ich dachte nicht, dass das so schwer sein könnte!

Ich bin mit zwei verschiedenen Platten, eine mit allen Daten der Quadratmeterzahl Preise, und die andere als eine Art „Reporting“ Arbeitsblatt. „Data Sheet“ ist, wo die Preise sind, und „Concrete“ ist das, was ich arbeite. Ich habe Bereiche von SF und ein zugehöriges Preis für diesen Bereich.

Spalte S ist der Preis, Spalte Q ist das obere Ende des Bereichs (so -1 gibt den Wert gleich oder kleiner als den High-End-Bereich)

! Eingang in Beton D15 ist diese Formel:

= INDEX ( ‚Datenlisten '$ S17: $ S57, MATCH (A15,' Datenlisten $ Q17: Q57 $, -1))

Es ist nicht die richtigen Daten zurückbringt, und wenn Sie über 500 auf der Referenzzelle gehen, es gibt einen #NA Fehler. Grrrr! Dies sollte nicht so kompliziert sein!

Ich habe gerade versucht, zusammen INDEX / MATCH zu mischen und anzupassen, aber ich habe ein # N / A Fehler ... Hier ist die Formel:

= INDEX ( 'Datenlisten' S17:! S57 (MATCH (Beton A15, 'Datenlisten' F17:! Q57,1) * UND (MATCH (Beton A15, 'Datenlisten' P17: P57, -1 ))))

Klingt wie ein komplexes Thema, David! Ihre Formel sieht gut aus ... Sind Sie wollen die Fähigkeit zu entfernen, für sie die Wildcard-Zeichen zu verwenden, so haben sie eine genaue Übereinstimmung zu tun? Oder versuchen Sie einfach, wie die Arbeit richtig / konsequent eine Wildcard-Suche zu machen?

Ich hatte beide LOOKUP und INDEX / Spiel Methoden verwendet, um einen Wert aus der Referenztabelle unten zu bekommen, um zu versuchen genaue Übereinstimmung value.However zurückzukehren, den Rückgabewert von * 10 ist mit dem einem von * 1 (0,5 statt 1) ​​entspricht, .
Suchvektor Result
(Lookup Range) (Index Range)
* 1 0,5
* 2 3
* 6 0
* 10 1
Ihre Hilfe wäre sehr dankbar!

Interessant. Meine Vermutung ist, dass das Problem hat mit dem Stern (*) in der Zelle zu tun. Das könnte die Formel verursachen als Platzhalter übereinstimmen. Ich schlage vor, Trimmen das Zeichen aus, wenn Sie Ihr INDEX-MATCH oder LOOKUP tun zu vergleichen. Zum Beispiel, hier ist, wie das erste Zeichen von einem Wert in Zelle A1 trimmen:
= RIGHT (A1, LEN (A1) -1)

Ich hoffe, das hilft!

Hallo ... Fest mit einem Problem nach oben ..
In Blatt 1, ich habe Eintrittsdatum einer Rechnung und Benutzernamen.
In Blatt 2 habe ich Benutzernamen und starten und Daten seiner Ferien enden.
Ich möchte verfolgen, wenn dieser Benutzer eine Rechnung eingegeben hat, als er im Urlaub war ...
Ich denke, ich brauche beide übereinstimmen, den Benutzernamen und, wenn das Datum zwischen Start- und Enddatum fallen wird ....
Kann mir jemand helfen ...

Hallo ... Das ist ein interessantes Problem!
Sie können prüfen, ob ein Datum in einen Bereich fällt, eine Formel wie folgt aus:
= IF (AND ([Rechnungsdatum]> [Startdatum], [Rechnungsdatum]

Trackbacks / Pingbacks

Kategorien

In Verbindung stehende Artikel

• VLOOKUP Funktion Formel in Excel 2010 Tutorial

• VLOOKUP Beispiel-Tabelle - Tutorial, Produktivität Portfolio

• Verwenden der Funktion SVERWEIS in Excel 2007