Loncat ke daftar isi utama

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

Pengarang: Xiaoyang Terakhir Dimodifikasi: 2025-04-09

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 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 dengan Fungsi TEXTJOIN dan FILTER

Penjelasan dari rumus ini:
  1. 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.
  2. 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!

Kutools for Excel menawarkan lebih dari 300 fitur canggih untuk menyederhanakan tugas-tugas kompleks, meningkatkan kreativitas dan efisiensi. Itegarate dengan kemampuan AI, Kutools mengotomatiskan tugas dengan presisi, membuat pengelolaan data menjadi mudah. Informasi detail dari Kutools for Excel...         Uji coba gratis...

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.
    tentukan opsi di kotak dialog

Hasil: Sekarang, semua nilai yang cocok digabungkan menjadi satu sel. Lihat tangkapan layar:
dirangkai berdasarkan kriteria oleh kutools


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:

dirangkai berdasarkan kriteria oleh vba

Penjelasan dari rumus ini:
  • 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

🤖 Kutools Ajudan AI: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  memohon Kutools Fungsi...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi
penggunaan Kutools dalam bahasa pilihan Anda – mendukung bahasa Inggris, Spanyol, Jerman, Prancis, Mandarin, dan 40+ lainnya!

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!