Note: The other languages of the website are Google-translated. Back to English
English English

Bagaimana cara vlookup mengembalikan beberapa nilai dalam satu sel di Excel?

Biasanya, di Excel, saat Anda menggunakan fungsi VLOOKUP, jika ada beberapa nilai yang cocok dengan kriteria, Anda bisa mendapatkan yang pertama. Tapi, terkadang, Anda ingin mengembalikan semua nilai yang sesuai yang memenuhi kriteria ke dalam satu sel seperti gambar berikut yang ditampilkan, bagaimana Anda bisa menyelesaikannya?

Vlookup untuk mengembalikan beberapa nilai ke dalam satu sel dengan fungsi TEXTJOIN (Excel 2019 dan Office 365)

Vlookup mengembalikan beberapa nilai ke dalam satu sel dengan User Defined Function

Vlookup untuk mengembalikan beberapa nilai ke dalam satu sel dengan fitur yang berguna


Vlookup untuk mengembalikan beberapa nilai ke dalam satu sel dengan fungsi TEXTJOIN (Excel 2019 dan Office 365)

Jika Anda memiliki versi Excel yang lebih tinggi seperti Excel 2019 dan Office 365, ada fungsi baru - GABUNG TEKS, dengan fungsi canggih ini, Anda dapat dengan cepat melakukan vlookup dan mengembalikan semua nilai yang cocok ke dalam satu sel.

Vlookup untuk mengembalikan semua nilai yang cocok ke dalam satu sel

Harap terapkan rumus di bawah ini ke dalam sel kosong tempat Anda ingin meletakkan hasilnya, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil pertama, lalu seret gagang isian ke sel yang ingin Anda gunakan rumus ini, dan Anda akan mendapatkan semua nilai yang sesuai seperti gambar di bawah ini:

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

Catatan: Dalam rumus di atas, A2: A11 adalah rentang pencarian berisi data pencarian, E2 adalah nilai pencarian, C2: C11 adalah rentang data tempat Anda ingin mengembalikan nilai yang cocok, ","adalah pemisah untuk memisahkan beberapa record.

Vlookup untuk mengembalikan semua nilai yang cocok tanpa duplikat ke dalam satu sel

Jika Anda ingin mengembalikan semua nilai yang cocok berdasarkan data pencarian tanpa duplikat, rumus di bawah ini dapat membantu Anda.

Silakan salin dan tempel rumus berikut ke dalam sel kosong, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil pertama, dan kemudian salin rumus ini untuk mengisi sel lain, dan Anda akan mendapatkan semua nilai yang sesuai tanpa dulpicate seperti gambar di bawah ini:

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

Catatan: Dalam rumus di atas, A2: A11 adalah rentang pencarian berisi data pencarian, E2 adalah nilai pencarian, C2: C11 adalah rentang data tempat Anda ingin mengembalikan nilai yang cocok, ","adalah pemisah untuk memisahkan beberapa record.

Vlookup mengembalikan beberapa nilai ke dalam satu sel dengan User Defined Function

Fungsi TEXTJOIN di atas hanya tersedia untuk Excel 2019 dan Office 365, jika Anda memiliki versi Excel yang lebih rendah, Anda harus menggunakan beberapa kode untuk menyelesaikan tugas ini.

Vlookup untuk mengembalikan semua nilai yang cocok ke dalam satu sel

1. Tahan ALT + F11 kunci, dan itu membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, dan tempel kode berikut di Jendela Modul.

Kode VBA: Vlookup untuk mengembalikan beberapa nilai ke dalam satu sel

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. Kemudian simpan dan tutup kode ini, kembali ke lembar kerja, dan masukkan rumus ini: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") ke dalam sel kosong tertentu di mana Anda ingin menempatkan hasilnya, lalu seret gagang isian ke bawah untuk mendapatkan semua nilai yang sesuai dalam satu sel yang Anda inginkan, lihat tangkapan layar:

Catatan: Dalam rumus di atas, A2: A11 adalah rentang pencarian berisi data pencarian, E2 adalah nilai pencarian, C2: C11 adalah rentang data tempat Anda ingin mengembalikan nilai yang cocok, ","adalah pemisah untuk memisahkan beberapa record.

Vlookup untuk mengembalikan semua nilai yang cocok tanpa duplikat ke dalam satu sel

Untuk mengabaikan duplikat dalam nilai yang cocok kembali, lakukan dengan kode di bawah ini.

1. Tahan Alt + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, dan tempel kode berikut di Jendela Modul.

Kode VBA: Vlookup dan mengembalikan beberapa nilai unik yang cocok ke dalam satu sel

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. Setelah memasukkan kode, selanjutnya klik Tools > Referensi di tempat terbuka Microsoft Visual Basic untuk Aplikasi jendela, dan kemudian, di muncul keluar Referensi - VBAProject kotak dialog, periksa Runtime Microsoft Scripting pilihan dalam Referensi yang Tersedia kotak daftar, lihat tangkapan layar:

4. Lalu klik OK untuk menutup kotak dialog, simpan dan tutup jendela kode, kembali ke lembar kerja, dan masukkan rumus ini: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

Catatan: Dalam rumus di atas, A2: C11 adalah rentang data yang ingin Anda gunakan, E2 adalah nilai pencarian, angkanya 3 adalah nomor kolom yang berisi nilai yang dikembalikan.

Vlookup untuk mengembalikan beberapa nilai ke dalam satu sel dengan fitur yang berguna

 Jika Anda memiliki kami Kutools untuk Excel, Dengan yang Lanjutan Gabungkan Baris fitur, Anda dapat dengan cepat menggabungkan atau menggabungkan baris berdasarkan nilai yang sama dan melakukan beberapa perhitungan yang Anda butuhkan.

Catatan:Untuk menerapkan ini Lanjutan Gabungkan Baris, pertama, Anda harus mengunduh Kutools untuk Excel, lalu terapkan fitur dengan cepat dan mudah.

Setelah menginstal Kutools untuk Excel, lakukan hal berikut:

1. Pilih rentang data yang ingin Anda gabungkan satu data kolom berdasarkan kolom lain.

2. Klik Kutools > Gabungkan & Pisahkan > Lanjutan Gabungkan Baris, lihat tangkapan layar:

3. Di muncul keluar Lanjutan Gabungkan Baris kotak dialog:

  • Klik nama kolom kunci yang akan digabungkan berdasarkan, lalu klik Kunci utama
  • Kemudian klik kolom lain yang ingin Anda gabungkan datanya berdasarkan kolom kunci, dan klik Menggabungkan untuk memilih satu pemisah untuk memisahkan data gabungan.

4. Kemudian klik OK tombol, dan Anda akan mendapatkan hasil sebagai berikut:

Unduh dan uji coba gratis Kutools untuk Excel Sekarang!


Artikel yang lebih relatif:

  • Fungsi VLOOKUP Dengan Beberapa Contoh Dasar Dan Tingkat Lanjut
  • Di Excel, fungsi VLOOKUP adalah fungsi yang andal untuk sebagian besar pengguna Excel, yang digunakan untuk mencari nilai di paling kiri rentang data, dan mengembalikan nilai yang cocok di baris yang sama dari kolom yang Anda tentukan. Tutorial ini membahas tentang cara menggunakan fungsi VLOOKUP dengan beberapa contoh dasar dan lanjutan di Excel.
  • Kembalikan beberapa nilai yang cocok berdasarkan satu atau beberapa kriteria
  • Biasanya, mencari nilai tertentu dan mengembalikan item yang cocok itu mudah bagi kebanyakan dari kita dengan menggunakan fungsi VLOOKUP. Namun, pernahkah Anda mencoba mengembalikan beberapa nilai yang cocok berdasarkan satu atau lebih kriteria? Pada artikel ini, saya akan memperkenalkan beberapa rumus untuk menyelesaikan tugas kompleks ini di Excel.
  • Vlookup Dan Mengembalikan Banyak Nilai Secara Vertikal
  • Biasanya, Anda dapat menggunakan fungsi Vlookup untuk mendapatkan nilai pertama yang sesuai, tetapi terkadang Anda ingin mengembalikan semua rekaman yang cocok berdasarkan kriteria tertentu. Artikel ini, saya akan berbicara tentang cara vlookup dan mengembalikan semua nilai yang cocok secara vertikal, horizontal atau ke dalam satu sel.
  • Vlookup Dan Kembalikan Beberapa Nilai Dari Daftar Drop Down
  • Di Excel, bagaimana Anda bisa vlookup dan mengembalikan beberapa nilai yang sesuai dari daftar turun bawah, yang berarti ketika Anda memilih satu item dari daftar turun bawah, semua nilai relatifnya ditampilkan sekaligus. Artikel ini, saya akan memperkenalkan solusi selangkah demi selangkah.

Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

  • Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
  • Lebih dari 300 fitur canggih. Mendukung Office / Excel 2007-2019 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap uji coba gratis 30 hari. Jaminan uang kembali 60 hari.
tab kte 201905

Tab Office Membawa antarmuka Tab ke Office, dan Membuat 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.
  • Tingkatkan produktivitas Anda hingga 50%, dan kurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Urutkan komentar berdasarkan
komentar (43)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Bagaimana saya menyesuaikan rumus ini untuk memisahkan setiap nilai yang dikembalikan tetapi "," serta hanya mengembalikan nilai unik?
Komentar ini diminimalkan oleh moderator di situs
Terima kasih untuk kodenya!!

Sedangkan untuk wildcard, cara lain adalah dengan menggunakan INSTR

Anda dapat mengganti [ If rng = pValue Then ] dengan [ InStr(1, rng.Value, pValue) Then ] dan jika Anda tidak ingin case sensitif maka gunakan [ InStr(1, rng.Value, pValue, vbTextCompare) Lalu ]
Komentar ini diminimalkan oleh moderator di situs
Terima kasih untuk kode VBA di atas. Bisakah Anda memberi tahu saya cara membuat hasilnya masuk ke baris baru di sel, yaitu seperti Alt-Enter 300 400 1000 1300
Komentar ini diminimalkan oleh moderator di situs
Terima kasih telah membagikan kode di atas. Saya telah menggunakan ini selama beberapa bulan sekarang tetapi hari ini sepertinya tidak berhasil. Saya mendapatkan sel kosong alih-alih kesalahan biasa ketika ada data yang akan dikembalikan. Ada pikiran?
Komentar ini diminimalkan oleh moderator di situs
Pekerjaan yang luar biasa.. Mendapat apa yang saya inginkan !!! Suka !!
Komentar ini diminimalkan oleh moderator di situs
Hai, saya sangat terkesan dengan pekerjaannya dan sangat mudah untuk membuatnya menggunakan fungsi ini. namun saya membutuhkan dukungan lebih lanjut. Ku ? adalah bagaimana saya bisa memilih nomor dari sel dengan banyak sel di array vlookup saya. yaitu Jika sel A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Apple D2 = banana Jadi bagaimana cara memilih 100 dari kolom array tabel saya C untuk mendapatkan koresponden D1 = apple Harap dicatat bahwa saya memiliki 7 digit angka dalam nilai pencarian saya dan array tabel yang dipisahkan oleh ";". Saya akan sangat menghargai jika Anda dapat menyelesaikan ini dan membantu saya menghemat banyak waktu.
Komentar ini diminimalkan oleh moderator di situs
Terima kasih untuk kode VBA. Saya mendapatkan apa yang saya inginkan! Saya hanya memodifikasi kode " rng.Offset(0, pIndex - 1) " menjadi " rng.Offset(0, pIndex - 2) " . Begitu juga MYVLOOKUP dapat mencari dari Kanan ke Kiri.
Komentar ini diminimalkan oleh moderator di situs
Inilah yang saya cari dan tidak terpikir untuk membuat UDF saya sendiri. Namun itu tidak akan berfungsi persis seperti VLOOKUP. Jika string yang Anda cari tidak hanya di kolom pertama maka itu bisa memberi Anda data di luar rentang asli yang dilewati. Nama Nomor Nama lain Kolom tidak dalam range lulus Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Jika tabel di atas adalah sel A1:D7 jika hanya lulus A1:C7 Fungsi "MYVLOOKUP" mengembalikan 1 1 2 2 5 5 ketika Anda mengharapkannya untuk mengembalikan 1 2 5. Perubahan di bawah ini memperbaiki masalah: Fungsi MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6 /9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Redupkan Baris Selamanya, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1). Nilai = pValue Kemudian xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i 'Untuk Setiap rng Dalam pWorkRng ' If rng = pValue Then ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' End If 'Next MYVLOOKUP = xResult End Function
Komentar ini diminimalkan oleh moderator di situs
Ini berfungsi dengan baik, tetapi saya perlu bantuan dengan perintah untuk menghapus duplikat dari hasil. Serius, kerja bagus.
Komentar ini diminimalkan oleh moderator di situs
Ini berfungsi dengan baik, tetapi saya masih memerlukan bantuan dengan fungsi perintah untuk menghapus duplikat dari hasil.
Komentar ini diminimalkan oleh moderator di situs
Beri tahu saya tentang komentar lanjutan
Komentar ini diminimalkan oleh moderator di situs
Kembalikan apa-apa! setelah menerapkan MYLOOKUP tidak memberikan hasil tetapi kosong.
Komentar ini diminimalkan oleh moderator di situs
Hai, Ini bekerja dengan baik. Yang ingin saya lakukan adalah mengadaptasi kode untuk memisahkan hasil nilai Anda dengan "///" atau penanda lainnya (untuk alasan teknis, saya tidak ingin hanya pemisah karakter tunggal). Juga, saya perhatikan bahwa formula ini tidak bekerja dengan wild card. Saya tahu saya meminta terlalu banyak, tetapi tidak karena vlookup dapat bekerja ketika saya mencari =myvlookup("*"&E6&"*",$A$2:$C$15,2) yang akan dilakukan/dapat dilakukan. Ada bantuan?
Komentar ini diminimalkan oleh moderator di situs
Perhatian. Saya menemukan cara untuk mendapatkan pemisah apa pun dalam output itu. Dasarnya. Tapi saya menemukan jawabannya. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Yang terakhir dan paling diinginkan adalah memungkinkannya bekerja dengan wildcard dalam kriteria pencarian. Sekali lagi terima kasih atas solusi yang indah dan brilian ini. Sangat membantu. Sekarang hanya ingin menjalankan makro dan diinstal di excel saya secara permanen, apa pun yang saya lakukan sehingga saya dapat menggunakannya ketika saya perlu. Dan wildcard! Terima kasih banyak. Wildcard adalah semua yang tersisa untuk dilakukan.
Komentar ini diminimalkan oleh moderator di situs
Untuk mendapatkan catatan unik, Anda dapat menggunakan di bawah ini: (dimodifikasi dengan merujuk kode pengguna lain) Fungsi MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6/9/16 Jay Coltrain 'Dim rng As Range Redupkan xResult As String xResult = "" Redupkan Baris Selamanya, i As Panjang Baris = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1).Value = pValue Kemudian xResult = xResult & "," & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i Dim varSection As Variant Dim sTemp As String Dim sDelimiter As String sDelimiter = "," Untuk Setiap varSection Di Split(xResult, sDelimiter) If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Kemudian sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) + 1) End Function
Komentar ini diminimalkan oleh moderator di situs
Ini bekerja dengan sempurna, tetapi saya perlu waktu untuk membuat Fungsi berfungsi dengan baik di dalam 20 tab saya, 50k+ baris spreadsheet. Sekarang pertanyaan BESARnya adalah bagaimana mengambil string yang dibatasi itu dan kemudian menggunakan setiap entri sebagai Indeks/Pencocokan (tidak menikah dengan Indeks/Pencocokan, tetapi tampaknya lebih cepat) nilai pencarian di kumpulan data lain, mengembalikan nilai SUM dari semua pengembalian ke dalam satu sel . Skenario saya adalah saya memiliki Pesanan Tunggal yang memiliki banyak faktur. Fungsi MYVLOOKUP Anda bekerja dengan sangat baik untuk melaporkan kembali semua faktur dalam satu sel. Yang ingin saya lakukan sekarang adalah mengambil setiap pengembalian yang digabungkan dengan sel yang dilaporkan, memutar melalui array itu dan menjumlahkan jumlah pembayaran setiap faktur kembali ke sel rumus. Saya menghargai bantuan apa pun yang dapat Anda tawarkan dalam hal ini dan terima kasih untuk Fungsi MYVLOOKUP!
Komentar ini diminimalkan oleh moderator di situs
Apa pun yang saya lakukan, saya selalu mendapatkan #nilai! kembali bukannya hasil. vlookup berfungsi dengan baik, jadi datanya berfungsi. Sudah mengikuti proses mengaktifkan macro. Saya bahkan menggabungkan semuanya menjadi satu lembar. Ada ide??
Komentar ini diminimalkan oleh moderator di situs
Makro yang bagus, bermanfaat. Tetapi perlu tahu apakah itu dapat dimodifikasi untuk memeriksa 2 kriteria & apakah ada yang menemukan untuk membuat wildcard berfungsi. Ada bantuan?
Komentar ini diminimalkan oleh moderator di situs
Apakah ada cara untuk mengubah hasil jadi alih-alih menampilkan 1000 1000 -1000 itu akan menunjukkan misalnya 1,000/1,000/(1,000) ?
Komentar ini diminimalkan oleh moderator di situs
Fungsi yang hebat, namun memotong 100,000 catatan terbukti sedikit banyak untuk laptop saya yang buruk, harus membiarkannya berjalan dalam semalam!
Komentar ini diminimalkan oleh moderator di situs
Ini luar biasa, terima kasih!
Belum ada komentar yang diposting di sini
Muat Lebih
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan