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
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, ""))
- FILTER($B$2:$B$16, $A$2:$A$16=D2, ""): Mengembalikan array berisi semua nilai yang memenuhi kondisi.
- $B$2:$B$16: Rentang data yang akan difilter.
- $A$2:$A$16=D2: Kondisi penyaringan, di mana nilai dalam $A$2:$A$16 harus sama dengan nilai dalam sel D2.
- TEXTJOIN(", ", TRUE, ...): Menggabungkan semua nilai yang cocok menjadi satu string, dipisahkan dengan koma dan spasi.
- ", ": Pembatas yang digunakan untuk memisahkan setiap nilai (dalam hal ini, koma dan spasi).
- BENAR: Mengabaikan nilai kosong untuk memastikan tidak ada pembatas tambahan yang disertakan.
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:
- Pilih "Rentang keluaran" dan "Nilai pencarian" di kotak teks secara terpisah;
- Pilih rentang tabel yang ingin Anda gunakan;
- Tentukan kolom kunci dan kolom pengembalian dari menu tarik-turun "Kolom Kunci" dan "Kolom Pengembalian" secara terpisah;
- 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:
- D2: Ini adalah nilai yang Anda cari dalam rentang yang ditentukan (LookupValue).
- A2: A16: Rentang tempat fungsi mencari nilai pencarian (LookupRange).
- B2: B16: Rentang yang berisi nilai yang akan digabungkan saat nilai pencarian cocok (ReturnRange).
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.
Alat Produktivitas Kantor Terbaik
Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Anda Rasakan Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Canggih untuk Meningkatkan Produktivitas dan Menghemat Waktu. Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...
Office Tab Hadirkan antarmuka Tab ke Office, dan Jadikan Pekerjaan Anda Jauh Lebih Mudah
- Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
- Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
- Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!