Come usare XLOOKUP() con più criteri in Excel

XLOOKUP() in Excel è attualmente la funzione più recente del gruppo di funzioni di ricerca (LOOKUP(), VLOOKUP(), HLOOKUP() e XLOOKUP()) e offre numerosi vantaggi, funzionalità estese e maggiore flessibilità.

In questo tutorial, vedremo innanzitutto a cosa serve la funzione XLOOKUP() in Excel e perché è migliore delle vecchie funzioni di ricerca; poi ne analizzeremo la sintassi di base e infine passeremo al punto chiave: usare la funzione XLOOKUP() con più criteri.

Se ti servono le basi di Excel, il corso per principianti Introduction to Excel è il posto giusto da cui partire.

TL;DR

• XLOOKUP() è la funzione di ricerca più recente di Excel. È più flessibile e veloce di VLOOKUP(), HLOOKUP() e LOOKUP(), con supporto per ricerche multidirezionali e corrispondenze parziali.

• Per cercare con più criteri, hai due approcci principali: concatenazione (unire i valori di ricerca con &, ad es. =XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)) oppure espressioni booleane (moltiplicare le condizioni, ad es. =XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)).

• La concatenazione è più semplice e leggibile, ma le espressioni booleane offrono maggiore flessibilità.

• I parametri opzionali ampliano ulteriormente la funzione: [if_not_found] restituisce un testo personalizzato quando non ci sono corrispondenze, [match_mode] abilita corrispondenze approssimate o con caratteri jolly e [search_mode] permette di invertire la direzione della ricerca.

Perché usare XLOOKUP() in Excel

La funzione XLOOKUP() cerca in un intervallo o in una matrice di dati e restituisce l’elemento corrispondente alla prima corrispondenza. Se non viene trovata alcuna corrispondenza, XLOOKUP() può restituire una corrispondenza approssimata, se viene specificato un tipo di corrispondenza. Per molti aspetti, la funzione XLOOKUP() supera le sue predecessore in Excel (VLOOKUP(), HLOOKUP() e LOOKUP()).

In particolare, consente di:

• cercare dati sia in orizzontale che in verticale e in qualsiasi direzione
• usare criteri multipli di ricerca
• ottenere una corrispondenza approssimata, con impostazione predefinita sulla corrispondenza esatta
• ottenere una corrispondenza parziale
• restituire più colonne e righe
• restituire un testo personalizzato quando non si trova alcuna corrispondenza.

Inoltre, la funzione XLOOKUP() è più veloce delle vecchie funzioni di ricerca in Excel, cosa importante quando si cerca in grandi quantità di dati.

Come usare XLOOKUP() con un solo criterio

Facciamo una breve panoramica della sintassi di base della funzione XLOOKUP():

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

dove i parametri obbligatori sono:

• lookup_value—il valore da cercare

• lookup_array—l’array o l’intervallo in cui cercare

• return_array—l’array o l’intervallo da cui restituire

e i parametri opzionali sono:

• [if_not_found]—il testo da restituire quando non viene trovata alcuna corrispondenza

• [match_mode]—il tipo di corrispondenza (esatta o approssimata) e cosa restituire se non si trova alcuna corrispondenza.

• [search_mode]—la modalità di ricerca da usare (ricerca diretta o inversa, ricerca binaria su array ordinati in ordine crescente o decrescente).

Vedremo esempi dell’uso dei parametri opzionali più avanti in questo tutorial.

Per imparare o ripassare altre funzioni e formule utili in Excel, dai un’occhiata al tutorial The 15 Basic Excel Formulas Everyone Needs to Know e alla Excel Formulas Cheat Sheet.

Come usare XLOOKUP() con più criteri

Dalla sua sintassi di base, vediamo che per impostazione predefinita la funzione XLOOKUP() di Excel è progettata per lavorare con un singolo valore di ricerca di una variabile specificata.

Tuttavia, possiamo adattare questa funzione per usarla su più variabili contemporaneamente, cercando un valore diverso in ciascuna variabile. In altre parole, possiamo effettuare la ricerca con XLOOKUP() applicando più criteri. Per farlo, ci sono due approcci principali: concatenazione ed espressioni booleane, che vedremo entrambi tra poco.

Prima di addentrarci nei dettagli tecnici, guardiamo una tabella di Excel con cui lavoreremo. Questa tabella fornisce informazioni su 10 gatti, inclusi nomi, colori ed età:

Nei nostri esempi, useremo XLOOKUP() per trovare una delle tre caratteristiche di un gatto usando le altre due.

Va benissimo che la nostra tabella sia molto semplice e possiamo trovare chiaramente ciò che ci serve ogni volta senza alcuna funzione. Qui, dobbiamo capire i principi base dell’uso di XLOOKUP() con più criteri. Una volta compresi, potremo estenderli facilmente a scenari reali più complessi, ad es. quando abbiamo più di due criteri di ricerca o requisiti specifici da soddisfare.

XLOOKUP() con più criteri usando la concatenazione

L’approccio della concatenazione è piuttosto lineare: dobbiamo concatenare tra loro i valori e gli array di ricerca corrispondenti. Vediamo come funziona.

Supponiamo di voler trovare l’età di una gatta bianca e grigia chiamata Nala. Per comodità, abbiamo aggiunto questi due criteri sullo stesso foglio in cui è la nostra tabella:

In questo caso, la formula per XLOOKUP() è la seguente:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)

Restituirà 2,5, l’età della prima Nala bianca e grigia (la mia gatta 😺). Ricorda che XLOOKUP() restituisce l’elemento corrispondente alla prima corrispondenza.

Per capire cosa rappresentano i primi due componenti della formula sopra — quelli con la e commerciale — possiamo eseguire ognuno di essi in una cella separata di Excel:

=B2:B11&C2:C11

Il risultato è NalaWhite e Grey.

=B2:B11&C2:C11

Il risultato è:

Sì, è tutto qui: abbiamo semplicemente concatenato tra loro i valori e gli array di ricerca corrispondenti, seguendo la sintassi base di XLOOKUP() vista prima.

XLOOKUP() con più criteri usando espressioni booleane

Ora svolgiamo lo stesso compito — trovare l’età di una gatta bianca e grigia chiamata Nala — usando il secondo approccio: le espressioni booleane. In questo caso, la formula per XLOOKUP() sarà la seguente:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Come previsto, restituirà 2,5, l’età della prima Nala bianca e grigia.

Esploriamo i componenti della formula. Qui, 1 significa TRUE, cioè stiamo cercando il valore TRUE nell’array di ricerca rappresentato dal secondo componente della formula — (B2:B11=G2)*(C2:C11=G3).

Questo componente, a sua volta, contiene due fattori, ciascuno dei quali verifica una certa condizione: il primo controlla se il nome corrisponde a quello cercato, mentre il secondo controlla se il colore corrisponde al colore cercato. Eseguiamo ciascun fattore in una cella separata di Excel:

=B2:B11=G2

Il risultato è:

=C2:C11=G3

Il risultato è:

Ora eseguiamo l’intero secondo componente della formula:

=(B2:B11=G2)*(C2:C11=G3)

Il risultato è:

Sebbene vediamo due valori pari a 1 nel risultato sopra (ovvero due valori TRUE), la funzione XLOOKUP() restituisce l’elemento corrispondente alla prima corrispondenza.

L’approccio basato su espressioni booleane per eseguire XLOOKUP() con più criteri sembra molto più complesso e meno intuitivo rispetto a quello basato sulla concatenazione. E sarebbe ancora peggio se usassimo più di due criteri di ricerca. Perché allora non usare sempre la concatenazione?

L’approccio con espressioni booleane ci dà molta più flessibilità quando usiamo XLOOKUP() con più criteri. Vediamone i vantaggi rispetto alla concatenazione.

Verificare il valore FALSE

Nella formula sopra, stavamo verificando se l’espressione booleana fosse TRUE, passando il valore 1:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

In alcuni casi, potremmo aver bisogno di controllare se l’espressione booleana è invece FALSE. Ad esempio, vogliamo trovare l’età del primo gatto nella nostra tabella che non sia una Nala bianca e grigia. Dovremmo modificare la formula così:

=XLOOKUP(0, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Il risultato sarà 1, l’età del primo gatto che non è una Nala bianca e grigia (più precisamente, è una Nala tricolore, ma non bianca e grigia).

Usare operatori logici

Con le espressioni booleane non siamo limitati al solo controllo di uguaglianza. Poniamo di voler trovare il colore di una gatta chiamata Nala che ha meno di 2 anni:

In questo caso, la formula per XLOOKUP() sarà:

=XLOOKUP(1, (B2:B11=G2)*(D2:D11<G3), C2:C11)

Il risultato sarà Tricolor.

Soddisfare almeno un criterio

Finora abbiamo verificato la corrispondenza a tutti i criteri forniti. In altri scenari, potremmo dover soddisfare almeno uno dei criteri.

Per vedere come funziona, torniamo al nostro compito iniziale — trovare l’età di una gatta bianca e grigia chiamata Nala:

Questa volta, però, vogliamo trovare l’età di un gatto che sia o chiamato Nala o bianco e grigio. La formula XLOOKUP() in questo caso sarà:

=XLOOKUP(1, (B2:B11=G2)+(C2:C11=G3), D2:D11)

Restituisce 1, l’età di una Nala tricolore.

Per ricordare, quando cercavamo l’età di un gatto che fosse sia chiamato Nala che bianco e grigio, la formula era la seguente:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

La sostituzione di * con + ha fatto la differenza.

Se vuoi scoprire tutto il potenziale di Excel, valuta un percorso di competenze completo, pratico e ben strutturato come Excel Fundamentals.

Parametri opzionali per XLOOKUP() con più criteri

Come nell’uso regolare di XLOOKUP() con un singolo criterio, possiamo utilizzare opzioni aggiuntive della funzione anche con più criteri. Qui entrano in gioco i parametri opzionali [if_not_found], [match_mode] e [search_mode].

Vediamo rapidamente esempi dell’uso di ciascuno di questi parametri con XLOOKUP() e più criteri. Per semplicità, in ogni esempio applicheremo l’approccio della concatenazione.

Restituire un testo fornito quando non si trova alcuna corrispondenza

Qui stiamo cercando l’età di una Nala nera — un gatto inesistente nella nostra tabella:

In questo caso, abbiamo aggiunto alla formula XLOOKUP() il parametro opzionale [if_not_found], a cui abbiamo assegnato il testo da restituire se non si trova alcuna corrispondenza:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, "No cat is found")

Infatti, la funzione ha restituito il testo fornito: No cat is found. Se non avessimo fornito alcun testo, la funzione avrebbe restituito #N/A.

Restituire una corrispondenza approssimata

Diciamo che stiamo cercando l’età di una Nala tricolore, ma non siamo sicuri se il suo colore sia scritto come "Tricolor" o "Tricolour" nella tabella. Qui dobbiamo usare una corrispondenza approssimata basata su caratteri jolly e passare alla funzione XLOOKUP() l’argomento [match_mode] uguale a 2.

Nella tabella sopra, abbiamo usato un asterisco (*), che rappresenta un numero qualsiasi di caratteri, incluso 0. Altri caratteri jolly si trovano nella documentazione di Microsoft Office.

La formula XLOOKUP() qui sarà:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , 2)

E restituirà 1, l’età della prima (e unica) Nala tricolore nella nostra tabella.

Nota che l’argomento 2 nella formula sopra indica che ci interessa una corrispondenza approssimata, non cercare l’età di una gatta Nala di colore "Tricolo*r". Nel nostro caso, l’asterisco ha catturato la parola "Tricolor", ma farebbe lo stesso anche con "Tricolour" o, per dire, "Tricolooor".

Eseguire una ricerca inversa

Ripetiamo l’esercizio iniziale — trovare l’età di una Nala bianca e grigia — ma questa volta inizieremo la ricerca dall’ultimo elemento.

Per farlo, dobbiamo aggiungere alla funzione XLOOKUP() il parametro opzionale [search_mode] impostato a -1, come sotto:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , , -1)

La funzione restituisce 7, l’età della prima Nala bianca e grigia dalla fine dell’array di ricerca.

XLOOKUP() vs. INDEX() e MATCH()

Nelle versioni più vecchie di Excel, per riprodurre una funzionalità simile all’uso di XLOOKUP() con più criteri, è necessario combinare le funzioni INDEX() e MATCH(). Senza entrare nella sintassi di entrambe, vediamo come trovare l’età della prima Nala bianca e grigia usando il vecchio metodo INDEX() + MATCH() e la nuova funzione XLOOKUP() (per i riferimenti di cella, consulta la tabella precedente):

=INDEX(D2:D11, MATCH(1, (G2=B2:B11)*(G3=C2:C11), 0))
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Sebbene gli argomenti sembrino in parte simili, ecco i principali vantaggi dell’uso di XLOOKUP() rispetto al vecchio metodo:

• Basta una sola funzione per questa operazione — non serve combinare funzioni.

• Possibilità di applicare l’approccio della concatenazione quando opportuno.

• Possibilità di usare parametri opzionali (alla funzione MATCH() mancano).