Formula generica
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Sommario
Per invertire un CERCA.VERT, ovvero per trovare il valore di ricerca originale utilizzando un risultato della formula CERCA.VERT, puoi utilizzare una formula complicata basata sulla funzione SCEGLI, o formule più semplici basate su INDICE e CONFRONTA o XLOOKUP come spiegato di seguito. Nell'esempio mostrato, la formula in H10 è:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Con questa configurazione, CERCA.VERT trova l'opzione associata a un costo di 3000 e restituisce "C".
Nota: questo è un argomento più avanzato. Se hai appena iniziato con CERCA.VERT, inizia da qui.
introduzione
Una limitazione chiave di CERCA.VERT è che può solo cercare i valori a destra. In altre parole, la colonna con i valori di ricerca deve essere a sinistra dei valori che si desidera recuperare con CERCA.VERT. Di conseguenza, con la configurazione standard, non è possibile utilizzare CERCA.VERT per "guardare a sinistra" e invertire la ricerca originale.
Dal punto di vista di CERCA.VERT, possiamo visualizzare il problema in questo modo:
La soluzione alternativa spiegata di seguito utilizza la funzione SCEGLI per riorganizzare la tabella all'interno di CERCA.VERT.
Spiegazione
Partendo dall'inizio, la formula in H5 è una normale formula CERCA.VERT:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Utilizzando G5 come valore di ricerca ("C") e i dati in B5: D8 come matrice di tabella, CERCA.VERT esegue una ricerca sui valori nella colonna B e restituisce il valore corrispondente dalla colonna 3 (colonna D), 3000. Avviso zero (0) viene fornito come ultimo argomento per forzare una corrispondenza esatta.
La formula in G10 estrae semplicemente il risultato da H5:
=H5 // 3000
Per eseguire una ricerca inversa, la formula in H10 è:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
La parte difficile è la funzione SCEGLI, che viene utilizzata per riorganizzare l'array della tabella in modo che il costo sia la prima colonna e l'opzione sia l'ultima:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
La funzione SCEGLI è progettata per selezionare un valore basato su un indice numerico. In questo caso, stiamo fornendo tre valori di indice in una costante di matrice:
(3,2,1) // array constant
In altre parole, chiediamo la colonna 3, quindi la colonna 2, quindi la colonna 1. Questa è seguita dai tre intervalli che rappresentano ciascuna colonna della tabella nell'ordine in cui appaiono sul foglio di lavoro.
Con questa configurazione, SCEGLI restituisce tutte e tre le colonne in un singolo array 2D come questo:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Se visualizziamo questo array come una tabella sul foglio di lavoro, abbiamo:
Nota: i titoli non fanno parte dell'array e sono mostrati qui solo per chiarezza.
In effetti, abbiamo scambiato le colonne 1 e 3. La tabella riorganizzata viene restituita direttamente a CERCA.VERT, che corrisponde a 3000 e restituisce il valore corrispondente dalla colonna 3, "C".
Con INDEX e MATCH
La soluzione di cui sopra funziona bene, ma è difficile da consigliare poiché la maggior parte degli utenti non capirà come funziona la formula. Una soluzione migliore è INDICE e CONFRONTA, utilizzando una formula come questa:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Qui, la funzione CONFRONTA trova il valore 3000 in D5: D8 e restituisce la sua posizione 3:
MATCH(G10,D5:D8,0) // returns 3
Nota: MATCH è configurato per una corrispondenza esatta impostando l'ultimo argomento su zero (0).
CONFRONTA restituisce un risultato direttamente a INDICE come numero di riga, quindi la formula diventa:
=INDEX(B5:B8,3) // returns "C"
e INDICE restituisce il valore dalla terza riga di B5: B8, "C".
Questa formula mostra come INDICE e CONFRONTA possono essere più flessibili di CERCA.VERT.
Con XLOOKUP
XLOOKUP fornisce anche un'ottima soluzione. La formula equivalente è:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Con un valore di ricerca da G10 (3000), anche un array di D5: D8 (costi) e un array di risultati di B5: B8 (opzioni), XLOOKUP individua 3000 nell'array di ricerca e restituisce l'elemento corrispondente dall'array dei risultati, "C". Poiché XLOOKUP esegue una corrispondenza esatta per impostazione predefinita, non è necessario impostare esplicitamente la modalità di corrispondenza.
