Generieke formule
=XLOOKUP(max,dates,results,,-1) // latest match by date
Samenvatting
Om de laatste overeenkomst in een set gegevens op datum te krijgen, kunt u XLOOKUP gebruiken in de geschatte overeenkomstmodus door match_mode in te stellen op -1. In het getoonde voorbeeld is de formule in G5, naar beneden gekopieerd,:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
waarbij datum (C5: C15), item (B5: B15) en prijs (D5: D15) benoemde bereiken zijn.
Uitleg
XLOOKUP biedt verschillende functies die het uitzonderlijk goed maken voor meer gecompliceerde zoekopdrachten. In dit voorbeeld willen we de laatste prijs voor een artikel op datum. Als de gegevens in oplopende volgorde op datum zouden zijn gesorteerd, zou dit heel eenvoudig zijn. In dit geval zijn de gegevens echter ongesorteerd.
XLOOKUP retourneert standaard de eerste overeenkomst in een dataset. Om de laatste overeenkomst te krijgen, kunnen we het optionele argument search_mode, op -1 zetten om ervoor te zorgen dat XLOOKUP "last to first" zoekt. We kunnen deze benadering hier echter niet gebruiken omdat er geen garantie is dat de laatste prijs voor een artikel als laatste verschijnt.
In plaats daarvan kunnen we het optionele argument match_mode instellen op -1 om een geschatte match van "exact of volgende kleinste" te forceren, en de opzoekwaarde en de lookup-array aanpassen zoals hieronder wordt uitgelegd. De formule in G5, gekopieerd, is:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Als we argumenten een voor een doorlopen, is de lookup_value de grootste (laatste) datum in de gegevens:
MAX(date) // get max date value
De lookup_array wordt afgeleid met een booleaanse logische uitdrukking:
(item=F5)*date
Door elk item te vergelijken met de waarde in F5, "Belt", krijgen we een reeks TRUE / FALSE-waarden:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
waar TRUE waarden staan voor vermeldingen voor "Belt". Deze array werkt als een filter. Wanneer het wordt vermenigvuldigd met de waarden in het benoemde bereik datum , worden de waar / onwaar waarden geëvalueerd om 1's en 0's:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Het resultaat is een array die alleen nullen en datums voor banden bevat:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Let op: de serienummers zijn geldige Excel-datums.
Deze array wordt rechtstreeks aan XLOOKUP geleverd als het argument lookup_array.
De return_array is het benoemde bereik prijs (D5: D15)
Het optionele argument not_found is niet opgegeven.
Match_mode is ingesteld op -1, voor exacte overeenkomst, of het volgende kleinste item.
XLOOKUP zoekt in de opzoekmatrix naar de maximale datumwaarde. Omdat de array al is gefilterd om datums uit te sluiten die niet aan "Belt" zijn gekoppeld, zoekt XLOOKUP eenvoudig de beste overeenkomst (ofwel de exacte datum of de eerstvolgende kleinste datum) die overeenkomt met de laatste datum.
Het uiteindelijke resultaat is de prijs die is gekoppeld aan de laatste datum. De formule blijft werken als de gegevens in willekeurige volgorde zijn gesorteerd.
