Bagaimana cara vlookup dan menggabungkan beberapa nilai yang sesuai di Excel?

Bila Anda menggunakan VLOOKUP di Excel, biasanya ia akan mengembalikan nilai pertama yang cocok dari kumpulan data. Namun, ada beberapa skenario di mana Anda mungkin perlu mengembalikan dan menggabungkan beberapa nilai yang sesuai (misalnya, semua nama yang dikaitkan dengan kelas tertentu). Karena VLOOKUP sendiri tidak dapat mencapai hal ini, kami akan mengeksplorasi teknik lanjutan untuk mencapai hasil yang diinginkan.

Vlookup dan menggabungkan beberapa nilai yang sesuai di Excel

• Menggunakan Fungsi TEXTJOIN dan FILTER (Excel 365/2021)
• Menggunakan Kutools for Excel
• Menggunakan Fungsi yang Ditentukan Pengguna

Vlookup dan menggabungkan beberapa nilai yang sesuai dengan Fungsi TEXTJOIN dan FILTER

Jika Anda menggunakan Excel 365 atau Excel 2021, kombinasi fungsi TEXTJOIN dan FILTER menyediakan cara yang ampuh dan efisien untuk menggabungkan beberapa nilai yang sesuai.

Di sel target, masukkan rumus berikut, lalu seret rumus ke sel lain untuk mengisi sel. Semua nilai yang sesuai akan diekstrak dan digabungkan ke dalam satu sel. Lihat tangkapan layar:

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

Vlookup dan menggabungkan beberapa nilai yang sesuai dengan Kutools for Excel

Bosan dengan keterbatasan VLOOKUP Excel saat menangani beberapa nilai yang sesuai? Namun dengan Kutools for ExcelDengan fitur Pencarian Satu-ke-Banyak, Anda dapat dengan mudah mencari dan menggabungkan beberapa nilai yang sesuai hanya dalam beberapa klik!

Setelah menginstal Kutools for Excel, lakukan seperti ini:

Klik "Kutools" > "Pencarian Super" > "Pencarian Satu-ke-Banyak (mengembalikan beberapa hasil)" untuk membuka kotak dialog. Di kotak dialog, harap tentukan operasi seperti di bawah ini:

1. Pilih "Rentang keluaran" dan "Nilai pencarian" di kotak teks secara terpisah;
2. Pilih rentang tabel yang ingin Anda gunakan;
3. Tentukan kolom kunci dan kolom pengembalian dari menu tarik-turun "Kolom Kunci" dan "Kolom Pengembalian" secara terpisah;
4. Terakhir, klik tombol OK.
   

Hasil: Sekarang, semua nilai yang cocok digabungkan menjadi satu sel. Lihat tangkapan layar:

Vlookup dan menggabungkan beberapa nilai yang sesuai dengan Fungsi yang Ditentukan Pengguna

Jika Anda terbiasa dengan VBA (Visual Basic for Applications), Anda dapat membuat Fungsi yang Ditentukan Pengguna (UDF) untuk menggabungkan beberapa nilai yang sesuai. Metode ini berfungsi di semua versi Excel.

1. Tekan terus tombol "ALT + F11" untuk membuka jendela "Microsoft Visual Basic for Applications".

2. Klik "Sisipkan" > "Modul", lalu tempel kode berikut di Jendela Modul.

Kode VBA: Vlookup dan menggabungkan beberapa nilai yang cocok dalam satu sel

Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
    Dim Cell As Range
    Dim Result As String
    Result = ""
    For Each Cell In LookupRange
        If Cell.Value = LookupValue Then
            Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
        End If
    Next Cell
    If Result <> "" Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    ConcatenateMatches = Result
End Function

3. Kemudian simpan dan tutup kode ini, kembali ke lembar kerja, dan masukkan rumus ini: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) ke dalam sel kosong tempat Anda ingin meletakkan hasil, lalu, seret gagang isian ke bawah untuk mengisi rumus ke sel lainnya. Semua nilai terkait berdasarkan data tertentu telah dikembalikan ke dalam satu sel dengan pemisah koma dan spasi, lihat tangkapan layar:

Menggabungkan beberapa nilai yang sesuai di Excel dapat dilakukan melalui berbagai metode, masing-masing dengan kelebihannya sendiri. Apakah Anda lebih suka menggunakan fungsi bawaan seperti TEXTJOIN dan FILTER, alat pihak ketiga seperti Kutools, atau solusi VBA kustom, kuncinya adalah memilih metode yang paling sesuai dengan versi Excel, tingkat keterampilan, dan persyaratan khusus Anda. Jika Anda tertarik untuk mempelajari lebih banyak tips dan trik Excel, situs web kami menawarkan ribuan tutorial.