Ich habe zwei Excel-Tabellen, Tabelle A und Tabelle B. Tabelle A enthält die Spalten „Kunden-ID“, „Postleitzahl“, „Kundenname“ und mehrere andere, die dieselben Spaltennamen wie die Spalten in Tabelle B haben. Ich möchte eine Formel erstellen, idealerweise mit strukturierter Referenzierung, sodass die Reihenfolge der Spalten in Tabelle B irrelevant ist, die den Wert in Tabelle B nachschlägt, der mit der Kunden-ID der Zeile in Tabelle A übereinstimmt, in der ich mich befinde, und mit dem Spaltenwert der Spalte, in der ich mich befinde.
Wenn sich meine Formel beispielsweise in der dritten Spalte der Tabelle A und in einer Zeile mit der Kunden-ID „123“ befindet, möchte ich, dass sie ihren eigenen Spaltennamen (Kundenname) überprüft und den Wert für den Kundennamen nachschlägt, wo die Kunden-ID = „123“ in Tabelle B ist.
Die folgende Formel funktioniert gut für die Spalte „Kundenname“:
=INDEX(TableB[Customer Name], MATCH([@[Customer Number]], TableB[Customer Number], 0))
aber ich möchte in der Lage sein, eine einzelne Formel zu erstellen, die den Teil [Kundenname] dynamisch durch den Namen der Spalte ersetzt, in der ich mich befinde, sodass ich ihn einfach in alle Spalten kopieren kann. Ich habe versucht, die Referenz mit #Headers und indirekt zu erstellen, aber ich erhalte einen Ref-Fehler:
=INDIRECT("INDEX(TableB["&[#Headers]&"], MATCH([@[Customer Number]], TableB[Customer Number], 0))")
Antwort1
INDEX MATCHist der richtige Ansatz, Sie müssen nur bei der Strukturierung vorsichtig sein.
Tabelle A links. Tabelle B rechts. Wir verwenden [Kundennummer], um [Postleitzahl] nachzuschlagen.
Dies ist die einzutragende Formel D2:
=INDEX(TableB,MATCH([@[Customer Number]],TableB[Customer Number],0),MATCH(D$1,TableB[#Headers],0))
INDEXgibt, wie Sie wissen, den Wert einer Zelle am Schnittpunkt einer Zeile und einer Spalte zurück. MATCHgibt die relative Position eines Werts in einem Array zurück.
Also suchen wir für die beiden Eingaben für INDEXzuerst die Zeilennummer in der Quelltabelle, die mit dem von uns verwendeten Nachschlagewert (Kundennummer) übereinstimmt (das ist die traditionelle erste Hälfte einer INDEX MATCH), indem wir MATCHein vertikales Array zum Suchen speisen. Dann suchen wir die Spaltennummer, die mit dem Namen der Spalte übereinstimmt, in der wir uns befinden, indem wir MATCHein horizontales Array speisen, das aus der Kopfzeile der Quellspalte besteht.
Sie werden feststellen, dass Sie die neuen Ergebnisse erhalten, ohne die Formel zu ändern, wenn Sie der Quelltabelle ein Feld hinzufügen und die Kopfzeile in der Formeltabelle ändern.
… und dass diese Formel sowohl horizontal als auch vertikal kopiert werden kann.
Die beiden Schlüssel hierzu sind:
- in dem Wissen, dass MATCH sowohl waagerecht als auch senkrecht zählt.
- manuelles Ändern der strukturierten Referenz, die Excel Ihnen liefert, in die tatsächliche Zellreferenz im R1C1-Stil, sodass Sie den Spaltenindex dieser Zellreferenz dynamisch statt fest machen können (d. h. C$1 im Gegensatz zu
TableA[[#Headers],[Post Code]], das den Wert für das Feld, in dem Sie sich befinden, fest codiert und als solches nicht kopiert wird, obwohl es funktioniert, wenn Sie den Namen der Spalte mit der Nachschlageformel manuell ändern und Sie nur ein Nachschlagefeld in der Zieltabelle benötigen).
AchtungIch weiß, dass diese Frage über drei Jahre alt ist, aber es ist eine gute Frage und eine großartige Demonstration der Vielseitigkeit der INDEX MATCHTechnik.