Salah satu fungsi Microsoft Excel untuk kategori lookup yang paling umum dan sering digunakan adalah fungsi VLOOKUP, dimana ia akan melihat dan membaca susunan tabel yang tersusun secara vertikal kemudian menampilkan hasil yang sesuai dengan kata kunci atau nilai yang dituliskan.

kata kunci yang biasa disebut dengan istilah lookup values atau nilai yang ingin dicari ini, sebenarnya tidak bisa berdiri sendiri melainkan membutuhkan bantuan dari sebuah tabel yang berisi data² lengkap dengan nilai² atau yang memuat sebuah frase seperti yang terdapat di nilai lookup values. Tabel ini biasa disebut dengan istilah tabel bantu.
coba perhatikan struktur data yang saya miliki berikut:


Dari data yang saya miliki di atas nampak terlihat bahwa kata yang ingin saya cari (P02) terdapat di dalam tabel bantu dan sejajar dengan nilai ini atau di kolom sebelah kanannya terdapat informasi yang nanti akan ditampilkan sebagai hasil dari pencarian. Inilah syarat utama yang harus dimiliki agar fungsi VLOOKUP berhasil dijalankan, yakni data-data yang dimuat dalam tabel bantu setidaknya harus memiliki nilai yang sama dengan nilai yang akan dicari, kalau tidak demikian pasti yang akan didapatkan adalah nilai atau hasil error seperti #N/A.

Namun beberapa waktu yang lalu saya diminta untuk membantu menyelesaikan soal dari mata pelajaran TIK atau mungkin KKPI, entah kelas atau tingkat berapa, yang tampilan soalnya seperti berikut :

Jika anda perhatikan soal di atas, siswa diminta untuk mencari Harga Batik yang mana tabel bantu sudah disediakan. Akan tetapi setelah diamati secara seksama, sepertinya ada ada ketidaksamaan antara kode yang ada di tabel utama (kata yang ingin dicari) dengan tabel bantu, ambil contoh nilai yang terdapat di baris ke-4, di mana seorang pemebeli bernama ERMALIA membeli sebuah batik dengan kode TB002, namun data-data yang terdapat di tabel bantu yang ada adalah kode TBN002 (nilai harga batik 1.200.000), Nah, di sinilah letak 'kesalahannya'.

Seharusnya kasus di atas dapat diselesaikan secara mudah dengan menggunakan formula HLOOKUP (namun saya yakin anda atau saya lebih suka jika menyelesaikannya menggunakan VLOOKUP, hehehe...), jika saja kode batik yang tertera adalah sama. Namun sayangnya tidak demikian.
Oke, anggaplah soal tersebut memang benar terdapat salah cetak, di mana nilai yang terdapat di kolom C4:C8 harusnya mengikuti nilai-nilai yang terdapat di range C11:E11 untuk nilai Kode Batik atau mungkin sebaliknya, maka formula yang cukup sederhana berikut bisa ditulis di sel D4 untuk mengetahui harga batik untuk kode yang dimaksud.
Fungsi Microsoft Excel
=HLOOKUP(C4;C11:E12;2)
Selanjutnya silahkan kumpulkan kumpulkan lembar jawaban dan berharap guru mata pelajaran anda membenarkan jawaban ini..hehehe.

Namun bagaimana jika soal ini memang seperti apa yang dicetak dalam buku mata pelajaran tersebut dan tidak ada kesalahan cetak ?!. Maka, sudah pasti jawaban menggunakan formula di atas adalah 100% SALAH - karena jawaban yang akan diberikan oleh Microsoft Excel adalah #N/A alias Not Available.

Lantas bagaimana? Formula apa yang harus digunakan untuk menyelesaikan soal ini? Apakah mungkin jika mengubah susunan Tabel Bantu secara vertikal kemudian menyelesaikannya menggunakan formula VLOOKUP bisa menghasilkan nilai yang ingin di cari ?!

Jawabannya adalah TIDAK... dan ini sudah terbukti. Silahkan anda coba sendiri utak-utik formula tersebut jika masih belum percaya.

Oke, karena kita sudah menyepakati bersama bahwa soal yang terdapat dalam buku tersebut memang benar alias tidak salah cetak, maka saya bisa menyelesaikan ini dengan menggunakan dua macam formula yang bisa anda gunakan. Namun sebelumnya saya akan berikan dan jelaskan formula inti untuk menyelesaikan kasus ini. Dan formula inti tersebut adalah sebagai berikut :
Fungsi Microsoft Excel
=MATCH("*"&RIGHT(C4;3);$C$11:$E$11;0)
Saya mengkombinasikan formula MATCH dan RIGHT untuk mengetahui keberadaan nilai yang sama berdasarkan 3 (tiga) nilai terakhir yang ada di sebelah kanan serta menggunakan bantuan WILDCARDS (*), yang mana formula ini akan menghasilkan nilai berupa angka dan nantinya akan kita gunakan sebagai referensi indeks kolomnya.
Jika kita pecah formula ini, maka akan tampak seperti berikut

1. =MATCH
   Formula yang digunakan untuk mengetahui nilai yang memiliki kesamaan dengan apa yang akan di cari dan akan menghasilkan nilai berupa angka, dan angka inilah nanti yang akan kita gunakan untuk membaca indeks kolomnya
2. "*"&RIGHT(C4;3)
   Ini adalah kombinasi antara Wildcards (*) dengan formula teks, dalam hal ini adalah formula RIGHT. Untuk kasus ini saya awali dengan mengecek nilai yang ada di sel C4 yakni sebuah nilai atau kata TB001. Sehingga bunyi dari formula ini adalah "membaca kata apa saja yang 3 angka di belakangnya memiliki nilai 001"
3. $C$11:$E$11
   Merupakan posisi pencarian yang terletak di sel C11 hingga E11, saya gunakan alamat sel absolut ($) agar nanti ketika formula ini di copy tidak mengalami perubahan dalam membaca alamat range ini.
4. 0
   Adalah pilihan nilai yang mesti di cantumkan dalam syntak penulisan formula MATCH, di mana nilai 0 memiliki makna hasil pencarian yang benar-benar cocok.

Coba lihat skema penyelesaian dan pembacaan untuk mencari hasil kode batik berikut:


Setelah anda memahami cara membaca formula di atas, maka selanjutnya adalah menggabung dengan formula lainnya. Dan untuk kasus ini saya akan memberikan 2 (dua) cara dalam menyelesaikan soal tersebut.

Formula 1 - Match & Index
Fungsi Microsoft Excel
=INDEX($C$11:$E$12;2;MATCH("*"&RIGHT(C4;3);$C$11:$E$11;0))
Formula 1 - Match & Offset
Fungsi Microsoft Excel
=OFFSET($B$10;2;MATCH("*"&RIGHT(C4;3);$C$11:$E$11;0))
Selamat.... sekarang anda sudah berhasil mengetahui bagaimana caranya mencari nilai yang tidak berhasil dilakukan menggunakan formula VLOOKUP maupun HLOOKUP.
Jangan lupa kunjungi kami lain waktu untuk mengetahui kehebatan Microsoft Excel lainnya. Ciao Adios

Masih mau belajar lagi !

Yuk, klik tautan berikut ini :