Wie verhindere ich, dass VLOOKUP beschädigt wird, nachdem ich einem benannten Bereich eine Spalte hinzugefügt habe?

Ich persönlich bevorzuge die Verwendung einer INDEX-MATCHCombo anstelle von VLOOKUP. Hier ist ein Beispiel aus dem Pokedex, das ich für meine Nichte zusammengestellt habe.

Um Squirtles versteckte Fähigkeiten zu erhalten, würde ich diese Formel verwenden:

=INDEX(F2:F11,MATCH("Squirtle", A2:A11,0),1) 

Dies ergibt das gleiche Ergebnis wie:

=INDEX(A1:G11,MATCH("Squirtle",A1:A11,0),MATCH("Hidden Ability",A1:G1,0)) 

Eine gute Sache von INDEX-MATCH ist, dass Sie in den meisten Fällen nicht den gesamten Datenbereich referenzieren müssen. Daher sollte die erste Formel funktionieren, selbst wenn Sie regelmäßig Spalten und Zeilen zu Ihrem Datenbereich hinzufügen. Es hat auch einen weiteren Vorteil: Da Sie nur 2 eindimensionale Bereiche referenzieren, wird schneller berechnet.

Sie können jedoch trotzdem MATCHmit Ihren VLOOKUPFormeln die Spaltennummer ermitteln.

=VLOOKUP(C1,NAMED_RANGE,MATCH("COLUMN_TEXT",$A$1:$H1,0),0) 

Dabei gilt Folgendes:
$A$1:$H$1Die erste Zeile in Ihrem Daten- / benannten Bereich oder die Zeile, die den Kopfzeilentext enthält.
NAMED_RANGEDer Name für Ihren Datenbereich.
"COLUMN_TEXT"Der Kopfzeilentext der Spalte, die die benötigten Daten
C1enthält, enthält Ihren Nachschlagewert

Edit: Index-Match-Beispiel gemäß Doug's Anfrage hinzugefügt. : D

Sie können die MATCHFunktion verwenden:

Diese Seite hat eine ziemlich gute Erklärung, wie man sie benutzt:
http://www.techonthenet.com/excel/formulas/match.php

Alternativ können Sie die COLUMNFunktion verwenden, um die tatsächliche Spaltennummer zu lesen (wenn Ihre Tabelle an einer anderen Stelle als in Spalte A beginnt, müssen Sie die Nummer der ersten Spalte in Ihrer Formel abziehen). Also zum Beispiel:

 =VLOOKUP(B2,$B$2:$D$300,COLUMN($D$1)-Column($B$1), False) 

Wo Spalte D Ihren Nachschlagewert enthält

Eine alternative Lösung ist die Erstellung einer benutzerdefinierten Funktion, die den Prozess sehr einfach machen sollte.

Hier ist ein Ansatz, der eine Funktion namens erstellt XLOOKUP:

Public Function XLOOKUP(ByRef row As Variant, ByRef column As Variant, ByRef table_array As Variant) As Variant XLOOKUP = CVErr(xlErrNA) Dim xRow As Long, xCol As Long With table_array For xRow = 1 To .Rows.Count If .Cells(xRow, 1).Value = row Then For xCol = 1 To .columns.Count If .Cells(1, xCol).Value = column Then XLOOKUP = .Cells(xRow, xCol).Value Exit Function End If Next Exit Function End If Next End With  End Function 

Sobald Sie es zu Ihrem Projekt hinzufügen, können Sie es wie folgt verwenden:

=XLOOKUP(RowLabel, ColumnLabel, Range)

Sie können Ihren ursprünglichen Lookup auch einfach mit der COLUMN () - Funktion für die Spalte mit dem zurückzugebenden Wert festlegen. In einer einfachen Version befindet sich die Spalte mit dem Suchwert Ihrer Tabelle in Spalte A, und der zurückzugebende Wert befindet sich in einer beliebigen anderen Spalte (Spalte L). Dann benutze:

SPALTE (L: L)

für den Wert, um die Spalte zurückzugeben. Komplizierter, als in der Nachschlagspalte nicht Spalte A? Subtrahieren Sie einfach von oben: (für die Suche in Spalte C)

SPALTE (L: L) -2

Der Wert hier ist, dass das Hinzufügen und Löschen von Spalten Ihre Formel nicht beschädigt, da Excel sie automatisch korrigiert. Eine Spalte hinzufügen? Es wird SPALTE (M: M). Und so zum Löschen einer Spalte. Alles wird von Excel behandelt. Und es ist wirklich leicht zu machen. Spalten wie "WYO"? Excel ist es egal. Suchen Sie die Spaltennummer Ihrer Suchspalte mit einer schnellen COLUMN () - Formel in dieser Spalte. Dann haben Sie den zu subtrahierenden Wert. Einfach.

Zweifellos weniger leistungsfähig als Index / Match und Übung macht dort den Meister. Aber es ist ein schnelles Ergebnis, das anhält. Und Sie können es anderen schnell beibringen, während Sie Ihre Index- / Match-Fähigkeiten verbessern. Es ist auch nicht so intuitiv wie das Verwenden von Named Ranges, aber oft kann man es nicht (Tabellen jemand?). (Und es kann auch der zugrunde liegende Wert für den benannten Bereich sein.)

Wenn Sie also einen schnellen, einfachen und mit einfachen Spaltenwechseln schwer zu brechenden Befehl wünschen, wird die Referenz beibehalten, wenn Sie die else-column ausschneiden und erneut einfügen, und nicht von ihrer neuen Position aus relativ aussehen. Aber für diese Art von Arbeit denke ich ein definitives Plus), dann ist dies eine Lösung zum Nachdenken.

Wenn Sie die Spaltenbereiche in Ihrer Suchformel benennen, funktionieren die Formeln noch gut, wenn Sie Spalten hinzufügen oder löschen.