Formula generica
=VLOOKUP(A1,CHOOSE((1,2),range2,range1),2,0)
Sommario
Per utilizzare CERCA.VERT per eseguire una ricerca a sinistra, è possibile utilizzare la funzione SCEGLI per riordinare la tabella di ricerca. Nell'esempio mostrato, la formula in F5 è:
=VLOOKUP(E5,CHOOSE((1,2),score,rating),2,0)
dove score (C5: C9) e rating (B5: B9) sono denominati intervalli.
Spiegazione
Uno dei limiti principali della funzione CERCA.VERT è che può solo cercare valori a destra. In altre parole, la colonna che contiene i valori di ricerca deve trovarsi a sinistra dei valori che si desidera recuperare con CERCA.VERT. Non è possibile ignorare questo comportamento poiché è cablato nella funzione. Di conseguenza, con la configurazione normale, non è possibile utilizzare CERCA.VERT per cercare una valutazione nella colonna B in base a un punteggio nella colonna C.
Una soluzione alternativa consiste nel ristrutturare la tabella di ricerca stessa e spostare la colonna di ricerca a sinistra dei valori di ricerca. Questo è l'approccio adottato in questo esempio, che utilizza la funzione SCEGLI, inversione di valutazione e punteggio in questo modo:
CHOOSE((1,2),score,rating)
Normalmente, SCEGLI viene utilizzato con un singolo numero di indice come primo argomento e gli argomenti rimanenti sono i valori tra cui scegliere. Tuttavia, qui diamo scegliere una costante di matrice per il numero di indice contenente due numeri: (1,2). In sostanza, chiediamo di scegliere sia il primo che il secondo valore.
I valori sono forniti come i due intervalli denominati nell'esempio: punteggio e valutazione. Si noti tuttavia che stiamo fornendo questi intervalli in ordine inverso. La funzione SCEGLI seleziona entrambi gli intervalli nell'ordine fornito e restituisce il risultato come un singolo array come questo:
(5,"Excellent";4,"Good";3,"Average";2,"Poor";1,"Terrible")
SCEGLI restituisce questo array direttamente a CERCA.VERT come argomento dell'array della tabella. In altre parole, SCEGLI fornisce una tabella di ricerca come questa a CERCA.VERT:
Utilizzando il valore di ricerca in E5, CERCA.VERT individua una corrispondenza all'interno della tabella appena creata e restituisce un risultato dalla seconda colonna.
Riordino con la costante di matrice
Nell'esempio mostrato, stiamo riordinando la tabella di ricerca invertendo "rating" e "score" all'interno della funzione scelta. Tuttavia, potremmo invece usare la costante dell'array per riordinare in questo modo:
CHOOSE((2,1),rating,score)
Il risultato è esattamente lo stesso.
Con INDEX e MATCH
Sebbene l'esempio sopra funzioni bene, non è l'ideale. Per prima cosa, la maggior parte degli utenti medi non capirà come funziona la formula. Una soluzione più naturale è INDICE e CONFRONTA. Ecco la formula equivalente:
=INDEX(rating,MATCH(E5,score,0))
In effetti, questo è un buon esempio di come INDICE e CONFRONTA siano più flessibili di CERCA.VERT.
