Perché VLOOKUP a volte non funziona

VLOOKUP è affidabile, ma può fallire in alcuni modi prevedibili. La maggior parte degli errori non è misteriosa, una volta note le regole.

In questa lezione imparerà le cause più comuni di una ricerca non riuscita e come risolvere esattamente ciascun problema. Con queste conoscenze, gli errori #N/A e #REF! difficili da interpretare diventano correzioni rapide e semplici.

VLOOKUP può eseguire la ricerca solo nella colonna più a sinistra del suo table_array e restituire valori a destra. Non può cercare un valore e restituire il dato che si trova alla sua sinistra.

Se gli ID si trovano nella colonna C e il nome desiderato è nella colonna A, VLOOKUP non può cercare a ritroso. Può riorganizzare le colonne in modo che quella di ricerca sia la prima oppure usare INDEX-MATCH o XLOOKUP, che eseguono ricerche in entrambe le direzioni.

Il col_index_num viene contato a partire dal lato sinistro del table_array, non del foglio. Un errore comune consiste nell'usare come numero la lettera della colonna del foglio.

Se l'intervallo è C1:F10 e desidera la colonna F, questa è la 4ª colonna dell'intervallo, quindi l'indice è 4, non 6. Contare dal bordo sbagliato restituisce il campo errato oppure, se il numero supera la larghezza dell'intervallo, un errore #REF!.

Se col_index_num è maggiore del numero di colonne nel table_array, VLOOKUP restituisce #REF!.

Ad esempio, richiedere la colonna 5 di un intervallo di 3 colonne A1:C10 è impossibile:

Corregga il problema ampliando il table_array in modo da includere la colonna necessaria oppure correggendo l'indice con un numero di colonna effettivamente presente nell'intervallo.

Se omette il quarto argomento, il valore predefinito è TRUE (corrispondenza approssimativa). In un elenco non ordinato, questo restituisce silenziosamente il valore vicino sbagliato invece di un errore, rendendo il problema difficile da individuare.

La soluzione è semplice e dovrebbe diventare un'abitudine: aggiunga sempre FALSE per le ricerche con corrispondenza esatta.

Un valore di ricerca pari a "A100" non corrisponde a "A100 " con uno spazio finale. I dati importati sono pieni di queste differenze invisibili.

Segnali: il valore esiste chiaramente, eppure ottiene #N/A. Pulisca entrambi i lati con TRIM per rimuovere gli spazi superflui:

Se il valore di ricerca è il numero 100 ma la tabella memorizza i codici come testo "100" (o viceversa), non corrisponderanno e otterrà #N/A.

Osservi il piccolo triangolo verde o i numeri allineati a sinistra, che segnalano la presenza di testo. Corregga il problema convertendo i dati: racchiuda il testo in VALUE() per trasformarlo in numero oppure concateni una stringa vuota a un numero con &"" per trasformarlo in testo, così entrambi i lati avranno lo stesso tipo.

Se dimentica di bloccare il table_array, copiando la formula verso il basso l'intervallo si sposta e non include più correttamente i dati. L'intervallo A1:C100 della riga 2 diventa A2:C101 nella riga 3, quindi A3:C102, saltando righe lungo il percorso.

Risolva il problema con riferimenti assoluti, così la tabella resta fissa e cambia solo il valore di ricerca:

Ogni errore indica una causa:

• #N/A - il valore non è stato trovato (mancata corrispondenza, spazi, tipo errato oppure valore effettivamente assente)
• #REF! - col_index_num è maggiore dell'intervallo oppure una cella referenziata è stata eliminata
• #VALUE! - un argomento è del tipo errato, ad esempio un indice di colonna negativo o uguale a zero
• #NAME? - il nome della funzione è scritto male, ad esempio VLOOKP

Colleghi il codice di errore al suo significato e avrà già risolto metà del problema.

Durante il debug, può anche racchiudere una ricerca in modo che gli utenti visualizzino un messaggio chiaro invece di un errore grezzo. IFERROR intercetta qualsiasi errore e restituisce al suo posto il testo specificato.

Questo non risolve la causa sottostante, quindi lo usi solo dopo aver capito perché la ricerca non è riuscita. Nascondere gli errori troppo presto può mascherare problemi reali nei dati.

Quando una ricerca non funziona come previsto, percorra questa rapida checklist:

• Il valore di ricerca si trova nella prima colonna dell'intervallo?
• Il col_index_num è contato dal bordo sinistro dell'intervallo e rientra nella sua larghezza?
• Ha aggiunto FALSE per una corrispondenza esatta?
• Entrambi i lati hanno lo stesso tipo (testo o numero) e non contengono spazi superflui?
• Il table_array è bloccato con i simboli del dollaro?

Seguendo questo elenco dall'alto verso il basso, risolverà la stragrande maggioranza dei problemi di ricerca in pochi secondi.

I problemi più comuni e le relative soluzioni:

• Limite della colonna sinistra - riorganizzi le colonne oppure usi INDEX-MATCH / XLOOKUP
• col_index_num errato o troppo grande - conti dal bordo sinistro dell'intervallo e ne ampli la larghezza
• FALSE mancante - imposti sempre la corrispondenza esatta per gli ID
• Spazi e differenze tra testo e numeri - pulisca con TRIM e converta con VALUE o &""
• Tabella non bloccata - usi $ per mantenere fisso l'intervallo

Legga il codice di errore, lo associ alla causa e applichi la soluzione. Ora dispone di tutti gli strumenti necessari per eseguire ricerche affidabili.