Formula generica
(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))
Sommario
Per recuperare più valori corrispondenti da un insieme di dati con una formula, puoi utilizzare le funzioni SE e PICCOLO per calcolare il numero di riga di ciascuna corrispondenza e riportare tale valore a INDICE. Nell'esempio mostrato, la formula in I7 è:
(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))
Dove gli intervalli denominati sono amts (D4: D11), id (I3) e ids (C4: C11).
Nota che questa è una formula di matrice e deve essere inserita con Control + Maiusc + Invio.
Spiegazione
In sostanza, questa formula è semplicemente una formula INDICE che recupera il valore in una matrice in una determinata posizione. Il valore per n è fornito nella colonna H, e tutto il lavoro "pesante" che la formula fa è capire la riga da cui recuperare un valore, dove riga corrisponde alla corrispondenza "n-esima".
La funzione IF fa il lavoro per capire quali righe contengono una corrispondenza e la funzione SMALL restituisce l'ennesimo valore da quella lista. All'interno di IF, il test logico è:
ids=id
che produce questo array:
(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Nota che l'ID cliente corrisponde alla prima e alla quarta posizione, che appaiono come VERO. L'argomento "valore se vero" in IF genera un elenco di numeri di riga relativi con questa espressione:
ROW(ids)-ROW(INDEX(ids,1,1))+1
che produce questo array:
(1;2;3;4;5;6;7)
Questa matrice viene quindi "filtrata" dai risultati del test logico e la funzione IF restituisce il seguente risultato della matrice:
(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)
Nota che abbiamo numeri di riga validi per la riga 1 e la riga 2.
Questo array viene quindi elaborato da SMALL, che è configurato per utilizzare i valori nella colonna H per restituire i valori "nth". La funzione SMALL ignora automaticamente i valori logici TRUE e FALSE nell'array. Alla fine, le formule si riducono a:
=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125
Gestione degli errori
Quando non ci sono più corrispondenze per un dato id, la funzione SMALL restituirà un errore #NUM. È possibile gestire questo errore con la funzione SE.ERRORE o aggiungendo logica per contare le corrispondenze e interrompere l'elaborazione una volta che il numero nella colonna H è maggiore del conteggio delle corrispondenze. L'esempio qui mostra un approccio.
Criteri multipli
Per aggiungere più criteri, utilizzare la logica booleana, come spiegato in questo esempio.
