Generisk formel
=XLOOKUP(max,dates,results,,-1) // latest match by date
Sammendrag
For å få den siste kampen i et datasett etter dato, kan du bruke XLOOKUP i omtrentlig kampmodus ved å sette match_mode til -1. I eksemplet vist er formelen i G5, kopiert ned,:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
der dato (C5: C15), vare (B5: B15) og pris (D5: D15) er kalt områder.
Forklaring
XLOOKUP tilbyr flere funksjoner som gjør det eksepsjonelt bra for mer kompliserte oppslag. I dette eksemplet ønsker vi den siste prisen for en vare etter dato. Hvis data ble sortert etter dato i stigende rekkefølge, ville dette være veldig greit. I dette tilfellet er imidlertid data usortert.
Som standard returnerer XLOOKUP den første kampen i et datasett. For å få den siste kampen, kan vi sette valgfritt argument search_mode, til -1 for å få XLOOKUP til å søke "last to first". Vi kan imidlertid ikke bruke denne tilnærmingen her fordi det ikke er noen garanti for at den siste prisen for en vare vises sist.
I stedet kan vi sette det valgfrie argumentet match_mode til -1 for å tvinge en omtrentlig samsvar med "eksakt eller nest minste", og justere oppslagsverdien og oppslagsmatrisen som forklart nedenfor. Formelen i G5, kopiert ned, er:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Arbeid gjennom argumenter en etter en, er lookup_value den største (siste) datoen i dataene:
MAX(date) // get max date value
Lookup_array er avledet med et boolsk logisk uttrykk:
(item=F5)*date
Ved å sammenligne hvert element med verdien i F5, "Belt", får vi en rekke SANNE / FALSE verdier:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
der SANNE verdier representerer oppføringer for "Belte". Denne matrisen fungerer som et filter. Når den multipliseres med verdiene i den angitte datoen for området , blir SANNE / FALSE verdiene evaluert til 1 og 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Resultatet er en matrise som bare inneholder nuller og datoer for belter:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Merk: serienumrene er gyldige Excel-datoer.
Denne matrisen leveres direkte til XLOOKUP som argumentet lookup_array.
Den return_array er navngitt område pris (D5: D15)
Det valgfrie argumentet not_found er ikke gitt.
Match_mode er satt til -1, for eksakt samsvar, eller neste minste element.
XLOOKUP ser gjennom oppslagsmatrisen for maksimal datoverdi. Siden matrisen allerede er filtrert for å ekskludere datoer som ikke er knyttet til "Belt", finner XLOOKUP ganske enkelt det beste samsvaret (enten den eksakte datoen eller den neste minste datoen) som tilsvarer den siste datoen.
Det endelige resultatet er prisen knyttet til den siste datoen. Formelen vil fortsette å fungere når dataene sorteres i hvilken som helst rekkefølge.
