SVERWEIS mit Dynamic Col Index
In link: / formulas-and-functions-Introduction-of-vlookup-function [die VLOOKUP-Funktion] definieren wir häufig col_index_no static. Wir codieren es fest in der VLOOKUP-Formel, wie VLOOKUP (id, data, 3,0). Das Problem tritt auf, wenn wir eine Spalte in Daten einfügen oder löschen. Wenn wir eine Spalte vor oder nach der 3. Spalte entfernen oder hinzufügen, bezieht sich die 3. Spalte nicht mehr auf die beabsichtigte Spalte. Dies ist ein Problem. Andere ist, wenn Sie mehrere Spalten suchen müssen. Sie müssen den Spaltenindex in jeder Formel bearbeiten. Einfaches Einfügen von Kopien hilft nicht.
Aber wie wäre es, wenn Sie VLOOKUP anweisen können, die Überschriften zu überprüfen und nur den passenden Überschriftenwert zurückzugeben. Dies wird als bidirektionaler VLOOKUP bezeichnet.
Wenn ich beispielsweise eine VLOOKUP-Formel für die Markierungsspalte habe, sollte VLOOKUP in den Daten nach Markierungsspalten suchen und Werte aus dieser Spalte zurückgeben. Dies wird unser Problem lösen.
Hmm … Okay, wie machen wir das? Durch Verwendung von „die Match-Funktion“ innerhalb der „VLOOKUP-Funktion„.
Generische Formel
=VLOOKUP(lookup_value,table_array,MATCH(lookup_heading,table_headings,0),0)
Lookup_value: Der Lookup-Wert in der ersten Spalte von table_array.
Table_array: Der Bereich, in dem Sie eine Suche durchführen möchten. ZB A2, D10.
Lookup_heading: Die Überschrift, die Sie in den Überschriften von table_array suchen möchten.
Table_headings: Referenz der Überschriften im Tabellenarray. Z.B. Wenn die Tabelle A2, D10 und Überschriften am oberen Rand jeder Spalte ist, dann ist es A1: D1.
Jetzt wissen wir also, was wir für den dynamischen col_index benötigen. Lassen Sie uns anhand eines Beispiels alles klären.
Dynamisches VLOOKUP-Beispiel In diesem Beispiel haben wir diese Tabelle, die Daten von Schülern im Bereich A4: E16 enthält.
Mit Rolle Nr. Und Überschrift möchte ich Daten aus dieser Tabelle abrufen. In diesem Fall möchte ich in Zelle H4 Daten von Rolle Nr. In Zelle G4 und von Überschrift in H3 erhalten. Wenn ich die Überschrift ändere, sollten Daten aus dem jeweiligen Bereich in Zelle H4 abgerufen werden.
Schreiben Sie diese Formel in Zelle H4
=VLOOKUP(G4,B4:E16,MATCH(H3,B3:E3,0),0)
Da unser Tabellenarray B4: E16 ist, wird unser Überschriftenarray zu B3: E3.
Hinweis: Wenn Ihre Daten gut strukturiert sind, haben Spaltenüberschriften die gleiche Anzahl von Spalten und es ist die erste Zeile in der Tabelle.
Wie es funktioniert:
Der Hauptteil ist also die automatische Auswertung der Spaltenindexnummer.
Dazu haben wir die MATCH-Funktion verwendet.
MATCH (H3, B3: E3,0): Da H3 „student“ enthält, gibt MATCH 2 zurück.
Wenn H3 es mit „Note“ bewertet hätte, hätte es 4 zurückgegeben und so weiter. Die VLOOKUP-Formel hat endlich die col_index_num.
=VLOOKUP(G4,B4:E16,2,0)
Wie wir wissen, ist der MATCH
Die Funktion gibt die Indexnummer eines bestimmten Werts im angegebenen eindimensionalen Bereich zurück. Daher sucht link: / lookup-formulas-excel-match-function [MATCH] nach jedem in H3 geschriebenen Wert im Bereich B3: E3 und gibt seine Indexnummer zurück.
Wenn Sie jetzt die Überschrift in H3 ändern, wenn es sich um Überschriften handelt, gibt diese Formel einen Wert aus der jeweiligen Spalte zurück. Andernfalls wird ein # N / A-Fehler angezeigt.
VLOOKUP in mehreren Spalten schnell
Im obigen Beispiel brauchten wir die Antwort von einem Spaltenwert. Was aber, wenn Sie mehrere Spalten gleichzeitig erhalten möchten? Wenn Sie die obige Formel kopieren, werden Fehler zurückgegeben. Wir müssen einige kleinere Änderungen daran vornehmen, um es portabel zu machen.
Verwenden von link: / excel-range-name-absolute-reference-in-excel [Absolute Referenzen] mit VLOOKUP Schreiben Sie die folgende Formel in Zelle H2.
=VLOOKUP($G2,$B$2:$E$14,MATCH(H$1,$B$1:$E$1,0),0)
Kopieren Sie nun H2 in alle Zellen im Bereich H2: J6, um es mit Daten zu füllen.
Wie es funktioniert:
Hier habe ich „absolute Referenz“ für jeden Bereich mit Ausnahme der Zeile im Suchwert für VLOOKUP ($ G2)
angegeben und Spalte in lookup_value für MATCH (H $ 1).
$ G2: Dadurch kann sich die Zeile beim Kopieren nach unten nach dem Suchwert für die VLOOKUP-Funktion ändern, die Spalte kann jedoch beim Kopieren nach rechts geändert werden. Dadurch sucht VLOOKUP nur mit der relativen Zeile nach Id aus der G-Spalte.
In ähnlicher Weise ermöglicht H $ 1, dass sich die Spalte beim horizontalen Kopieren ändert und die Zeile beim Kopieren nach unten einschränkt.
Benannte Bereiche verwenden
Das obige Beispiel funktioniert gut, aber es wird schwierig, diese Formel zu lesen und zu schreiben. Und das ist überhaupt nicht tragbar. Dies kann durch benannte Bereiche vereinfacht werden.
Wir werden hier zuerst einige Namen nennen. In diesem Beispiel habe ich $ B $ 2: $ E $ 14: als Daten $ B $ 1: $ E $ 1: als Überschriften H $ 1: Nennen Sie es als Überschrift. Machen Sie die Spalten relativ. Wählen Sie dazu H1. Drücken Sie STRG + F3, klicken Sie auf Neu, und entfernen Sie im Abschnitt Bezieht sich auf ‚$‘ von der Vorderseite von H.
$ G2: Nennen Sie es auch RollNo. Dieses Mal wird die Zeile relativ, indem ‚$‘ von der Vorderseite von 2 entfernt wird.
Wenn Sie nun alle Namen auf dem Blatt haben, schreiben Sie diese Formel an eine beliebige Stelle in der Excel-Datei. Es wird immer die richtige Antwort bekommen.
=VLOOKUP(RollNo,Data, MATCH(Heading, Headings,0),0)
Jeder kann das lesen und verstehen.
Mit diesen Methoden können Sie also col_index_num dynamisieren. Lassen Sie mich im Kommentarbereich unten wissen, ob dies hilfreich war.
Verwandte Artikel:
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Verwendung der VLOOKUP-Funktion in Excel] link: Relative% 20und% 20Absolute% 20Reference% 20in% 20Excel [Relative und absolute Referenz in Excel]
link: / excel-bereichsname-alles-über-benannte-bereiche-in-excel [Benannte Bereiche in Excel]
link: / lookup-formulas-how-to-vlookup-from-different-excel-sheet [Wie man aus verschiedenen Excel-Tabellen VLOOKUP]
link: / lookup-formulas-vlookup-multiple-values [VLOOKUP Multiple Values]
Beliebte Artikel
link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [50 Excel-Verknüpfung zur Steigerung Ihrer Produktivität]: Schneller bei Ihrer Aufgabe. Mit diesen 50 Verknüpfungen können Sie noch schneller in Excel arbeiten.
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Verwendung der VLOOKUP-Funktion in Excel]: Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die zum Nachschlagen von Werten aus verschiedenen Bereichen verwendet wird und Blätter.
link: / tips-countif-in-microsoft-excel [Verwendung der COUNTIF-Funktion in Excel]: Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]: Dies ist eine weitere wichtige Dashboard-Funktion. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.