Cari kata di excel sebenarnya sangat mudah cukup dengan menekan kombinasi tombol ctrl+F isi kata yang ingin dicari pada form yang disediakan dan tekan Find All, semua sell yang mengandung kata tersebut akan ditampilkan. Selesai.
Contoh diatas adalah cara gampangnya dan yang akan dibahas pada tulisan ini adalah pencarian yang memanfaatkan fungsi search
, rank
dan vlookup
. Sebelum lebih jauh lagi baiknya ketiga fungsi ini harus dimengerti cara penggunaannya,
Fungsi search()
Apa yang dilakukan oleh fungsi search? Fungsi search pada excel bukan menapilkan karakter dari sel yang dicari ke sel yang dituju layaknya fungsi copy, melainkan fungsi search akan mengembalikan nomor posisi karakter yang di cari,
sintaks
=SEARCH(find_text,within_text,[start_num])
dimana
find_text
adalah teks yang ingin anda temukan posisinya
within_text
adalah text yang akan di cari posisinya oleh find_text
start_num
adalah argumen opsional yang berfungsi menentukan posisi awal pencarian.
contoh
=SEARCH(“ada”,“kepada”)
pada contoh tersebut akan mengembalikan nilai 4
, karena kata “ada” berada posisi ke 4 kata “kepada”.
Lanjut ke fungsi Rank
Fungsi Rank()
Rank! Yah, ranking jadi tidak usah dijelaskan lebih panjang lagi, lanjut ke sintaxnya
=RANK(number,ref,[order])
dimana
number
adalah nomor atau angka yang akan dirankingkan
ref
adalah referensi berupa larik bilangan atau daftar angka termasuk null(kosong), bila larik tersebut adalah bukan nomor sel tersebut akan mengembalikan nilai kesalahan
order
adalah argumen yang digunakan untuk mengurutkan hasil ranking
contoh
terdapat baris data
A1 berisi nilai 7
A2 berisi nilai 6
A3 berisi nilai 8
Baris data tersebut di-RANK-kan, seperti pada contoh berikut
=RANK(6,A1:A3,1)
dan hasil yang diperoleh adalah 1
, karena angka 6 adalah nilai terkecil dari larik bilangan 7,6,8
bila rumus yang dipakai adalah
=RANK(6,A1:A3,0)
maka hasil yang diperoleh adalah 3
, bila diurutkansecara descending maka angka 6 berada pada urutan ke 3
Bila argumen nilai order 1
maka urutan rank nya dimulai dari terkecil hingga yang paling besar dan bila nilai argumen order adalah 0
maka urutannya akan sebaliknya.
fungsi vlookup
lookup digunakan untuk menemukan dan menampilkan data dari sel berdasarkan rentan larik yang dipilih
sintaks
=VLOOKUP(lookup_value,table_array, col_index_num,[range_lookup])
dimana
lookup_value
berisi nilai yang akan dicari dalam rentang larik, pencarian dilakukan berdasrkan kolom paling kiri.
table_array
adalah data larik dimana kolom pertama adalah kolom yang digunakan untuk nilai lookup_value nya
col_index_num
adalah nomor kolom dalam larik dimulai dari angka 1 pada kolom paling kiri, data pada col_index_num akan ditampilkan pada sel yang disematkan rumus vlookup
range_lookup
adalah opsional yang berisi nilai TRUE jika mengharapkan atau sel akan mengembalikan nilai yang diperkirakan cocok atau FALSE jika menginginkan nilai yang sama persis.
Fungsi pentingnya sudah selesai dipaparkan.
kombinasikan Fungsi Search, Rank dan vlookup untuk membuat search box
Skenarionya adalah jika sel cari di isi maka data yang relevan dengan data yang ada pada baris data akan ditampilkan pada sel dibawahnya. Bagaimana caranya? Ikuti langkah berikut.
gunakan rumus search
Buat tabel pada sheet sebagai contoh buatlah data bulan seperti pada gambar 1

Arahkan kursor ke sel D3 dan gunakan rumus search

=SEARCH($G$2,C3)
guanakan rumus search pada semua kolom seperti pada gambar 3

Gunakan Rumus Rank
Rumus Rank digunakan pada kolom awal yaitu sel B3 hingga B14, kenapa harus kolom awal? Karena komom ini akan digunakan untuk meng-index vlookup sel.
Lihat penggunaan rumusnya pada gambar 4

Rumus Vlookup untuk menampilkan data
Gunakan rumus vlookup pada sel G3 hingga G14, lihat Gambar 5
=VLOOKUP(F3,$B$3:$D$14,2,FALSE)

dengan menggunakan ketiga rumus tersebut seharusnya telah menghasilkan search box namun masih terdapat kesalahan, tahap berikutnya penanganan kesalahan.
Penanganan Kesalahan
Hilangkan #N/A
Setelah melihat hasilnya ternya pada kolom untuk menampilkan hasil pencarian masih terdapat kesalahan data pada sel cari kosong namun kolom hasil pencarian sudah terisi dan terdapat sel berisi nilai #N/A

pada gambar 6 sel G4
hingga G14
menghasilkan #N/A, bagaimana cara menghilangkannya? Gunakan rumus IFNA
.
Singkatnya yang dikerjakan IFNA
adalah mengembalikan nilai bila TRUE dan jika menghasilkan #N/A akan mengembalikan nilai yang ditentukan.
rumus
=IFNA(VLOOKUP(F3,$B$3:$D$14,2,FALSE),"")
gunakan rumus ini pada kolom F3
hingga F14
, lihat gambar 7

Data tidak tampil

Pada gambar 8 pada sel search telah diisi teks namun hasilnya tidak ada, kesalahan tersebut disebabkan oleh vlookup tidak menemukan lookup_value pada index_pertama/kolom pertama yaitu RANK. RANK sendiri akan mengembalikan nilai kesalahan jika referensinya bukan angka atau null(kosong), berarti kesalah terjadi pada larik angka yang digunakan sebagai referensi oleh RANK yaitu sel D3
hingga D14
, lihat gambar 9

pemecahan masalahnya adalah dengan menggunakan rumus IFERROR
, sama seperti IFNA
, IFERROR
juga akan mengembalikan nilai yang telah ditentukan bila hasil rumus yang digunakan adalah ERROR,
lihat gambar 10, bagaimana menggunakan rumus IFERROR
=IFERROR(SEARCH($G$2,C3),"")

dan hasilnya pada kolom RANK telah terisi angka dan search telah tampil, lihat gambar 11

Menampilkan semua pencarian yang ditemukan
Masalah berikutnya adalah harapannya jika menulis sepenggal kalimat/kata atau bahkan 1 huruf pada hasil pencarian akan memuat semua data yang memuat sepenggal kata tersebut, sebagai contoh bila mengisi huruf "J"
pada kolom cari maka hasil yang ditampilkan adalah JANUARI, JUNI dan JULI namum yang tampil cuma 1 sel saja, lihat gambar 12

masalah ini terjadi diakibatkan oleh kolom rank yang adalah kolom yang dicari oleh VLOOKUP memiliki nilai yang sama, yaitu 1, lihat gambar 13

Gunakan rumus bantuan untuk mengurutkan perangkingan, modifikasi sel search dengan menambahkan rumus ROW()
, lihat rumus dan gambar 14.
=IFERROR(SEARCH($G$2,C3)+ROW()/10,"")
Cara kerja rumus ROW()
adalah mengembalikana nomor baris sel yang terpilih sebagai contoh jika menerapkan rumus ROW pada sel A1
maka akan mengembalikan nilai 1, dimana nilai 1 tersebut adalah baris 1.
Skenario rumus ROW yang digunakan adalah menambah nilai SEARCH
yang diperoleh dengan ROW()/10
. Sehingga menghasilkan nilai yang berbeda di tiap barisnya.

dan hasil yang diperoleh adalah seperi pada gambar 15, Apabila sel cari diisi huruf “J”
maka pencerian dalam larik yang memiliki huruf “J”
akan ditampilkan.

Sebenarnya sampai tahap ini search box sudah jadi, tapi pada kolom baris masih terdapat nilai yang error, untuk memperbaikinya cukup tambahkan rumus IFERROR, lihat rumus dan gambar 16
=IFERROR(RANK(D3,$D$3:$D$14,1),"")

atau bisa juga hide saja kolom RANK dan SEARCH seperti pada gamar 17

Terimakasih telah membaca dan selamat berkreasi