Come cercare un valore in un altro foglio Excel
Cerca un valore in un altro foglio Excel con CERCA.X, CERCA.VERT o INDICE e CONFRONTA, con riferimenti al foglio corretti e intervalli bloccati.
Metodo 1: CERCA.X tra fogli diversi
Supponi che il foglio corrente contenga un codice prodotto in A2 e che un foglio chiamato Products contenga i codici nella colonna A e i prezzi nella colonna C. Usa =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,"Not found"). XLOOKUP cerca nel primo intervallo e restituisce il valore allineato del secondo; la corrispondenza esatta è predefinita. La funzione richiede Microsoft 365 oppure Excel 2021 o una versione successiva.
Costruisci il riferimento digitando la funzione, passando all'altro foglio e selezionando l'intervallo: Excel inserisce da solo il nome del foglio. Il nome del foglio è seguito da un punto esclamativo. Se contiene uno spazio o segni di punteggiatura, mettilo tra apici singoli, come in 'Product List'!$A$2:$A$100; un apice dentro il nome va raddoppiato. I simboli del dollaro mantengono fissi gli intervalli quando copi la formula.
Metodo 2: CERCA.VERT e INDICE con CONFRONTA
Per cartelle di lavoro che devono aprirsi in versioni senza XLOOKUP, usa =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE). La chiave deve trovarsi nella prima colonna della tabella selezionata, 3 è la colonna da restituire contata dal bordo sinistro della tabella e FALSE richiede una corrispondenza esatta.
=INDEX(Products!$C$2:$C$100,MATCH(A2,Products!$A$2:$A$100,0)) separa l'intervallo dei risultati da quello della chiave e funziona in tutte le versioni. Entrambi gli intervalli devono iniziare e finire su righe allineate. L'esercizio interattivo qui sotto usa un unico foglio, perché la griglia di pratica è un solo foglio; in Excel l'unica differenza è aggiungere il nome del foglio a ogni intervallo di origine.
Metodo 3: tabelle di Excel e riferimenti strutturati
Un intervallo fisso come A2:C100 esclude in silenzio la riga 101. Se i dati di ricerca sono una tabella di Excel chiamata tblProducts, fai riferimento alle sue colonne per nome, per esempio ID e Price, invece che a indirizzi fissi. I nomi delle tabelle valgono per l'intera cartella di lavoro, quindi la formula funziona da qualsiasi foglio, non richiede il nome del foglio e si espande quando aggiungi righe.
INDIRECT (INDIRETTO) può costruire un riferimento a partire da un testo, come =INDIRECT("'"&B1&"'!A2") per scegliere il foglio indicato in B1. Usala con parsimonia: è volatile, smette di puntare al posto giusto se rinomini un foglio, perché il nome è scritto in un testo, ed è difficile da verificare. Un riferimento diretto o una tabella di solito è la scelta migliore.
Mantenere sano il collegamento
Quando è possibile, tieni il foglio di ricerca nella stessa cartella di lavoro. Un riferimento a un altro file ha l'aspetto [Prices.xlsx]Sheet1!$A$2:$C$100 e richiede che quel file sia raggiungibile, con ulteriori richieste di aggiornamento e di sicurezza. Se elimini il foglio di origine le formule diventano errori #RIF!, quindi rinomina i fogli invece di ricrearli.
Se una ricerca tra fogli restituisce #N/D anche se la chiave sembra presente, la causa sta nei dati e non nel riferimento al foglio: leggi perché una VLOOKUP restituisce #N/D per spazi, chiavi di testo e chiavi numeriche e corrispondenza approssimata.
Tocca a te
In F2 restituisci il prezzo del codice in E2. In Excel la tabella A:C potrebbe trovarsi in un altro foglio.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Find ID | Price | |
| 2 | P-1 | Lamp | 18 | P-2 | ||
| 3 | P-2 | Chair | 24 | |||
| 4 | P-3 | Desk | 55 | |||
| 5 | P-4 | Shelf | 32 |
Soluzione: =XLOOKUP(E2,A2:A5,C2:C5,"Not found")
Esempio verificato
Gli ordini contengono i codici; i prezzi stanno in un secondo foglio chiamato «Product List», perciò ogni riferimento richiede gli apici. P-9 non è nell'elenco.
| A | |
|---|---|
| 1 | Product ID |
| 2 | P-2 |
| 3 | P-9 |
| A | B | C | |
|---|---|---|---|
| 1 | ID | Product | Price |
| 2 | P-1 | Lamp | 18 |
| 3 | P-2 | Chair | 24 |
| 4 | P-3 | Desk | 55 |
| Formula | Risultato |
|---|---|
=XLOOKUP(A2,'Product List'!$A$2:$A$4,'Product List'!$C$2:$C$4,"Not found") | 24 |
=XLOOKUP(A3,'Product List'!$A$2:$A$4,'Product List'!$C$2:$C$4,"Not found") | "Not found" |
=VLOOKUP(A2,'Product List'!$A$2:$C$4,3,FALSE) | 24 |
=INDEX('Product List'!$B$2:$B$4,MATCH(A2,'Product List'!$A$2:$A$4,0)) | "Chair" |
=SUM('Product List'!$C$2:$C$4) | 97 |
Errori frequenti
- Dimenticare gli apici attorno al nome di un foglio che contiene spazi.
- Lasciare relativi gli intervalli di origine prima di trascinare verso il basso.
- Usare
VLOOKUPapprossimata per identificativi o codici.
Domande frequenti
Come faccio riferimento a un foglio con spazi nel nome?
Metti il nome tra apici, per esempio 'Product List'!A2:A100.
CERCA.X può restituire un valore a sinistra?
Sì. Gli intervalli di ricerca e di risultato sono indipendenti.
Perché il riferimento si sposta quando copio la formula?
Blocca gli intervalli di ricerca con i simboli del dollaro oppure usa i riferimenti strutturati di una tabella.
Posso cercare in un'altra cartella di lavoro?
Sì, ma i collegamenti esterni aggiungono problemi di percorso, accesso, aggiornamento e manutenzione. Tieni disponibile la cartella di origine e prova il comportamento dei collegamenti.
Metti in pratica
Continua con una lezione verificata di FormulaCamp in inglese: A Lookup Table on Another Sheet (in inglese) Gratis
Apri la lezione gratuita (in inglese)