Formula generica
=XLOOKUP(max,dates,results,,-1) // latest match by date
Sommario
Per ottenere l'ultima corrispondenza in un insieme di dati per data, puoi utilizzare XLOOKUP in modalità di corrispondenza approssimativa impostando match_mode su -1. Nell'esempio mostrato, la formula in G5, copiata verso il basso, è:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
dove data (C5: C15), articolo (B5: B15) e prezzo (D5: D15) sono intervalli denominati.
Spiegazione
XLOOKUP offre diverse funzionalità che lo rendono eccezionalmente buono per ricerche più complicate. In questo esempio, vogliamo l'ultimo prezzo di un articolo per data. Se i dati fossero ordinati per data in ordine crescente, sarebbe molto semplice. Tuttavia, in questo caso, i dati non sono ordinati.
Per impostazione predefinita, XLOOKUP restituirà la prima corrispondenza in un set di dati. Per ottenere l'ultima corrispondenza, possiamo impostare l'argomento opzionale search_mode, a -1 per far sì che XLOOKUP cerchi "dall'ultimo al primo". Tuttavia, non possiamo utilizzare questo approccio qui perché non vi è alcuna garanzia che l'ultimo prezzo di un articolo appaia per ultimo.
Invece, possiamo impostare l'argomento opzionale match_mode su -1 per forzare una corrispondenza approssimativa di "esatta o successiva più piccola", e regolare il valore di ricerca e l'array di ricerca come spiegato di seguito. La formula in G5, copiata in basso, è:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Analizzando gli argomenti uno per uno, lookup_value è la data più grande (più recente) nei dati:
MAX(date) // get max date value
Lookup_array è derivato da un'espressione logica booleana:
(item=F5)*date
Confrontando ogni elemento con il valore in F5, "Belt", otteniamo un array di valori VERO / FALSO:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
dove i valori TRUE rappresentano le voci per "Belt". Questo array agisce come un filtro. Quando viene moltiplicato per i valori nella data dell'intervallo denominato , i valori VERO / FALSO vengono valutati in 1 e 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Il risultato è un array che contiene solo zeri e date per le cinture:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Nota: i numeri di serie sono date Excel valide.
Questo array viene consegnato direttamente a XLOOKUP come argomento lookup_array.
Return_array è il prezzo dell'intervallo denominato (D5: D15)
L'argomento facoltativo not_found non è fornito.
Match_mode è impostato su -1, per la corrispondenza esatta o l'elemento più piccolo successivo.
XLOOKUP cerca nell'array di ricerca il valore massimo della data. Poiché la matrice è già stata filtrata per escludere le date non associate a "Belt", XLOOKUP trova semplicemente la migliore corrispondenza (la data esatta o la data più piccola successiva) che corrisponde alla data più recente.
Il risultato finale è il prezzo associato alla data più recente. La formula continuerà a funzionare quando i dati vengono ordinati in qualsiasi ordine.
