Generisk formel
=XLOOKUP(max,dates,results,,-1) // latest match by date
Sammanfattning
För att få den senaste matchningen i en uppsättning data efter datum kan du använda XLOOKUP i ungefärligt matchningsläge genom att ställa in match_mode till -1. I exemplet som visas är formeln i G5, kopierad ner,:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
där datum (C5: C15), artikel (B5: B15) och pris (D5: D15) heter intervall.
Förklaring
XLOOKUP erbjuder flera funktioner som gör det exceptionellt bra för mer komplicerade sökningar. I det här exemplet vill vi ha det senaste priset för en artikel efter datum. Om data sorterades efter datum i stigande ordning skulle det vara väldigt enkelt. I det här fallet är dock data osorterade.
Som standard returnerar XLOOKUP den första matchningen i en datamängd. För att få den sista matchningen kan vi ställa in det valfria argumentet search_mode, till -1 så att XLOOKUP söker "sista till första". Vi kan dock inte använda den här metoden här eftersom det inte finns någon garanti för att det senaste priset för en artikel visas sist.
Istället kan vi ställa in det valfria argumentet match_mode till -1 för att tvinga en ungefärlig matchning av "exakt eller näst minsta" och justera uppslagsvärdet och uppslagsmatrisen som förklaras nedan. Formeln i G5, kopierad ner, är:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Genom att arbeta igenom argumenten en efter en är lookup_value det största (senaste) datumet i data:
MAX(date) // get max date value
Lookup_array härrör från ett booleskt logiskt uttryck:
(item=F5)*date
Genom att jämföra varje artikel med värdet i F5, "Belt", får vi en matris med SANT / FALSKA värden:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
där SANT värden representerar poster för "Belt". Denna array fungerar som ett filter. När det multipliceras med värdena i området som heter dagen är sant / falskt värden utvärderas till 1: or och 0: s:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Resultatet är en matris som endast innehåller nollor och datum för bälten:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Obs! Serienumren är giltiga Excel-datum.
Denna matris levereras direkt till XLOOKUP som argumentet lookup_array.
Den return_array är det namngivna området pris (D5: D15)
Det valfria argumentet not_found tillhandahålls inte.
Match_mode är inställd på -1 för exakt matchning eller nästa minsta objekt.
XLOOKUP söker igenom uppslagsmatrisen för det maximala datumvärdet. Eftersom arrayen redan har filtrerats för att utesluta datum som inte är associerade med "Belt", hittar XLOOKUP helt enkelt den bästa matchningen (antingen det exakta datumet eller nästa minsta datum) som motsvarar det senaste datumet.
Det slutliga resultatet är det pris som är associerat med det senaste datumet. Formeln fortsätter att fungera när data sorteras i valfri ordning.
