Come cercare con CERCA.VERT e restituire l’ultima corrispondenza in Excel?
La funzione CERCA.VERT di Excel è uno degli strumenti più utilizzati per cercare dati tabellari in base a un criterio specifico. Tuttavia, per impostazione predefinita, CERCA.VERT restituisce soltanto la prima occorrenza corrispondente al criterio di ricerca all’interno di un set di dati. Questo comportamento rappresenta spesso un limite quando i dati contengono valori ripetuti nell’intervallo di ricerca e si desidera invece recuperare l’ultima occorrenza di una voce corrispondente. Tale esigenza si presenta frequentemente in scenari come il tracciamento dello stato più recente, la ricerca delle ultime vendite relative a un cliente o l’identificazione dell’ultima voce registrata in elenchi cronologici. Per superare questa limitazione, Excel offre diversi approcci alternativi che combinano funzioni come CERCA, CERCA.X, INDICE e CONFRONTA, oltre a soluzioni intuitive di terze parti come Kutools per Excel. In questo articolo esploreremo il funzionamento di ciascun metodo, ne analizzeremo le applicazioni pratiche, ne evidenzieremo punti di forza e limiti e forniremo suggerimenti operativi per aiutarvi a eseguire facilmente una ricerca con CERCA.VERT e ottenere l’ultimo valore corrispondente.
Cercavert e restituisci l’ultima corrispondenza in Excel
Cerca.vert e restituisci l’ultimo valore corrispondente con la funzione CERCA
Sebbene CERCA.VERT non riesca a individuare direttamente l’ultimo elemento corrispondente, la funzione CERCA offre una soluzione ingegnosa. Questo approccio si rivela particolarmente efficace quando il set di dati non è ordinato e si cerca una formula compatibile con quasi tutte le versioni di Excel. La formula sfrutta il modo in cui CERCA gestisce matrici ed errori per trovare rapidamente l’ultima occorrenza.
Per estrarre l’ultima corrispondenza utilizzando CERCA, segui questi passaggi:
1. Seleziona la cella in cui desideri visualizzare l’ultima corrispondenza e inserisci la seguente formula:
=LOOKUP(2,1/($A$2:$A$12=E2),$C$2:$C$12)2. Premere Invio. Se si desidera applicare la formula ad altre righe, trascinare la maniglia di riempimento fino all’intervallo desiderato: in questo modo sarà possibile cercare l’ultima corrispondenza per più valori di ricerca in modo semplice.
$A$2:$A$12è la colonna in cui cercare (criteri).E2è la cella che contiene il valore da cercare.$C$2:$C$12è la colonna di restituzione (risultati).
1/($A$2:$A$12=E2)genera una matrice con un valore pari a 1 dove la condizione è vera e errori#DIV/0!altrove.CERCA.VERT(2, ...)sfrutta il fatto che CERCA.VERT ignora gli errori e cerca il numero 2 (che non è presente). CERCA.VERT trova quindi l’ultimo 1 nella matrice e restituisce il valore corrispondente dalla matrice dei risultati, ottenendo così l’ultima corrispondenza.
Suggerimenti e note:
- Assicurati che gli intervalli di ricerca e restituzione abbiano le stesse dimensioni e utilizza riferimenti assoluti quando copi la formula verso il basso.
- Se la matrice di ricerca contiene celle vuote o errori, i risultati potrebbero risentirne. Pulisci i dati o racchiudi la formula con
SE.ERROREse necessario. - Se ricevi
#N/D, verifica che il valore da cercare esista effettivamente nell’intervallo di origine.
Vantaggi: Funziona con tutte le versioni di Excel e non richiede l’inserimento speciale di formule matrice.
Limitazioni:Meno affidabile in presenza di celle vuote o valori di errore nella matrice di ricerca; non restituisce messaggi personalizzati in caso di errore senza l’uso di funzioni aggiuntive (ad es.)SE.ERRORE).
Cerca.vert e restituisci l’ultimo valore corrispondente con Kutools per Excel
Kutools per Excel offre un modo intuitivo ed efficiente per restituire l'ultimo valore corrispondente, ideale per gli utenti che preferiscono un approccio grafico, senza formule, o che devono elaborare rapidamente grandi set di dati. Lo strumento integrato Cerca dal basso verso l'alto della suite Super RICERCA.VERT di Kutools elimina la complessità delle formule, gestisce automaticamente gli errori e può produrre risultati simultaneamente in più celle, risparmiando tempo e riducendo al minimo gli errori di immissione. Questo metodo è particolarmente indicato per chi non ha familiarità con le funzioni avanzate di Excel o desidera evitare la modifica manuale delle formule.
Dopo aver installato Kutools per Excel, seguire questi passaggi:
1. Fai clic su Kutools > Super RICERCA.VERT > Cerca dal basso verso l'alto. Vedi lo screenshot:
2.Nella finestra di dialogo Cerca dal basso verso l'alto:
- Seleziona le celle contenenti il valore da cercare e quelle di destinazione nella sezione «Area di posizionamento dell'elenco e Intervallo di valore di ricerca».
- Specifica l’intervallo corrispondente nella sezione «Intervallo dati». Assicurati che tutti gli intervalli abbiano dimensioni e ordine identici per evitare disallineamenti.
- Fai clic su OK per applicare.
Una volta eseguito, Kutools restituirà immediatamente gli ultimi elementi corrispondenti, come mostrato di seguito:
Suggerimento: Se desideri visualizzare un messaggio personalizzato al posto di #N/D per le ricerche senza corrispondenza, fai clic su Opzioni, seleziona Sostituisci il risultato di output non trovato e restituisci il valore specificato e inserisci il testo desiderato.
Punti di forza: Non richiede la modifica delle formule. Supporta operazioni批量 e la sostituzione degli errori. Perfetto anche per chi è alle prime armi con Excel ed ideale per gestire grandi volumi di dati.
Svantaggi: Richiede l'installazione del componente aggiuntivo Kutools; la funzionalità è disponibile solo nelle versioni completa o di prova di Kutools.
Suggerimento pratico: Prima di confermare, verifica sempre l'output e l'intervallo di input per assicurarti che i risultati siano accurati, soprattutto se i dati cambiano dinamicamente.
Cerca.vert e restituisci l’ultimo valore corrispondente con le funzioni INDICE e CONFRONTA
La combinazione delle funzioni INDICE e CONFRONTA offre un metodo ancora più flessibile e compatibile con diverse versioni di Excel per trovare l’ultimo valore corrispondente, superando i limiti di CERCA.VERT. Questo approccio è estremamente versatile, non richiede che i dati siano ordinati ed è supportato da tutte le versioni di Excel, incluse quelle obsolete. Tuttavia, a seconda della versione in uso, potrebbe essere necessario inserire la formula come formula matrice per ottenere risultati corretti.
Per utilizzare questo metodo, procedere come segue:
1.Nella cella di destinazione, inserire la formula seguente:
=INDEX($C$2:$C$12,MATCH(2,1/($A$2:$A$12=E2)))2.Confermare la formula:
- In Excel 2019 o versioni precedenti, conferma con Ctrl + Maiusc + Invio (Excel aggiungerà automaticamente le parentesi graffe {}).
- In Microsoft 365 / Excel 2021 e versioni successive, premi semplicemente Invio.
3. Se sono presenti più valori nell’Intervallo di valori di ricerca, trascina la maniglia di riempimento verso il basso per applicare la formula alle righe adiacenti ed elaborarli in blocco.
$A$2:$A$12è la colonna di ricerca (criteri).E2è la cella che contiene il valore da cercare.$C$2:$C$12è la colonna di restituzione (risultati).
1/($A$2:$A$12=E2)genera una matrice con un valore pari a 1 dove la condizione di ricerca è vera e restituisce errori#DIV/0!altrove. In questo modo, i valori logici VERO/FALSO vengono convertiti in segnali numerici.CONFRONTA(2, 1/($A$2:$A$12=E2))chiede a Excel di cercare il numero 2 (che non è presente). Di conseguenza, CONFRONTA restituisce la posizione dell’ultimo 1 nella matrice, ovvero quella dell’ultima corrispondenza valida.INDICE($C$2:$C$12, ...)utilizza tale posizione per recuperare il valore corrispondente dall’intervallo di restituzione.
Consigli e suggerimenti:
- Assicurati che gli intervalli di ricerca e di restituzione abbiano lo stesso numero di righe e utilizza riferimenti assoluti quando copi la formula verso il basso.
- Se visualizzi
#N/Do#DIV/0!, controlla la presenza di chiavi non corrispondenti, celle vuote o errori nella matrice di ricerca. Per un output più pulito, racchiudi la formula conSE.ERRORE, ad esempio:=IFERROR(your_formula, "").
Vantaggi: Versatile e retrocompatibile con tutte le versioni di Excel.
Svantaggi: Leggermente più difficile da ricordare; richiede l’immissione come formula matrice nelle versioni precedenti di Excel.
Cerca.vert e restituisci l’ultimo valore corrispondente con la funzione CERCA.X
La funzione CERCA.X, disponibile in Excel 365, Excel 2021 e versioni successive, offre la soluzione più semplice e moderna per restituire l'ultima corrispondenza. Grazie a parametri che ti permettono di controllare la direzione della ricerca e la gestione degli errori, CERCA.X esegue ricerche dal basso verso l'alto, recuperando automaticamente l'ultimo valore corrispondente—senza dover ricorrere a complesse formule matrice legacy.
Per utilizzare CERCA.X per trovare l’ultimo valore corrispondente:
1. Nella cella di destinazione, inserisci la formula seguente, quindi trascina la maniglia di riempimento per applicarla agli altri valori dell’intervallo di ricerca, se necessario.
=XLOOKUP(E2, $A$2:$A$12, $C$2:$C$12, , , -1)
E2: il valore da cercare.$A$2:$A$12: la matrice in cui effettuare la ricerca.$C$2:$C$12: la matrice di valori da restituire., ,: due virgole indicano che i parametri opzionaliif_not_foundematch_modesono stati omessi (vengono utilizzati i valori predefiniti).-1:search_mode=-1esegue la ricerca dall’ultimo al primo elemento (dal basso verso l’alto), restituendo così la ultima corrispondenza.
Note pratiche:
- Non è richiesta alcuna immissione speciale per le matrici. Puoi fornire un messaggio personalizzato tramite
if_not_foundse necessario. - RICERCA.X restituisce l’ultima corrispondenza in base all’ordine della matrice fornita (dal basso verso l’alto con)
-1), indipendentemente dalla visibilità del filtro. - Per l’elaborazione in blocco, copia la formula verso il basso: ogni riga viene valutata in modo indipendente.
Vantaggi: Sintassi semplice; ricerca integrata dall’ultimo al primo elemento; niente formule matrice legacy richieste.
Limitazione: Disponibile solo in Excel 365, Excel 2021 e versioni successive.
Cerca.vert e restituisci l’ultimo valore corrispondente con una macro VBA
In alcuni casi, specialmente quando è necessario automatizzare la ricerca o gestire set di dati molto grandi, una macro VBA rappresenta una soluzione pratica ed efficace. Grazie a VBA, puoi personalizzare la logica di ricerca e gestire eccezioni o condizioni particolari che le formule standard potrebbero non affrontare con facilità.
Scenario applicabile: Dai priorità a questa soluzione se devi eseguire frequentemente la stessa operazione di ricerca su diverse cartelle di lavoro o desideri racchiudere la tua logica in uno script riutilizzabile.
1. Fare clic su Sviluppo > Visual Basic per aprire la finestra di Microsoft Visual Basic, Applications. Quindi fare clic su Inserisci > Modulo e incollare il codice seguente nel modulo:
Option Explicit
Sub FindLastMatch()
Dim searchRange As Range
Dim returnRange As Range
Dim searchValue As Variant
Dim i As Long
Dim foundValue As Variant
Dim found As Boolean
Const xTitleId As String = "KutoolsforExcel"
' Get ranges and value from user
On Error GoTo CleanFail
Set searchRange = Application.InputBox("Select the lookup column (single column):", xTitleId, Type:=8)
If TypeName(searchRange) = "Boolean" Then Exit Sub ' Cancel pressed
Set returnRange = Application.InputBox("Select the return column (single column):", xTitleId, Type:=8)
If TypeName(returnRange) = "Boolean" Then Exit Sub ' Cancel pressed
searchValue = Application.InputBox("Enter the lookup value:", xTitleId, Type:=2)
If VarType(searchValue) = vbBoolean And searchValue = False Then Exit Sub ' Cancel pressed
' Basic validations
If searchRange.Columns.Count <> 1 Or returnRange.Columns.Count <> 1 Then
MsgBox "Please select a single column for both lookup and return ranges.", vbExclamation
Exit Sub
End If
If searchRange.Rows.Count <> returnRange.Rows.Count Then
MsgBox "Lookup and return ranges must have the same number of rows.", vbExclamation
Exit Sub
End If
If Not searchRange.Parent Is returnRange.Parent Then
MsgBox "Lookup and return ranges must be on the same worksheet.", vbExclamation
Exit Sub
End If
' Scan from bottom to top
found = False
For i = searchRange.Rows.Count To 1 Step -1
If CStr(searchRange.Cells(i, 1).Value) = CStr(searchValue) Then
foundValue = returnRange.Cells(i, 1).Value
found = True
Exit For
End If
Next i
If found Then
MsgBox "The last matching value is: " & foundValue, vbInformation
Else
MsgBox "No match found.", vbInformation
End If
Exit Sub
CleanFail:
MsgBox "Operation cancelled or invalid selection.", vbExclamation
End Sub
2. Fai clic sul pulsante 
per eseguire il codice. Nelle finestre di dialogo visualizzate, seleziona la colonna di ricerca, la colonna di restituzione e inserisci il valore da cercare quando richiesto. La macro esegue la scansione partendo dall’ultima riga verso l’alto e visualizza l’ultimo valore corrispondente trovato.
Note:
- Assicurati che gli intervalli di ricerca e di restituzione siano composti da singole colonne, abbiano lo stesso numero di righe e si trovino nello stesso foglio di lavoro.
- La macro confronta i valori come testo per semplicità. Se devi distinguere tra formati numerici (ad esempio, 00123 e 123), adatta di conseguenza la logica di confronto.
- Se non viene trovata alcuna corrispondenza o se la selezione non è valida o viene annullata, viene visualizzata una notifica.
Punti di forza: Completamente automatizzato e riutilizzabile: non devi inserire o copiare formule tra le celle.
Svantaggi: Configurazione iniziale leggermente più complessa; richiede una cartella di lavoro abilitata alle macro (.xlsm) e un ambiente con macro attendibili.
Recuperare l’ultimo valore corrispondente in Excel è un’esigenza comune, che si tratti di monitorare le ultime transazioni, analizzare aggiornamenti o tenere traccia delle modifiche nel tempo. Grazie ai metodi illustrati sopra, puoi scegliere l’approccio più adatto in base alla tua versione di Excel, alle tue preferenze operative e al tuo livello di familiarità con gli strumenti disponibili. Le soluzioni basate su CERCA, INDICE & CONFRONTA, CERCA.X, Kutools e macro VBA offrono ciascuna punti di forza distinti e scenari d’uso in cui eccellono.
Risoluzione problemi: Se la formula restituisce un errore #N/D o #DIV/0!, controlla innanzitutto che gli intervalli siano selezionati correttamente, che il valore cercato esista effettivamente e che gli intervalli siano allineati in modo appropriato. Per garantire l’affidabilità delle formule, evita, ove possibile, celle vuote nella colonna di ricerca. In caso di dubbi, prova la formula su un piccolo campione dei tuoi dati per verificarne la configurazione: ti aiuterà a individuare rapidamente eventuali errori.
Per approfondire ulteriormente tecniche avanzate di ricerca—come il recupero di risultati multipli, la concatenazione di corrispondenze o la ricerca tra fogli di lavoro—ti consigliamo di consultare risorse specializzate come la nostra pagina di tutorial su Excel, ricca di articoli mirati e guide dettagliate passo dopo passo. Padroneggiare queste competenze ti permetterà di elaborare, analizzare e presentare i tuoi dati critici in Excel con maggiore sicurezza ed efficacia.
Altri articoli correlati:
- Cerca valori con Cercavert tra più fogli di lavoro
- In Excel, puoi applicare 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 utilizzare CERCA.VERT per cercare un valore attraverso più fogli di lavoro? Immagina di avere i tre fogli seguenti, ciascuno con i propri intervalli di dati, e di voler estrarre alcuni valori corrispondenti in base a criteri specifici provenienti da questi tre fogli.
- Utilizza Cercavert con corrispondenza esatta e approssimativa in Excel
- In Excel, CERCVERT è una delle funzioni più importanti per cercare un valore nella colonna più a sinistra di una tabella e restituire il corrispondente valore presente nella stessa riga di un intervallo specificato. Ma stai utilizzando correttamente la funzione CERCVERT in Excel? In questo articolo ti spiegherò come usarla al meglio.
- Cercavert e restituisci una cella vuota o un valore specifico invece di 0 o #N/D
- Di solito, quando si utilizza la funzione CERCA.VERT per recuperare un valore corrispondente, se la cella trovata è vuota, la funzione restituisce 0; se invece il valore cercato non viene trovato, viene visualizzato l’errore #N/D, come nell’immagine seguente. Come puoi fare affinché, al posto di 0 o #N/D, venga mostrata una cella vuota o un testo personalizzato a tua scelta?
- Cercavert e restituisci l’intera riga / Riga intera di un valore corrispondente in Excel
- Di solito puoi usare CERCVERT per cercare e restituire un valore corrispondente da un intervallo di dati, ma hai mai provato a trovare e restituire l’intera riga in base a criteri specifici, come nell’immagine seguente?
- Cercavert e concatena più valori corrispondenti in Excel
- Come ben sappiamo, la funzione CERCA.VERT in Excel permette di cercare un valore e restituire i dati corrispondenti da un’altra colonna, ma di solito riesce a recuperare solo la prima corrispondenza quando ce ne sono diverse. In questo articolo ti mostrerò come cercare e 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