Come cercare valori corrispondenti da destra a sinistra in Excel?
La funzione CERCA.VERT in Excel è ampiamente utilizzata per cercare e recuperare dati, ma presenta un limite importante: consente di cercare valori solo da sinistra a destra. Ciò significa che il valore da cercare deve trovarsi nella prima colonna della tabella, mentre il valore da restituire deve essere posizionato a destra di tale colonna. E se invece avessi bisogno di cercare da destra a sinistra? Questa guida ti mostra diversi metodi per ottenere proprio questo risultato, con approcci basati su formule e soluzioni automatizzate tramite VBA. Conoscere e applicare queste tecniche di ricerca inversa ti permetterà di gestire in modo efficace layout di dati reali, che spesso non rispettano i vincoli tipici di CERCA.VERT.
Cerca.vert con valori corrispondenti da destra a sinistra
Cerca.vert valori da destra a sinistra con le funzioni CERCA.VERT e SE
Sebbene CERCA.VERT, da solo, non possa cercare valori da destra a sinistra, puoi adattare la struttura dei tuoi dati con la funzione SE per aggirare questa limitazione. Questo approccio è ideale se preferisci affidarti a funzioni Excel familiari e hai bisogno di una soluzione rapida, senza dover riorganizzare fisicamente il tuo set di dati.
Inserisci la formula seguente nella cella desiderata, quindi trascina il quadratino di riempimento fino alle celle in cui vuoi applicare la stessa formula per ottenere tutti i valori corrispondenti. Questo metodo funziona bene con intervalli statici, ma potrebbe richiedere aggiustamenti se i tuoi dati si espandono o modificano la propria struttura. Vedi screenshot:
=VLOOKUP(E2, IF({1,0}, $C$2:$C$9, $A$2:$A$9), 2, 0)
- E2: Questo è il valore che stai cercando. È la chiave che Excel cercherà nell’intervallo limitato.
- SE({1,0}; $C$2:$C$9; $A$2:$A$9): Questa parte della formula crea una tabella virtuale riordinando le colonne. Normalmente, CERCA.VERT può cercare solo nella prima colonna di una tabella e restituire un valore da una colonna a destra. Utilizzando SE({1,0}; ...), stai chiedendo a Excel di creare una nuova tabella con le colonne invertite:
♦ La prima colonna della nuova tabella è $C$2:$C$9.
♦ La seconda colonna della nuova tabella è $A$2:$A$9. - 2: Indica a CERCA.VERT di restituire il valore dalla seconda colonna della tabella virtuale creata dalla funzione SE. In questo caso, restituirà un valore dall'intervallo $A$2:$A$9.
- 0: Indica che desideri una corrispondenza esatta. Se Excel non trova una corrispondenza esatta per E2 nell'intervallo $C$2:$C$9, restituirà un errore.
Suggerimento: Se visualizzi un errore #N/D, assicurati che il valore cercato esista effettivamente nella colonna di ricerca. Questo approccio funziona bene con intervalli statici, ma potrebbe rivelarsi poco pratico per intervalli dinamici ampi o quando la dimensione e la posizione della tabella cambiano frequentemente.
Cerca.vert valori da destra a sinistra con Kutools per Excel
Se preferisci un approccio più intuitivo, Kutools per Excel offre funzionalità avanzate che semplificano le ricerche complesse, inclusa la possibilità di cercare facilmente da destra a sinistra. Ideale per chi desidera evitare formule complesse, Kutools permette di accedere rapidamente alle opzioni di Ricerca avanzata senza dover effettuare configurazioni manuali. Questo approccio si adatta perfettamente a dati di qualsiasi dimensione ed è particolarmente vantaggioso per chi esegue ricerche su base regolare.
Dopo aver installato Kutools per Excel, procedi come segue:
1. Fai clic su «Kutools» > «Super RICERCA.VERT» > «Cerca da destra a sinistra», vedi screenshot:
2. Nella finestra di dialogo «Cerca da destra a sinistra», procedi come segue:
- Seleziona le celle del valore da cercare e le celle di output dalle sezioni «Area di posizionamento dell'elenco» e «Intervallo di valore di ricerca»;
- Quindi, specifica gli elementi corrispondenti nella sezione «Intervallo dati», assicurandoti che gli intervalli siano corretti per evitare discrepanze.
- Infine, fai clic sul pulsante OK.
3. Ora i record corrispondenti sono stati restituiti in base all’intervallo di valori di ricerca dall’elenco a destra; vedi screenshot:
Se desideri sostituire il valore di errore #N/D con un testo personalizzato, ti basta fare clic sul pulsante «Opzioni», selezionare l’opzione «Sostituisci il risultato non trovato con un valore specificato» e digitare il testo desiderato.
Questa funzionalità è particolarmente utile quando si condividono fogli di lavoro, poiché impedisce che messaggi di errore appaiano nei risultati finali.
Cerca.vert valori da destra a sinistra con le funzioni INDICE e CONFRONTA
La combinazione di INDICE e CONFRONTA rappresenta un’alternativa versatile e potente a CERCA.VERT, in grado di cercare valori in qualsiasi direzione—sinistra, destra, alto o basso—offrendo una flessibilità superiore, soprattutto quando la colonna di ricerca non è la prima dell’intervallo dati. Questa soluzione è ideale per set di dati più ampi e per tabelle pivot, dove le posizioni delle colonne possono variare.
Immettere o copiare la formula seguente in una cella vuota per visualizzarne il risultato, quindi trascinare il quadratino di riempimento verso il basso fino alle celle in cui si desidera applicare la formula. Assicurarsi che gli intervalli includano i dati corretti e utilizzare riferimenti assoluti (con il simbolo $) se si intende mantenere fissi gli intervalli di ricerca durante la copia della formula.
=INDEX($A$2:$A$9,MATCH(E2,$C$2:$C$9,0))
- E2: Questo è il valore che stai cercando.
- MATCH(E2, $C$2:$C$9,0)La funzione CONFRONTA cerca il valore in E2 all'interno dell’intervallo $C$2:$C$9. Lo 0 specifica che si desidera una corrispondenza esatta; se il valore viene trovato, la funzione restituisce la sua posizione relativa nell’intervallo.
- INDEX($A$2:$A$9, ...)La funzione INDICE utilizza quindi la posizione restituita da CONFRONTA per individuare il valore corrispondente nell'intervallo $A$2:$A$9.
Suggerimenti: Se devi cercare dati su più colonne, puoi modificare l’intervallo di INDICE oppure ottimizzare la formula per gestire corrispondenze con più condizioni. Inoltre, se i tuoi dati vengono aggiornati spesso o coprono tabelle ampie, INDICE e CONFRONTA sono generalmente più affidabili di CERCA.VERT: non richiedono infatti di riorganizzare le colonne né di ricreare le tabelle di ricerca.
Cercare valori da destra a sinistra con la funzione XLOOKUP
Se utilizzi Excel 365 o Excel 2021, la funzione XLOOKUP rappresenta un’alternativa moderna e semplificata a VLOOKUP. È in grado di cercare valori in qualsiasi direzione, senza necessità di formule complesse. XLOOKUP è perfetta per chi desidera un approccio più pulito e flessibile ed è disponibile nell’ultima versione di Excel. A differenza di VLOOKUP o HLOOKUP, non richiede che la colonna di restituzione si trovi a destra della colonna di ricerca, offrendo una flessibilità decisamente superiore.
Immettere o copiare la formula seguente in una cella vuota per visualizzarne il risultato, quindi trascinare il quadratino di riempimento verso il basso fino alle celle destinate a contenere questa formula. Questo metodo si rivela particolarmente utile nelle cartelle di lavoro condivise o nella creazione di modelli pensati per strutture di dati variabili.
=XLOOKUP(E2,$C$2:$C$9,$A$2:$A$9)
- E2: Questo è il valore che stai cercando.
- $C$2:$C$9Questo è l'intervallo in cui Excel cercherà il valore presente in E2: si tratta dell'array di ricerca.
- $A$2:$A$9Questo è l'intervallo da cui Excel estrarrà il valore corrispondente: l'array di restituzione.
Avvertenze: CERCA.X non è disponibile nelle versioni di Excel precedenti a Excel 365 e Excel 2021. Per le versioni più vecchie, utilizza il metodo INDICE e CONFRONTA descritto sopra oppure valuta alternative come VBA.
Cercare valori da destra a sinistra con una macro VBA per una ricerca automatizzata da destra a sinistra
Quando le soluzioni basate su formule risultano troppo limitanti o quando è necessario automatizzare ricerche ripetitive, VBA (Visual Basic for Applications) offre un potente strumento per eseguire ricerche da destra a sinistra, gestendo criteri complessi ed elaborazioni in batch. Questo approccio si rivela particolarmente vantaggioso nella gestione di grandi set di dati, nell’automazione di flussi di lavoro o nell’esecuzione di operazioni difficilmente realizzabili con formule standard. Tuttavia, richiede l’abilitazione delle macro e una conoscenza di base dell’ambiente di sviluppo di Excel.
Scenari applicabili: Reportistica automatizzata, gestione di intervalli dinamici, lavoro con dati non contigui o in presenza di criteri condizionali o a più livelli.
Vantaggi: Estremamente flessibile, ripetibile e personalizzabile.Svantaggi: Richiede l’abilitazione delle macro e conoscenze di base di scripting; non è adatto a cartelle di lavoro condivise in cui le macro sono limitate.
Come utilizzare questa soluzione VBA:
1. Fare clic su Sviluppo > Visual Basic per aprire l’editor VBA. Nella nuova finestra Microsoft Visual Basic for Applications, fare clic su Inserisci > Modulo e incollare il codice seguente nella finestra del modulo:
Sub RightToLeftVlookup()
Dim lookupValue As Variant
Dim searchRange As Range, returnRange As Range
Dim resultCell As Range
Dim foundCell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set searchRange = Application.InputBox("Select the range to search (lookup column):", xTitleId, Type:=8)
Set returnRange = Application.InputBox("Select the range to return value from (target column):", xTitleId, Type:=8)
Set resultCell = Application.InputBox("Select the cell to output the result:", xTitleId, Type:=8)
lookupValue = Application.InputBox("Enter the value to look up:", xTitleId, Type:=2)
If searchRange Is Nothing Or returnRange Is Nothing Or resultCell Is Nothing Or lookupValue = "" Then
MsgBox "Operation cancelled.", vbExclamation
Exit Sub
End If
Set foundCell = searchRange.Find(lookupValue, LookIn:=xlValues, LookAt:=xlWhole)
If Not foundCell Is Nothing Then
Dim rowOffset As Long
rowOffset = foundCell.Row - searchRange.Rows(1).Row + 1
resultCell.Value = returnRange.Cells(rowOffset, 1).Value
Else
resultCell.Value = "#N/A"
End If
End Sub2. Per eseguire il codice, dopo averlo incollato, chiudere l’editor VBA e tornare a Excel. Premere quindi il tasto F5 oppure fare clic sul pulsante Esegui.
3. Segui le istruzioni per selezionare la colonna di ricerca (quella in cui si trova il valore da cercare), la colonna di restituzione (da cui desideri ottenere il risultato), la cella di output e il valore di ricerca stesso. La macro popolerà automaticamente la cella di output con il valore corrispondente tratto dalla colonna di restituzione, anche se questa si trova a sinistra della colonna di ricerca.
Risoluzione problemi e suggerimenti:
- Assicurati che l’intervallo di ricerca e l’intervallo di restituzione abbiano lo stesso numero di righe e siano entrambi verticali (allineati riga per riga) per ottenere risultati precisi.
- Se i tuoi dati si trovano in fogli diversi, seleziona l’intervallo in modo appropriato durante le richieste, assicurandoti che le righe siano allineate.
- Se il valore cercato non viene trovato, la macro restituirà «#N/D» nella cella specificata. Verifica attentamente l’intervallo di ricerca e l’ortografia del valore qualora dovessi ottenere questo risultato.
- Per applicare la macro a più intervalli di valori di ricerca, puoi adattarla ulteriormente per eseguire un ciclo su un intervallo di valori oppure eseguirla ripetutamente per ciascun valore necessario.
- Le macro devono essere abilitate nella cartella di lavoro per consentire il corretto funzionamento del codice VBA.
Questa macro VBA è un’opzione pratica per automatizzare ricerche da destra a sinistra e gestire criteri più avanzati o variabili, specialmente se si eseguono regolarmente tali operazioni o se si richiede una logica più complessa rispetto a quella fornita dalle formule.
Sebbene VLOOKUP sia uno strumento eccellente per ricerche di base, è limitato perché può cercare solo a destra della colonna di ricerca. Fortunatamente, Excel offre diversi metodi alternativi per recuperare valori da destra a sinistra con facilità ed efficienza, indipendentemente dalla posizione dei dati nel foglio di lavoro. Tra i suggerimenti pratici più utili: verificare attentamente gli intervalli, assicurarsi di usare corrispondenze esatte e scegliere il metodo più adatto alla propria versione di Excel e alle proprie esigenze operative. In caso di errori come #N/D, controllare sempre che il valore cercato esista effettivamente e che le dimensioni degli array coincidano. Quando si ricorre a VBA o a componenti aggiuntivi come Kutools, è fondamentale salvare il lavoro e testare le soluzioni su dati di esempio prima di applicarle a fogli importanti. Se desideri scoprire ancora più trucchi e suggerimenti per Excel,il nostro sito web offre migliaia di tutorial.
Altri articoli correlati:
- Cerca.vert valori tra più fogli di lavoro
- In Excel, possiamo utilizzare facilmente la funzione CERCA.VERT per recuperare valori corrispondenti all’interno di una singola tabella in un foglio di lavoro. Ma ti sei mai chiesto come eseguire una ricerca con CERCA.VERT tra più fogli di lavoro? Immagina di avere i seguenti tre fogli, ciascuno contenente un intervallo di dati, e di voler estrarre alcuni valori corrispondenti in base a criteri specifici provenienti da questi tre fogli.
- Utilizzare la corrispondenza esatta e approssimata di Cerca.vert in Excel
- In Excel, CERCA.VERT è una delle funzioni più importanti: cerca un valore nella colonna più a sinistra di una tabella e restituisce il valore corrispondente nella stessa riga di un intervallo specificato. Ma stai usando correttamente la funzione CERCA.VERT in Excel? In questo articolo ti spiegherò come utilizzarla al meglio.
- Cerca.vert con valore corrispondente dal basso verso l'alto in Excel
- Di solito, la funzione CERCA.VERT ti permette di cercare i dati dall’alto in basso, restituendo il primo valore corrispondente nell’elenco. A volte, però, potresti aver bisogno di cercare dal basso verso l’alto per ottenere l’ultimo valore corrispondente. Hai qualche buona idea su come gestire questa operazione in Excel?
- Cerca.vert e restituisci l’intera riga / Riga intera di un valore corrispondente in Excel
- Di solito puoi usare la funzione CERCA.VERT per trovare e restituire un valore corrispondente da un intervallo di dati, ma hai mai provato a trovare e restituire l’intera riga di dati in base a criteri specifici, come illustrato nell’immagine seguente?
- Cerca.vert e concatena più valori corrispondenti in Excel
- Come ben sappiamo, la funzione CERCA.VERT di Excel ci permette di cercare un valore e restituire i dati corrispondenti da un’altra colonna, ma di norma riesce a recuperare soltanto la prima corrispondenza quando ne esistono più di una. In questo articolo ti spiegherò come sfruttare CERCA.VERT per concatenare tutte le corrispondenze multiple in un’unica cella oppure in un elenco verticale.
Migliori Strumenti per la Produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un’efficienza mai vista prima.Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi