CERCA X: UN CERCA VERTICALE POTENZIATO

Oltre 1.000 professionisti utilizzano la nostra piattaforma per formarsi su contabilità, l’analisi, la modellazione finanziaria e molto altro.

Inizia ora anche tu ad accelerare la tua carriera!


Sei un utilizzatore appassionato di Excel CERCA VERTICALE ma non riesci a trovare una soluzione ai suoi limiti e agli errori che ti restituisce?

Ecco la soluzione: la funzione CERCA.X.

In questo articolo diviso per livelli, dal base al più avanzato, scopriremo cos’è CERCA X, cosa può fare questa potente funzione per te e come può aiutarti a risolvere i tuoi problemi di ricerca lavorando con un mini progetto che puoi scaricare qui.

La FinancetoBI Academy diventa Finprime Institute!

Abbiamo ascoltato i vostri feedback e i vostri suggerimenti e abbiamo creato la piattaforma definitiva!

Ma vogliamo festeggiare questo traguardo con voi! Per questo motivo potrete ottenere GRATUITAMENTE il nostro Corso di Accelerazione di Excel ed entrare a far parte di questo nuovo progetto!

Finprime Institute

CERCA.X è una potente funzione Excel che amplia le funzionalità di CERCA VERTICALE.

Ad esempio, mentre CERCA VERTICALE ci consente di cercare dati solo a destra della matrice di ricerca, CERCA X ci offre la flessibilità di cercare dati ovunque all’interno dei nostri dati. Ciò apre nuove possibilità per l’analisi e la manipolazione dei dati.

La sintassi della funzione CERCA.X è la seguente:

= CERCA.X(valore_ricerca; matrice_ricerca; matrice_restituita; [se_non_trovato]; [modalità_confronto]; [modalità_ricerca])

cerca x excel

Ecco una panoramica degli argomenti:

  • valore_ricerca: il valore che desideriamo cercare all’interno della matrice di ricerca.
  • matrice_ricerca: il range o la matrice di dati in cui vogliamo eseguire la ricerca.
  • matrice_restituita: il range o la matrice di dati da cui vogliamo recuperare i valori corrispondenti.

Gli altri tre argomenti sono facoltativi, ma li vedremo comunque più avanti nel nostro articolo.

Ecco la sfida. Il tuo capo ti ha chiesto di completare questa tabella, aggiungendo le informazioni relative alla divisione, alla sotto divisione, e i valori legati al bonus.

Ti ha inoltre chiesto di preparargli un piccolo riassunto a destra, dove con un semplice menù a tendina può selezionare il collaboratore e ottenere subito le informazioni richieste.

cerca.x

In una seconda tabella, ti ha invece fornito un database con le informazioni di riferimento.

cerca.x excel

Vediamo subito come fare con la funzione di Excel CERCA.X.

Iniziamo dalla Divisione in cui è impiegato il personale.

Nella cella C10 digitiamo = CERCA.X e premiamo TAB sulla tastiera per selezionarla.

excel cerca.x

Quello che dobbiamo cercare è il nome del dipendente, per cui per il primo argomento selezioniamo la cella A10, e premiamo punto e virgola per passare all’argomento successivo.

Funzioni Excel

Con la funzione CERCA.X Excel ci chiede ora la matrice di ricerca, ovvero la colonna in cui cercare il nome, che nel nostro caso è la colonna nome del foglio con i dati dei dipendenti (foglio “Database” del documento di lavoro).

Per selezionare la colonna in cui cercare il valore, ci riferiamo ad essa selezionandola, in questo modo:

cerca verticale

e usando F4 per bloccarne i riferimenti e ancora punto e virgola per passare all’argomento successivo.

Riferimenti assoluti

Nella matrice restituita selezioniamo ora la colonna da cui estrarre il dato di cui abbiamo bisogno, nel nostro caso la colonna “Divisione” del foglio “Database” e di nuovo F4 per bloccarne i riferimenti.

cerca x excel

Ecco come apparirà la funzione:

cerca x come funziona

Ora premiamo semplicemente invio.

Ecco che CERCA X ci ha dato immediatamente il valore che cercavamo:

cerca x excel

Come abbiamo notato, gli altri argomenti sono facoltativi e ancora meglio, a differenza di CERCA.VERT, con CERCA.X Excel non ci ha chiesto il tipo di corrispondenza, se esatta o approssimativa.

Con CERCA.X infatti il tipo di corrispondenza è per default “esatta”.

Facciamo doppio clic con il mouse sul quadratino in basso a destra della cella e portiamo la formula in tutte le celle successive.

funzione cerca excel

Cosa accade se nella colonna che contiene il nome, il nome non è completo ma ad esempio nella cella A10 c’è scritto semplicemente Robert, quindi senza il cognome?

cerca verticale

La funzione CERCA VERTICALE, come per CERCA.X, ci restituirebbe un errore, ma quelli che fa in più la funzione CERCA.X è darci la possibilità di “aggirare” questo problema facendogli riconoscere ugualmente il nome da ricercare e in modo semplice:

  • nella cella C10 in cui abbiamo inserito la nostra funzione premiamo F2 per entrare nella cella e prima della parentesi di chiusura premiamo punto e virgola per passare al quarto argomento ([se non trovato]). Quello che a noi interessa è in realtà il quinto argomento, modalità_confronto, per cui passiamo direttamente a questo argomento premendo ancora punto e virgola.
funzione cerca excel

Tra le diverse opzioni che questo argomento ci offre, l’ultima riguarda il carattere jolly, che può essere un asterisco o un punto interrogativo. Inseriamo quindi 2 per chiedere a CERCA.X di attivare quella funzione.

Ora posizioniamoci nel primo argomento e subito dopo il riferimento di cella inseriamo: &”*”, e premiamo invio.

excel cerca.vert

Come possiamo vedere, anche se il nome non è digitato correttamente, la funzione cerca X ci restituisce il risultato desiderato.

cerca verticale excel

Facciamo quindi doppio clic sul solito quadratino in basso a destra nella cella per copiare verso il basso la formula aggiornata.

Passiamo ora alla colonna Sotto divisione. Prima però copiamo per intero la formula inserita nella colonna Divisione e ora incolliamola nella colonna Sotto divisione.

cerca x

Come vediamo ci restituisce un errore. Questo succede perché nell’impostare all’inizio la funzione, non abbiamo bloccato i riferimenti della cella A10 del dipendente. Infatti, copiando la funzione e incollandola nella prima colonna subito a destra, il riferimento al dipendente si è “spostato” di una colonna a destra, perdendo di fatto il riferimento iniziale.

Abbiamo volutamente inserito questo errore in modo da mostrarvi il significato dei riferimenti assoluti e di quelli relativi.

Torniamo quindi nella prima cella della colonna Divisione, premiamo F2 e nel primo argomento, premiamo F4 fino a quando il simbolo del dollaro $ non è solo subito dietro la lettera “A”, che dovrà essere così: $A10:

excel cerca.x

Ora possiamo premere Invio. Di nuovo, doppio clic sul quadratino in basso a destra per copiare questo aggiornamento anche in tutte le celle in basso.

Copiamo ora di nuovo la cella e incolliamola nella cella adiacente nella colonna Sotto divisione.

Cambiamo ora la colonna da cui estrarre la sotto divisione, che se andiamo a vedere nella tabella con i dati dei dipendenti nel foglio “Database” vediamo che nel nostro caso non è la colonna B ma è la colonna C.

Premendo quindi F2 nella prima cella della colonna Sotto divisione (cella D10), posizioniamoci prima del riferimento di colonna nell’argomento matrice_restituita e cambiamo la colonna B con la colonna C.

funzione cerca excel

E ora possiamo copiare la formula in tutte le righe successive.

Verifichiamo quindi che il risultato sia corretto. Prendiamo ad esempio Scarlett Johansson e verifichiamo la sotto divisione con il database.

Abbiamo “software” nel database e la nostra tabella ci ha riportato lo stesso risultato, per cui è corretto.

excel cerca.x

Proviamo ora con Benedict Cumberbatch. Dal database vediamo qui che era impiegato nella vendita diretta, ma poi si è spostato nella sezione e-commerce, che è la posizione più aggiornata.

cerca x

La nostra tabella però ci ha dato come risultato Vendita diretta, per cui non va bene.

cerca x

Lo stesso vale ad esempio anche per Chris Evans.

Ecco che CERCA X interviene dandoci lo strumento per ottenere il risultato corretto.

Torniamo nella nostra tabella e nella cella D10 premiamo F2 per entrare nella funzione. Prima della parentesi premiamo ora punto e virgola per andare nel sesto argomento.

Vediamo che ci offre diverse possibilità, dove le prime due riguardano la possibilità di cercare il valore dall’alto verso il basso, metodo di default, e dal basso verso l’alto:

cerca.x excel

e poiché il nostro elenco è ordinato per data quest’ultima opzione è quella che fa per noi, perché ci da il dato più recente, digitiamo allora -1 per selezionarla.

cerca verticale excel

Premiamo ora invio e copiamo la formula verso il basso, ed ecco che risultati ora sono corretti.

Se controlliamo infatti Benedict Cumberbatch e Chris Evans vediamo che ora vengono indicati nelle sotto divisioni più recenti.

cerca verticale excel

Per il Livello 4, spostiamoci nel riquadro di riassunto a destra e inseriamo prima di tutto un menù a tendina per poter selezionare velocemente il nome del dipendente.

Posizioniamoci quindi sulla cella H10 e premiamo ALT → A → CC → V.

Nel riquadro “Consenti”, selezioniamo “Elenco”, e nella stringa Origine selezioniamo l’intervallo di celle che contiene il nome, e premiamo OK.

excel cerca.x

Apriamo ora il menù a tendina e selezioniamo il primo nome che appare, “Robert”.

excel cerca.vert

Nella cella sotto il nome “Divisione” (I10), inseriamo la funzione CERCA.X manualmente, chiediamo di cercare il nome presente nella cella a sinistra accompagnato da &”*” e bloccandone come sempre i riferimenti con F4.

cerca x excel

Come intervallo di ricerca inseriamo sempre la colonna “Nome” nella tabella di origine.

cerca.x excel

e come terzo argomento anziché selezionare una sola colonna, selezioniamo insieme entrambe le colonne Divisione e Sotto divisione,

cerca.x excel

Il quarto argomento lo lasciamo vuoto selezionando sempre punto e virgola. Lasciamo vuoto questa volta anche il quinto argomento, quindi ancora punto e virgola e andiamo all’ultimo argomento.

Ora nell’ultimo argomento digitiamo -1 per avere la divisione e la sotto divisione più aggiornate così come nell’esempio visto nel Livello 3, premiamo ora invio.

Come vediamo CERCA X cerca ora contemporaneamente il valore presente sia nella colonna “Divisione” e per espansione nella colonna “Sotto divisione”, questo con un’unica funzione.

cerca x e cerca vert

Passiamo all’ultimo e più avanzato esempio di questo articolo: il bonus.

Nel foglio database, vediamo che in corrispondenza di un livello di salario, il dipendente può ottenere un bonus.

Ad esempio dai 30.000 ai 35.000 € il bonus sarà pari a zero, da 35.000 € fino ai 40.000 sarà il 2%, dai 40.000 ai 45.000 € sarà pari al 3% e così via.

cerca orizzontale

Vediamo come impostare questa condizione con la funzione CERCA X

Andiamo nella colonna bonus della nostra tabella, inseriamo la funzione CERCA.X e selezioniamo come primo argomento la cella B10, corrispondente alla colonna salario, e blocchiamo il riferimento di colonna con F4 (il simbolo del dollaro $ dovrà essere prima della lettera B per non bloccare anche il riferimento di riga: $B10).

Come secondo argomento selezioniamo invece la colonna “Salario” nel foglio Database e come terzo argomento selezioniamo a questo punto la colonna con i bonus.

Il quarto argomento, che riguarda l’eventualità che non trovi il risultato, in questo caso non ci interessa per cui lo lasciamo vuoto.

Il quinto argomento è quello che dobbiamo utilizzare per dire ad Excel qual è il livello di bonus che spetta al dipendente, quindi nel nostro caso digitiamo -1 ovvero “elemento successivo più piccolo” . La funzione apparirà così:

funzione cerca.x

A questo punto premiamo INVIO.

In questo modo potrà capire ad esempio che nel caso di Scarlett Johansson, con un salario di 37.000 €, il suo livello di bonus è quello corrispondente al salario che va da 35.000 € , quindi l’elemento successivo più piccolo, a 40.000 €:

FinancetoBI Academy

Come sempre doppio clic sul quadratino in basso a destra nella cella per copiare la funzione anche nelle righe successive.

funzione cerca.x

Come ultima cosa, vediamo che il bonus corrispondente a Beneditct Cumberbatch non lo ha trovato. Questo succede perché il suo livello di salario non è compreso nell’elenco dei bonus.

funzione cerca excel

Per risolvere questo problema, utilizziamo il quarto argomento, quello che non abbiamo ancora utilizzato. Ci posizioniamo quindi nell’argomento [se_non_trovato] e inseriamo tra virgolette “Valore non trovato” e premiamo invio.

excel cerca.vert

Ora con CERCA.X Excel, qualora non dovesse trovare il valore corrispondente, ci restituirà “Valore non trovato”.

Abbiamo visto come la funzione di CERCA X sia molto più potente di CERCA VERTICALE, dandoci molte possibilità in più di elaborazione rispetto ormai a questa vecchia funzione.

Provate ora a pensare ad un progetto o un lavoro su cui potreste utilizzare la funzione CERCA X e come vi può aiutare nel vostro lavoro, considerando che:

  • non ha problemi a cerca verso destra o verso sinistra;
  • non è necessario specificare che si vuole una corrispondenza esatta, questa viene utilizzata di default;
  • abbiamo la possibilità di cercare l’ultimo valore, quindi dal basso verso l’altro, anziché avere necessariamente il primo dall’alto verso il basso;
  • funziona sia verticalmente che orizzontalmente.

Corso Excel di Accelerazione

Imparando e padroneggiando queste funzioni, velocizzerete notevolmente il vostro lavoro e le vostre analisi. Per saperne di più, consultate queste altre risorse della FinancetoBI Academy:

7 Funzioni di Excel per accelerare il tuo lavoro

5 tecniche su come inserire formule in Excel

Media ponderata in Excel

Carlo Liotto
Carlo Liotto
BI & Corporate Finance Consultant - Trainer
financetobi.com/

Benvenuti! Sono Carlo Liotto e sono il fondatore della FinancetoBI Academy. Sono uno dei principali consulenti in Italia specializzati in Finanza Aziendale, Financial Modeling e Business Intelligence. I miei clienti spaziano da start-up a PMI con fatturati da 1 a 200mln di euro in diverse industries, così come studi professionali, commercialisti e consulenti e grazie alla fusione di queste competenze generano un enorme valore dalle informazioni aziendali, per loro o per i loro clienti. Ho inoltre scritto un libro sulla strategia per start-up con uno dei più importanti editori in Italia e che potete trovare nel business center di Fisco e Tasse: https://bit.ly/startup-fiscoetasse. Nel tempo libero, metto casco, guanti, e giacca di pelle e mi metto in sella alla mia moto verso i passi di montagna.

Related Posts