Come eseguire una ricerca VLOOKUP per restituire più valori in una singola cella in Excel?

VLOOKUP è una funzione potente in Excel, ma di default restituisce solo il primo valore corrispondente. Cosa fare se è necessario recuperare tutti i valori corrispondenti e combinarli in una sola cella? Questo è un requisito comune quando si analizzano set di dati o si riassumono informazioni. In questa guida, vi illustreremo passo dopo passo come restituire più valori in una singola cella utilizzando sia formule che funzionalità utili.

Restituire più valori in una cella con la funzione TEXTJOIN (Excel 2019 e Office 365)

• Restituisci tutti i valori corrispondenti in una cella
• Restituisci tutti i valori corrispondenti senza duplicati in una cella

Restituisci più valori in una cella con Kutools

Restituisci più valori in una cella con una Funzione Definita dall'Utente

• Restituisci tutti i valori corrispondenti in una cella
• Restituisci tutti i valori corrispondenti senza duplicati in una cella

Restituisci più valori in una cella con la funzione TEXTJOIN (Excel 2019 e Office 365)

Se disponete di una versione superiore di Excel, come Excel 2019 e Office 365, c'è una nuova funzione - TEXTJOIN, con questa potente funzione, potete rapidamente eseguire una ricerca VLOOKUP e restituire tutti i valori corrispondenti in una singola cella.

  Restituisci tutti i valori corrispondenti in una cella

Applicate la seguente formula in una cella vuota dove desiderate inserire il risultato, quindi premete contemporaneamente i tasti Ctrl + Maiusc + Invio per ottenere il primo risultato, poi trascinate la maniglia di riempimento verso il basso fino alla cella in cui volete usare questa formula, e otterrete tutti i valori corrispondenti come mostrato nello screenshot qui sotto:

  Restituisci tutti i valori corrispondenti senza duplicati in una cella

Se desiderate restituire tutti i valori corrispondenti in base ai dati di ricerca senza duplicati, la seguente formula potrebbe esservi utile.

Copiate e incollate la seguente formula in una cella vuota, quindi premete contemporaneamente i tasti Ctrl + Maiusc + Invio per ottenere il primo risultato, e poi copiate questa formula per riempire altre celle, e otterrete tutti i valori corrispondenti senza duplicati come mostrato nello screenshot qui sotto:

Restituisci più valori in una cella con Kutools

Con la funzionalità "Unione avanzata righe" di Kutools per Excel, potete facilmente recuperare più valori corrispondenti in una singola cella - non sono richieste formule complesse! Dite addio alle soluzioni manuali e sbloccate un modo più efficiente per gestire le vostre attività di ricerca in Excel. Esploriamo come Kutools per Excel rende tutto possibile!

Dopo aver installato Kutools per Excel, procedete come segue:

1. Selezionate l'intervallo di dati che desiderate combinare in base a un'altra colonna.

2. Cliccate su "Kutools" > "Unisci e Dividi" > "Unione avanzata righe", vedere screenshot:

3. Nella finestra di dialogo "Unione avanzata righe" che appare:

• Cliccate sul nome della colonna chiave da combinare in base a, e poi cliccate su "Chiave principale".
• Poi cliccate su un'altra colonna che desiderate combinare in base alla colonna chiave, e cliccate sulla casella di riepilogo a discesa dal campo "Calcolo", scegliete un separatore per separare i dati combinati dalla sezione "Unisci".
• Quindi, cliccate sul pulsante OK.

Tutti i valori corrispondenti da un'altra colonna, in base allo stesso valore, sono combinati in una singola cella. Vedere gli screenshot:

Suggerimenti: Se desiderate rimuovere contenuti duplicati durante l'unione delle celle, basta selezionare l'opzione "Elimina valori duplicati" nella finestra di dialogo. Questo assicura che solo voci uniche siano combinate in una singola cella, rendendo i vostri dati più puliti e organizzati senza alcuno sforzo extra. Vedere gli screenshot:

Scarica e prova gratuitamente Kutools per Excel Ora!

Restituisci più valori in una cella con una Funzione Definita dall'Utente

La funzione TEXTJOIN sopra menzionata è disponibile solo per Excel 2019 e Office 365, se avete altre versioni inferiori di Excel, dovreste usare alcuni codici per completare questo compito.

  Restituisci tutti i valori corrispondenti in una cella

1. Tenete premuti i tasti "ALT + F11", e si aprirà la finestra "Microsoft Visual Basic for Applications".

2. Cliccate su "Inserisci" > "Modulo", e incollate il seguente codice nella finestra del Modulo.

Codice VBA: Vlookup per restituire più valori in una cella

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. Poi salvate e chiudete questo codice, tornate al foglio di lavoro, e inserite questa formula: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") in una cella vuota specifica dove desiderate inserire il risultato, quindi trascinate la maniglia di riempimento verso il basso per ottenere tutti i valori corrispondenti in una cella che desiderate, vedere screenshot:

  Restituisci tutti i valori corrispondenti senza duplicati in una cella

Per ignorare i duplicati nei valori corrispondenti restituiti, procedete con il seguente codice.

1. Tenete premuti i tasti "Alt + F11" per aprire la finestra "Microsoft Visual Basic for Applications".

2. Cliccate su "Inserisci" > "Modulo", e incollate il seguente codice nella finestra del Modulo.

Codice VBA: Vlookup e restituisci più valori univoci corrispondenti in una cella

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. Dopo aver inserito il codice, cliccate su "Strumenti" > "Riferimenti" nella finestra "Microsoft Visual Basic for Applications" aperta, e poi, nella finestra di dialogo "Riferimenti – VBAProject" che appare, selezionate l'opzione "Microsoft Scripting Runtime" nella casella di riepilogo "Riferimenti disponibili", vedere screenshot:

4. Poi cliccate su OK per chiudere la finestra di dialogo, salvate e chiudete la finestra del codice, tornate al foglio di lavoro, e inserite questa formula: =MultipleLookupNoRept(E2,$A$2:$C$11,3) in una cella vuota dove desiderate visualizzare il risultato, e poi trascinate la maniglia di riempimento verso il basso per ottenere tutti i valori corrispondenti, vedere screenshot:

Che optiate per formule come TEXTJOIN combinate con funzioni matriciali, utilizziate strumenti come Kutools per Excel o una Funzione Definita dall'Utente, tutti questi approcci aiutano a semplificare compiti di ricerca complessi. Scegliete il metodo che meglio soddisfa le vostre esigenze. Se siete interessati ad esplorare altri suggerimenti e trucchi di Excel, il nostro sito web offre migliaia di tutorial.

Altri articoli correlati: