Formula generica
=LOOKUP(2,1/(item="hat"),price)
Sommario
Per cercare il prezzo più recente di un prodotto in un elenco, ordinato in modo che gli elementi più recenti vengano visualizzati per ultimi, è possibile utilizzare una formula basata sulla funzione CERCA. Nell'esempio di spettacolo, la formula in G7 è:
=LOOKUP(2,1/(item=F7),price)
dove l' elemento è l'intervallo denominato B5: B12, il prezzo è l'intervallo denominato D5: D12 ei dati vengono ordinati in ordine crescente per data.
Spiegazione
La funzione CERCA presuppone che i dati siano ordinati e fa sempre una corrispondenza approssimativa. Se il valore di ricerca è maggiore di tutti i valori nell'array di ricerca, il comportamento predefinito è "tornare indietro" al valore precedente. Questa formula sfrutta questo comportamento creando un array che contiene solo 1 ed errori, quindi cercando deliberatamente il valore 2, che non verrà mai trovato.
Innanzitutto, viene valutata questa espressione:
item=F7
Quando F7 contiene "sandali" il risultato è un array di valori VERO e FALSO come questo:
(FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)
Questo array è fornito come divisore per 1:
1/(FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)
L'operazione matematica forza automaticamente i valori TRUE e FALSE su 1 e 0, quindi il risultato è un altro array come questo:
(#DIV/0!;1;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!)
restituito direttamente alla funzione CERCA come argomento del vettore di ricerca.
Notare che l'array contiene solo due valori univoci: l'errore di divisione per zero (# DIV / 0!) E il numero 1.
LOOKUP cerca nell'array il valore 2, ignorando i valori di errore. Non trovando 2, ricade sull'ultimo 1, nella posizione 7 nel vettore di ricerca. CERCA quindi restituisce il settimo elemento nel vettore dei risultati (l'intervallo denominato "prezzo"), il valore 15.
Per saperne di più sul concetto di cercare intenzionalmente un valore che non apparirà mai, leggi BigNum.
