Usare XLOOKUP con più criteri in Excel

XLOOKUP può cercare valori con più criteri in Excel usando due approcci principali: logica booleana (moltiplicazione/ somma di condizioni) e concatenazione di chiavi. La prima è più flessibile per condizioni complesse e confronti logici; la seconda è semplice e leggibile quando le combinazioni di colonne sono univoche. Entrambi funzionano in Excel 2021, Microsoft 365, Excel web e app mobili.

Collegamenti rapidi

• La sintassi di XLOOKUP e un esempio con un singolo criterio
• Esempio 1: più criteri con logica booleana
• Esempio 2: più criteri con concatenazione

Introduzione

Un malinteso comune è che XLOOKUP accetti solo un criterio. In realtà, è possibile cercare su più criteri combinando colonne in un array temporaneo. In questo articolo vedremo i due metodi principali, le differenze, quando usare l’uno o l’altro, esempi passo passo, comportamenti di errore, alternative e checklist operative.

Requisiti minimi: Excel 2021 o successivo (incluso Microsoft 365), Excel per il web o le app mobili/tablet.

Nota importante: le formule che usano array dinamici funzionano nella maggior parte delle versioni moderne di Excel; se lavori con una versione molto vecchia potresti dover usare CTRL+MAIUSC+INVIO per formule array (ma questo è raro nelle versioni supportate oggi).

La sintassi di XLOOKUP e un esempio con un singolo criterio

La sintassi può sembrare complessa ma è lineare:

=XLOOKUP(a,b,c,d,e,f)

dove

• a (obbligatorio) è il valore da cercare,
• b (obbligatorio) è l’array o intervallo in cui effettuare la ricerca,
• c (obbligatorio) è l’array o intervallo da cui restituire i valori corrispondenti,
• d (facoltativo) è il testo da restituire se non viene trovato nulla,
• e (facoltativo) è la modalità di confronto (0 = corrispondenza esatta predefinita, -1 = corrispondenza esatta o elemento più piccolo, 1 = corrispondenza esatta o elemento più grande, 2 = corrispondenza con jolly),
• f (facoltativo) è la modalità di ricerca (1 = primo->ultimo predefinito, -1 = ultimo->primo, 2 = ricerca binaria ascendente, -2 = ricerca binaria discendente).

Esempio pratico: dopo aver scelto un ID nella cella E1, la formula

=XLOOKUP(E1,T_Scores[ID],T_Scores[Score],"No match",0,1)

restituisce il punteggio corrispondente (es. 51) nella cella E2.

Spiegazione rapida degli argomenti nell’esempio di cui sopra:

• E1: valore cercato (es. 1323),
• T_Scores[ID]: intervallo dove cercare l’ID,
• T_Scores[Score]: intervallo da cui restituire il valore,
• “No match”: testo restituito se non viene trovato nulla,
• 0: corrispondenza esatta,
• 1: ricerca dal primo all’ultimo elemento.

Gli ultimi due argomenti sono opzionali perché i loro valori sono i predefiniti.

Esempio 1: più criteri con logica booleana

Questo approccio crea un array temporaneo che combina più condizioni mediante operatori logici e aritmetici. È molto potente quando vuoi:

• applicare condizioni di confronto (>, <, >=, <=),
• usare OR tra criteri (sommare i booleani),
• usare AND tra criteri (moltiplicare i booleani),
• gestire criteri con tipi diversi (stringhe, numeri, date).

Scenario: un brand vende sei bevande in diversi paesi, e a ogni combinazione bevanda+paese è assegnato un responsabile (manager) e un numero di vendite. Vuoi inserire una bevanda e un paese e ottenere il manager e le vendite.

Formula consigliata (suddivisa in righe per chiarezza):

=XLOOKUP(
  1,
  (T_Managers[Drink]=G1)*(T_Managers[Country]=G2),
  T_Managers[[Manager]:[Sales]],
  "No result"
)

Spiegazione dettagliata passo per passo:

1. Valutazione dei singoli criteri come array booleani

   • (T_Managers[Drink]=G1) restituisce un array di TRUE/FALSE per ogni riga della colonna Drink; TRUE dove il valore corrisponde a G1.
   • (T_Managers[Country]=G2) restituisce un array di TRUE/FALSE per ogni riga della colonna Country; TRUE dove il valore corrisponde a G2.

2. Conversione booleana->numerica e combinazione con moltiplicazione

   • In Excel TRUE si comporta come 1 e FALSE come 0.
   • Moltiplicando gli array ottieni un array di 0/1 che è 1 solo quando entrambe le condizioni sono vere (AND).

   Esempio intermedio:

   (T_Managers[Drink]=G1) -> {0;0;0;0;0;0;1;1;1;1;1;1;0…}

   (T_Managers[Country]=G2) -> {0;0;0;0;1;0;0;0;0;0;1;0…}

   Prodotto -> {0;0;0;0;0;0;0;0;0;0;1;0…}

   Il primo 1 indica la riga che soddisfa entrambe le condizioni.

3. XLOOKUP cerca il valore 1 nell’array risultante

   • XLOOKUP(1, lookup_array, return_array, …) cerca la prima occorrenza di 1 e restituisce i valori corrispondenti dalla return_array (qui Manager e Sales).

4. Risultato

   • Se la riga trovata è la undicesima, la formula restituisce Manager = Olivia e Sales = 346.

Immagine che illustra l’array temporaneo con TRUE/1 dove la condizione è soddisfatta:

Vantaggi della logica booleana

• Permette confronti >, <, >=, <=.
• Permette di combinare condizioni con AND (moltiplicazione) o OR (somma).
• Non richiede modifiche ai dati originali (non crea colonne helper).

Limitazioni

• Le formule possono apparire meno leggibili se combini molti criteri.
• Se usi OR (somma) e più righe soddisfano condizioni, XLOOKUP restituirà la prima corrispondenza trovata (da cima a fondo) a meno che non usi ordine inverso.

Esempi di varianti utili

• AND tra tre criteri:

=XLOOKUP(1,(col1=val1)*(col2=val2)*(col3=val3),return_range)

• OR tra criteri (prima corrispondenza):

=XLOOKUP(1,(col1=val1)+(col2=val2),return_range)

• Con confronto numerico (maggiore di):

=XLOOKUP(1,(colA>100)*(colB=G1),return_range)

Note su performance: per tabelle molto grandi (decine di migliaia di righe) calcoli su array temporanei possono essere più lenti rispetto a colonne helper o indici strutturati ottimizzati. Testare le performance prima di adottare la soluzione su fogli di lavoro critici.

Esempio 2: più criteri con concatenazione

Concatenare i valori di ricerca e le colonne è un approccio semplice e leggibile. Funziona bene quando la combinazione di colonne forma una chiave unica e non sono necessari confronti complessi.

Formula esempio:

=XLOOKUP(
  G1&G2,
  T_Managers[Drink]&T_Managers[Country],
  T_Managers[[Manager]:[Sales]],
  "No result"
)

Spiegazione:

• G1&G2 costruisce la stringa di ricerca, ad esempio “CoffeeSpain”.
• T_Managers[Drink]&T_Managers[Country] crea un array di stringhe combinate per ogni riga, ad esempio “Apple juiceAustralia”, “CoffeeSpain”, ecc.
• XLOOKUP cerca la stringa concatenata e restituisce i valori dalle colonne Manager e Sales.

Vantaggi della concatenazione

• Formula più leggibile e semplice da scrivere.
• Buona scelta se la chiave risultante è sempre univoca.
• Evita conversioni implicite tra booleani e numeri.

Limitazioni della concatenazione

• Sensibile a spazi, formattazione e casse: “Coffee Spain” ≠ “CoffeeSpain”.
• Non adatta a confronti non-equality (>, <, ecc.).
• Se la combinazione non è unica, XLOOKUP restituisce la prima corrispondenza.

Suggerimenti pratici per concatenazione robusta

• Usare TRIM() per rimuovere spazi indesiderati:

=TRIM(T_Managers[Drink])&"|"&TRIM(T_Managers[Country])

• Inserire un separatore esplicito (es. “|” o “~”) per evitare ambiguità tra valori concatenati.
• Usare UPPER() o LOWER() per normalizzare la maiuscole/minuscole:

=UPPER(TRIM(G1))&"|"&UPPER(TRIM(G2))

Confronto diretto: Boolean vs Concatenazione

• Leggibilità: concatenazione più leggibile.
• Flessibilità: booleana più potente (operatori >, <, OR, AND, combinazioni miste).
• Robustezza: concatenazione richiede cura su spazi e formati.
• Performance: simili per tabelle di dimensione media; testare su dataset grandi.

Decisione pratica: se la chiave è chiaramente unica e i valori sono puliti, preferisci la concatenazione. Se devi usare confronti o combinare condizioni complesse, scegli la logica booleana.

Abbinare XLOOKUP ad altre funzioni utili

• FILTER: restituisce tutte le righe che soddisfano i criteri (non solo la prima) — utile per set di risultati multipli.

  Esempio:

  =FILTER(T_Managers[[Manager]:[Sales]],(T_Managers[Drink]=G1)*(T_Managers[Country]=G2),"No result")

• INDEX/MATCH: alternativa storica; INDEX con MATCH multipli richiede colonne helper o formule matriciali più complesse.

• UNIQUE: se vuoi identificare quante combinazioni distinte esistono.

Quando XLOOKUP potrebbe fallire o dare risultati inattesi

• La combinazione non esiste -> restituisce il valore specificato in d (es. “No result”).
• Valori con spazi o formattazione diversa -> concatenazione non corrisponde.
• Tipi diversi (testo vs numero): “123” non è uguale a 123.
• Più righe soddisfano la condizione con OR -> XLOOKUP restituisce la prima corrispondenza.
• Performance scadente su tabelle molto grandi con molte colonne calcolate al volo.

Mitigazioni pratiche

• Normalizza input e colonne (TRIM, VALUE, UPPER/LOWER).
• Usa separatori nelle concatenazioni.
• Considera colonne helper calcolate e materializzate se il foglio è usato intensamente.

Checklist rapida prima di implementare

• Verificare versione di Excel (2021/365 o web)
• Confermare unicità della chiave (per concatenazione)
• Normalizzare dati (TRIM, casse, tipi)
• Valutare alternative (FILTER per più risultati)
• Testare performance su dataset reale

Mini-metodologia per scegliere la soluzione giusta

1. Definisci il requisito: vuoi la prima corrispondenza o tutte le corrispondenze?
2. La chiave combinata è univoca? Sì -> concatenazione. No -> booleana o FILTER.
3. Serve confronto non esatto (> <)? -> booleana.
4. Hai limiti di performance? -> prova con colonne helper o query esterne (Power Query).
5. Implementa, poi scrivi test case e convalida con dati di esempio reali.

Test case e criteri di accettazione

Esempi di test da eseguire su qualsiasi implementazione:

• Caso positivo singolo: inserire valori presenti -> attendersi manager e vendite corretti.
• Caso non trovato: inserire combinazione inesistente -> restituisce “No result” o testo definito.
• Caso multiplo con OR: se si usa OR, verificare quale riga viene restituita (la prima) e confermare che è accettabile.
• Pulizia stringhe: testare valori con spazi iniziali/finali e differenze di maiuscole.
• Performance: testare ricerca su 10k+ righe e misurare tempi di aggiornamento.

Criteri di accettazione (esempio)

• La formula restituisce il manager corretto entro 1 secondo su dataset standard (< 50k righe).
• La formula gestisce input con spazi errati tramite TRIM.
• I risultati sono coerenti rispetto ai casi di test forniti.

Esempi pratici rapidi (snippet)

Ricerca AND su tre colonne:

=XLOOKUP(1,(Table[Col1]=A1)*(Table[Col2]=B1)*(Table[Col3]=C1),Table[[Result]:[OtherResult]],"No match")

Concatenazione con separatore e normalizzazione:

=XLOOKUP(UPPER(TRIM(G1))&"|"&UPPER(TRIM(G2)),UPPER(TRIM(T_Managers[Drink]))&"|"&UPPER(TRIM(T_Managers[Country])),T_Managers[[Manager]:[Sales]],"No result")

Usare FILTER per tutte le corrispondenze:

=FILTER(T_Managers[[Manager]:[Sales]],(T_Managers[Drink]=G1)*(T_Managers[Country]=G2),"No result")

Decision tree per scegliere il metodo

flowchart TD
  A[Hai bisogno di cercare su più criteri?] -->|No| B[Usa XLOOKUP standard]
  A -->|Sì| C{La combinazione è univoca e solo uguaglianze?}
  C -->|Sì| D[Usa concatenazione]
  C -->|No| E{Hai bisogno di confronti > < o OR complessi?}
  E -->|Sì| F[Usa logica booleana 'AND/OR']
  E -->|No, voglio tutte le corrispondenze| G[Usa FILTER]
  D --> H[Normalizza dati 'TRIM, UPPER']
  F --> I[Testa performance su dataset reale]
  G --> I

Ruoli: checklist operativa

• Sviluppatore foglio Excel
  • Normalizza le colonne chiave
  • Aggiungi separatori se usi concatenazione
  • Documenta formule complesse con commenti nelle celle
• Analista dati
  • Fornisci dati di test rappresentativi
  • Verifica unicità delle chiavi
• Product owner / stakeholder
  • Conferma requisito: prima corrispondenza o tutte?
  • Approvare compromessi su performance vs manutenibilità

Piccola guida di best practice

• Preferisci formule leggibili e documentate.
• Se le formule diventano troppo complesse, valuta colonne helper o Power Query.
• Metti messaggi chiari per i casi “No result” per facilitare il debug dell’utente.
• Usa nomi di tabella strutturati (es. T_Managers) per leggibilità e manutenzione.

Glossario in una riga

• XLOOKUP: funzione di Excel per cercare valori in un intervallo e restituire corrispondenze.
• FILTER: funzione che restituisce tutte le righe che soddisfano una condizione.
• TRIM: rimuove spazi iniziali e finali.
• CONCAT (o &): concatenazione di stringhe.
• Array dinamico: serie di valori restituiti da una formula che si espande automaticamente.

Riepilogo

• XLOOKUP supporta ricerche a più criteri usando la logica booleana o la concatenazione.
• Scegli la soluzione in base a unicità della chiave, complessità dei criteri e requisiti di performance.
• Normalizza sempre input e dati delle colonne per evitare discrepanze causate da spazi, casse o tipi.

Note finali

Se stai implementando fogli condivisi o automatizzati, documenta la scelta del metodo nella documentazione del file, includi i test case e considera colonne helper o Power Query se il foglio cresce in complessità.

Microsoft 365 include accesso ad app Office come Word, Excel e PowerPoint su più dispositivi, 1 TB di OneDrive e funzionalità aggiuntive. Verifica il tipo di abbonamento se hai bisogno di funzioni avanzate.