Cercare l'ultimo valore corrispondente

La maggior parte delle ricerche restituisce la prima corrispondenza trovata. Tuttavia, a volte serve l'ultima: il prezzo più recente di un prodotto, l'ultimo aggiornamento di stato o l'ultima voce relativa a un cliente.

Quando un elenco cresce nel tempo e la stessa chiave compare più volte, la riga più in basso è generalmente quella più aggiornata. Un normale VLOOKUP o MATCH con corrispondenza esatta, invece, seleziona ostinatamente la riga superiore.

Questa lezione mostra diversi metodi affidabili per recuperare l'ultimo valore corrispondente.

MATCH(value, range, 0) esamina l'intervallo dall'alto verso il basso e si ferma alla prima corrispondenza esatta. Se "Apple" compare nelle righe 2, 5 e 9, MATCH restituisce 2.

È perfetto quando le chiavi sono univoche, ma ignora le righe più recenti. Per raggiungere l'ultima occorrenza, serve una tecnica che cerchi dal basso oppure restituisca la posizione dell'ultima corrispondenza.

Se utilizza una versione moderna di Excel o Google Sheets, XLOOKUP semplifica l'operazione. Il quinto e il sesto argomento controllano la modalità di corrispondenza e la direzione di ricerca.

Passi -1 come argomento della modalità di ricerca per cercare dall'ultima voce alla prima. XLOOKUP restituirà quindi il valore associato alla chiave corrispondente più in basso.

In questo caso cerca il prodotto indicato in G1 nell'intervallo A2:A10 e restituisce il prezzo corrispondente da B2:B10, iniziando la ricerca dal basso.

Nei fogli di calcolo meno recenti, un trucco noto utilizza LOOKUP con il numero 2 e una divisione condizionata.

L'espressione 1/(A2:A10=G1) produce 1 per le righe corrispondenti e un errore di divisione per le righe non corrispondenti. LOOKUP cerca 2, un valore maggiore di tutti quelli presenti, supera gli errori e raggiunge l'ultimo 1 valido, restituendo il valore corrispondente da B2:B10.

Esamini passo per passo 1/(A2:A10=G1):

• Nelle righe in cui la chiave corrisponde si ottiene 1/TRUE = 1.
• Nelle righe che non corrispondono si ottiene 1/FALSE = un errore #DIV/0!.

LOOKUP ignora gli errori e, quando non trova il valore cercato (2), restituisce il risultato allineato all'ultima voce non contenente errori. Poiché tutte le corrispondenze valgono 1, prevale l'ultimo 1 e si ottiene il valore dell'ultima riga corrispondente.

Può anche rimanere nell'ambito della famiglia INDEX-MATCH. L'idea consiste nel trovare la posizione dell'ultima corrispondenza e passarla quindi a INDEX.

Utilizzando lo stesso trucco della divisione all'interno di MATCH, cerchi 2 in 1/(A2:A10=G1) per ottenere la posizione dell'ultima corrispondenza. Passi poi questa posizione a INDEX applicato alla colonna da cui restituire il valore.

Quando il terzo argomento di MATCH viene omesso, il valore predefinito è 1, cioè corrispondenza approssimativa su dati ordinati in senso crescente. MATCH cerca quindi il valore più grande minore o uguale a 2.

L'array 1/(A2:A10=G1) contiene solo 1 ed errori. Il valore più grande minore o uguale a 2 è 1 e MATCH restituisce la posizione dell'ultimo 1 di questo tipo. Tale posizione corrisponde esattamente all'ultima riga corrispondente.

Supponiamo che A2:A10 contenga gli stati dell'ordine "Order-7" registrati nel tempo e che B2:B10 contenga il testo dello stato. "Order-7" compare nelle righe 3, 6 e 9.

• L'array di corrispondenza assegna 1 alle righe 3, 6 e 9 e restituisce errori per tutte le altre.
• MATCH(2, ...) restituisce 9 come posizione, contando dall'inizio dell'intervallo: si tratta dell'ultima corrispondenza.
• INDEX restituisce lo stato dell'ultima riga, cioè quello più recente.

Quale approccio dovrebbe usare?

• XLOOKUP con -1: è il metodo più semplice e leggibile, se l'applicazione lo supporta.
• LOOKUP(2, 1/...): funziona quasi ovunque e non richiede una versione specifica.
• INDEX-MATCH(2, 1/...): è utile quando serve anche la posizione o si vuole restituire un valore da una colonna diversa.

Tutti e tre restituiscono lo stesso risultato; scelga in base agli strumenti disponibili e al livello di leggibilità desiderato per la formula.

Presti attenzione ai seguenti aspetti:

• Intervalli di dimensioni diverse: l'intervallo della condizione e quello da cui restituire il valore devono avere la stessa altezza, altrimenti le righe risultano disallineate.
• Duplicati nascosti: gli spazi finali possono fare in modo che "Apple " sia diverso da "Apple"; pulisca prima il testo con TRIM.
• Nessuna corrispondenza: il trucco restituisce un errore se non trova alcuna corrispondenza. Lo racchiuda in IFERROR per gestire il caso in modo chiaro.

Può combinare il trucco dell'ultima corrispondenza con due condizioni. Moltiplichi i test delle condizioni all'interno della divisione, in modo che solo le righe che soddisfano entrambe le chiavi producano 1.

Per esempio, può trovare il prezzo più recente per cui il prodotto è uguale a G1 e la regione è uguale a G2. Il trucco LOOKUP(2, ...) raggiungerà comunque l'ultima riga che soddisfa i criteri.

È utile per i registri con marcatura temporale in cui lo stesso prodotto compare in più regioni.

Per restituire l'ultima corrispondenza invece della prima:

• Utilizzi XLOOKUP(..., -1) per cercare dal basso verso l'alto, quando disponibile.
• Utilizzi il trucco classico LOOKUP(2, 1/(range=key), result) con qualsiasi versione.
• Utilizzi INDEX(result, MATCH(2, 1/(range=key))) quando serve anche la posizione.

Ricordi di mantenere gli intervalli delle stesse dimensioni, eliminare gli spazi indesiderati e racchiudere la formula in IFERROR per maggiore sicurezza.