Dropdown List Dengan Pencarian Sugesti Di Excel

Layaknya Google Search yang memiliki fitur saran pencarian ketika kita sedang menuliskan sesuatu dalam kotak pencarian. Dalam tutorial ini, kita akan mempelajari cara membuat Dropdown List di Excel dengan fitur saran pencarian seperti Google Search yang menampilkan kata yang cocok saat mengetik.

{getToc} $title={Daftar Isi}

Membuat Dropdown List

Admin menggunakan daftar siswa sebagai contoh pada tutorial ini, lalu kita buat kotak pencarian menggunakan Dropdown list dengan tujuan menampilkan saran kata yang cocok untuk daftar siswa. Seperti contoh dibawah ini.

Mengonfigurasi Kotak Pencarian

Sebelum kita mengonfigurasi combo box, kita buat dahulu named range sementara agar tidak ada kendala saat kofigurasi combo box. Silakan pilih cell A1 dan tulis nama Pencarian pada Name Box lalu klik Enter.

Langkah selanjutnya, kita akan menggunakan combo box dan mengonfigurasinya sehingga saat kita mengetiknya, teks juga tercermin dalam cell secara real time. Berikut adalah langkah-langkahnya.
Buka Tab Developer > Insert > Combo Box (bagian ActiveX Controls).

Kemudian pindahkan kursor ke area sheet dan klik di mana saja. Ini akan memasukan combo box. Lalu klik kanan pada Combo Box dan pilih Properties.

Di kotak dialog properti, buat perubahan berikut:
AutoWordSelect: False
LinkedCell: B3
ListFillRange: Pencarian (named range sementara yang tadi telah kita buat)
MatchEntry: 2 – fmMatchEntryNone

Buka tab Developer dan klik Design Mode untuk menonaktifkan mode desain agar kita bisa memasukkan teks dalam Combo Box. Selain itu, karena cell B3 ditautkan ke combo box, teks apa pun yang kita masukkan di combo box juga akan masuk di cell B3 secara real time.

Setting Data

Sekarang kotak pencarian sudah siap, kita perlu mendapatkan datanya. Intinya adalah segera setelah kita mengetik sesuatu di kotak telusur, itu hanya akan menampilkan item yang memiliki teks serupa di dalamnya. Untuk melakukan ini, kita akan menggunakan tiga kolom helper dan satu dynamic named range.

Kolom Helper 1

Tuliskan rumus berikut di Cell F3 dan seret untuk seluruh kolom (F3:F22)

=--ISNUMBER(IFERROR(SEARCH($B$3;E3;1);""))

Rumus ini menghasilkan 1 ketika teks di Combo Box ada di nama siswa sebelah kiri. Misalnya, jika kita mengetik Ahm, maka nilai untuk Ahmad Danie dan Ahmad Saputra adalah 1 dan semua nilai yang tersisa adalah 0.

Kolom Helper 2

Tuliskan rumus berikut di Sel G3 dan seret untuk seluruh kolom (G3:G22)

=IF(F3=1;COUNTIF($F$3:F3;1);"")

Rumus ini menghasilkan 1 untuk kemunculan pertama di mana teks Combo Box cocok dengan nama siswa, 2 untuk kemunculan kedua, 3 untuk kemunculan ketiga dan seterusnya. Misalnya, jika kita mengetik Ahm, cell G3 akan menampilkan nilai 1 jika cocok dengan Ahmad Danie, dan G9 akan menampilkan 2 jika cocok dengan Ahmad Saputra. Cell lainnya akan kosong.

Kolom Helper 3

Tuliskan rumus berikut di cell H3 dan seret untuk seluruh kolom (H3:H22)

=IFERROR(INDEX($E$3:$E$22;MATCH(ROWS($G$3:G3);$G$3:$G$22;0));"")

Rumus ini menumpuk semua nama yang cocok bersama-sama tanpa ada cell kosong di antaranya. Misalnya, jika kita mengetik Ahm, kolom ini akan menampilkan nilai 1 dan 2 berdempetan, dan cell lainnya akan kosong.

Membuat Dynamic Named Range

Ketika kolom helper sudah jadi, sekarang kita perlu membuat dynamic named range. Named range ini hanya akan mengacu pada nilai yang cocok dengan teks yang dimasukkan di combo box. Kita akan menggunakan dynamic named range ini untuk menunjukkan nilai di kotak drop-down.

Pada langkah konfigurasi tadi kita sudah membuat named range Pencarian dan sudah di masukan di opsi ListFillRange pada properties combo box. Sekarang kita akan mengedit named range tersebut. Berikut langkah-langkahnya:
Pergi ke Formulas > Name Manager. Di name manager dialogue box, pilih name Pencarian. Pada Refers to Field, masukkan rumus berikut:

=$H$3:INDEX($H$3:$H$22;MAX($G$3:$G$22);1)

Atau bisa menggunakan fungsi di bawah ini pada Refers to Field:

=OFFSET($H$3;0;0;MAX($G$3:$G$22);1)

Tambahkan Kode VBA

Bagian terakhir adalah menulis sedikit kode VBA. Kode ini membuat drop down dinamis sehingga menampilkan item/nama yang cocok saat kita mengetik di kotak pencarian. Tambahkan kode ini ke sheet:
Klik kanan pada tab Sheet dan pilih View Code.

Di jendela VBA, Salin dan Tempel kode berikut:

Private Sub ComboBox1_Change()
    ComboBox1.ListFillRange = "Pencarian"
    Me.ComboBox1.DropDown
End Sub
 
Private Sub ComboBox1_Click()
    'pindahkan value hasil pencarian ke cell lain
    Range("B6").Value = Range("B3").Value
    'hapus value combo box agar tidak error jika mengetik di cell setelah pilih combo box
    Me.ComboBox1.Value = ""
End Sub
 
Private Sub ComboBox1_GotFocus()
    Me.ComboBox1.Value = ""
End Sub

Selesai..! Kita sudah siap dengan kotak Pencarian seperti Google yang akan menunjukkan item/nama yang cocok saat kita mengetik di dalamnya. Untuk tampilan dan nuansa yang lebih baik, kita dapat menutupi cell B3 dengan Combo Box dan menyembunyikan semua kolom pembantu.

Perhatian..! Jangan memilih value combo box dengan tombol panah, bisa menyebabkan Excel Not Responding{alertWarning}

Cukup sampai disini tutorial Dropdown List Dengan Pencarian Sugesti Di Excel, silakan tonton videonya Membuat Kotak Pencarian Di Excel Dengan Sugesti Kata Pencarian. Semoga bermanfaat