Generische Formel
=XLOOKUP(max,dates,results,,-1) // latest match by date
Zusammenfassung
Um die letzte Übereinstimmung in einem Datensatz nach Datum zu erhalten, können Sie XLOOKUP im ungefähren Übereinstimmungsmodus verwenden, indem Sie match_mode auf -1 setzen. Im gezeigten Beispiel lautet die kopierte Formel in G5:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Dabei werden Datum (C5: C15), Artikel (B5: B15) und Preis (D5: D15) als Bereiche bezeichnet.
Erläuterung
XLOOKUP bietet verschiedene Funktionen, die es für kompliziertere Suchvorgänge besonders gut machen. In diesem Beispiel möchten wir den aktuellen Preis für einen Artikel nach Datum. Wenn die Daten in aufsteigender Reihenfolge nach Datum sortiert wären, wäre dies sehr einfach. In diesem Fall sind die Daten jedoch unsortiert.
Standardmäßig gibt XLOOKUP die erste Übereinstimmung in einem Datensatz zurück. Um die letzte Übereinstimmung zu erhalten, können wir das optionale Argument search_mode auf -1 setzen, damit XLOOKUP "last to first" sucht. Wir können diesen Ansatz hier jedoch nicht verwenden, da nicht garantiert werden kann, dass der letzte Preis für einen Artikel zuletzt angezeigt wird.
Stattdessen können wir das optionale Argument match_mode auf -1 setzen, um eine ungefähre Übereinstimmung von "genau oder nächstklein" zu erzwingen, und den Suchwert und das Sucharray wie unten erläutert anpassen. Die kopierte Formel in G5 lautet:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Der Lookup_Wert ist das größte (neueste) Datum in den Daten:
MAX(date) // get max date value
Das lookup_array wird mit einem booleschen Logikausdruck abgeleitet:
(item=F5)*date
Durch Vergleichen jedes Elements mit dem Wert in F5, "Gürtel", erhalten wir ein Array von TRUE / FALSE-Werten:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
Dabei stehen TRUE-Werte für Einträge für "Belt". Dieses Array wirkt wie ein Filter. Wenn es durch die Werte in dem benannten Bereich multipliziert Datum , die WAHR / FALSCH - Werte werden ausgewertet 1 und 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Das Ergebnis ist ein Array, das nur Nullen und Daten für Gürtel enthält:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Hinweis: Die Seriennummern sind gültige Excel-Daten.
Dieses Array wird direkt an XLOOKUP als Argument lookup_array geliefert.
Die return_array ist der benannte Bereich Preis (D5: D15)
Das optionale Argument not_found wird nicht bereitgestellt.
Match_mode wird für die genaue Übereinstimmung oder das nächstkleinere Element auf -1 gesetzt.
XLOOKUP durchsucht das Lookup-Array nach dem maximalen Datumswert. Da das Array bereits gefiltert wurde, um Daten auszuschließen, die nicht mit "Belt" verknüpft sind, findet XLOOKUP einfach die beste Übereinstimmung (entweder das genaue Datum oder das nächstkleinere Datum), die dem letzten Datum entspricht.
Das Endergebnis ist der Preis, der mit dem letzten Datum verbunden ist. Die Formel funktioniert weiterhin, wenn die Daten in beliebiger Reihenfolge sortiert sind.
