Fungsi Flookup Dalam Microsoft Excel Beserta Contohnya

Banyak pembaca yang ingin tahu tentang rumus VLOOKUP pada Program Inventori Kontrol. Karena ingin menyesuaikan program tersebut agar lebih cocok dengan kebutuhan mereka. Banyak halaman di internet membahas rumus ini, tapi hanya beberapa yang memberikan contoh penerapan.

Rumus VLOOKUP berfungsi untuk mencari suatu referensi atau acuan tertentu pada data di kolom pertama tabel, lalu mengambil data lain di baris yang sama dengan baris data referensi tapi pada kolom berbeda berdasar nomor index kolom.

Sebagai contoh: ada suatu tabel dimana terdapat nomor telpon, nama dan alamat, sebagaimana gambar dibawah. Nomor telpon digunakan sebagai patokan atau referensi. Jika pada sel kuning (B2) diinput nomor telpon, maka pada sel C2 akan muncul nama, dan pada sel D2 akan muncul alamat.

Pada sel C2 terlihat rumus yang digunakan yaitu =VLOOKUP(B2,B4:D7,2,0). Bagian-bagian rumus tersebut dijelaskan berikut ini dengan ditandai huruf merah:

=VLOOKUP(B2,B4:D7,2,0).

Referensi pada rumus itu adalah sel B2, sel kuning, yang berisi nomor telpon 08111011. Nomor telpon tersebut akan dicari siapa pemiliknya, dan dimana alamatnya.

=VLOOKUP(B2,B4:D7,2,0).

Rumus akan mencari data yang sama dengan sel B2 pada kolom B dari tabel yang tertulis pada daerah atau range (B4:D7).

=VLOOKUP(B2,B4:D7,2,0).

Rumus menemukan data 08111011 pada sel B6, lalu mengambil data di kolom kedua (2) dari tabel, maka akan didapat data Alex.

=VLOOKUP(B2,B4:D7,2,0).

Angka 0 pada bagian akhir rumus dinamakan range_lookup yang menentukan tingkat akurasi, jika ditulis satu (1) maka rumus akan mencari yang mirip dengan data referensi 08111011, jika ditulis 0 maka rumus hanya mencari data yang sama dengan data referensi 08111011.

Terlihat dibawah adalah rumus dengan range_lookup menggunakan 1 menggantikan 0. Tampak data referensi adalah nomor telpon 08111015, yang sebenarnya tidak ada di tabel nomor telpon berekor 5. Tapi rumus mencari yang mirip yaitu 08111012, dan itu adalah nomor telpon Tina. Sedangkan rumus VLOOKUP pada alamat (Address) tidak menggunakan 1 pada range_lookup tapi menggunakan nol (0), sehingga rumus tidak menemukan data yang cocok (#N/A).

Sebagaimana rumus pada Excel lainnya, rumus ini dapat tulis langsung pada sel. Tapi agar lebih jelas, berikut adalah langkah-langkah yang diperlukan untuk mengaplikasikan rumus VLOOKUP dengan menggunakan prosedur normal. Tampak pada gambar dibawah mouse (panah merah) menunjuk pada tombol fx (Insert Function) dari Formula Bar. Akan dicantumkan rumus VLOOKUP pada sel C2.

Setelah tombol fx ditekan maka akan muncul tampilan Insert Function sebagaimana gambar dibawah.

Jika rumus tidak terlihat di jendela Select a function, ketik vlookup pada jendela Search for a function lalu tekan Go. Setelah muncul rumus VLOOKUP pada jendela Select a function, terlihat diatas berlatar biru, tekan tombol OK.

Selanjutnya akan muncul jendela Function Arguments, seperti pada gambar dibawah. Sebagai Lookup_value adalah sel B2, yaitu nilai yang akan dicari pada tabel atau bisa disebut nilai referensi.

Table_array adalah tabel yang akan diambil datanya, yaitu B4:D7, judul tabel boleh tidak dimasukkan.

Col_index_num adalah posisi kolom dimana data akan diambil, yaitu kolom ke dua dari tabel, maka pada rumus ditulis 2.

Range_lookup adalah 0 atau FALSE, sehingga rumus hanya akan mencari data yang benar-benar sama dengan data referensi.

Selanjutnya tekan tombol OK maka rumus akan tertulis dan berfungsi pada sel C2. Lakukan hal yang sama untuk sel D2 agar rumus mencari alamat (Address) dari nomor telpon referensi.

Perlu diperhatikan bahwa nomor telpon diawali dengan angka 0 untuk kode area atau nomor handphone / cellular phone / ponsel . Pastikan kolom atau sel yang tertulis nomor telpon diformat dalam bentuk Text. Jika diformat dalam sebagai General, maka akan dikenali sebagai angka, sehingga tidak bisa menuliskan angka 0 di depan.

Program Inventori Kontrol

Pada lembar Excel untuk Program Inventori Kontrol, rumus VLOOKUP digunakan untuk mengambil data Deskripsi dan Lokasi, dengan Part Number sebagai referensi atau acuan.

Terlihat di Formula Bar rumus VLOOKUP menggunakan referensi sel B4 atau Part Number, membaca data pada tabel di sheet ‘register’ di kolom D sampai kolom G yang mana tertulis pada rumus sebagai register!D:G.

Di sheet register, kolom yang diambil datanya adalah kolom ke dua yaitu kolom E, terlihat pada rumus tertulis 2.

Dengan akurasi data yang harus benar-benar sama dengan data referensi maka pada rumus Range_lookup tertulis 0.

Program ini dapat digunakan untuk keperluan bisnis seperti memantau stok di gudang, toko, bisnis online, check harga barang dan lain-lain. Dengan sedikit modifikasi maka dapat digunakan untuk mencatat data karyawan, data siswa, dengan nomor induk sebagai referensinya.

Absensi Karyawan

Bagi anda yang menjalankan bisnis atau bagian personalia yang perlu memantau kehadiran karyawan, program berikut dapat membantu kerja anda. Pada lembar Excel ini, terdapat contoh rumus VLOOKUP yang digunakan untuk membaca nomor identitas karyawan (ID), lalu rumus menemukan dan menampilkan nama karyawan yang sesuai dengan nomor identitas tersebut. Dengan menggunakan nomor identitas maka pencatatan jam kerja karyawan akan jauh lebih mudah dan cepat. Tidak perlu menulis setiap nama terus menerus, yang dapat menyebabkan terjadinya salah ketik. Sel-sel yang mengandung rumus diberi warna biru.

Terlihat digambar atas formula VLOOKUP pada sel C13, yang membaca ID karyawan di sel B13 dan menulis pada sel C13 sebagai Margareth Isabella Taylor. ID dan nama karyawan sudah didaftarkan pada tabel REGISTER dipojok kiri atas. Tabel REGISTER inilah yang dilihat oleh rumus VLOOKUP untuk mendapatkan nama karyawan berdasarkan ID.

Input data pada tabel absensi adalah: tanggal, ID karyawan, jam masuk kerja, jam keluar untuk makan siang, jam masuk setelah makan siang, dan jam pulang kerja. Keterangan dapat ditambahkan jika ada data yang tidak normal.

Pada tabel jam kerja sudah diberi Conditional Formatting, yang akan merubah warna huruf dan angka jika sesuai kondisi tertentu secara otomatis. Sehingga jam masuk (IN) akan merah jika lebih dari jam 8:00. Jam keluar makan siang (OUT for lunch) akan merah jika kurang dari jam 12:00. Jam masuk setelah makan siang (IN after lunch) akan merah jika lebih dari jam 13:00. Dan jam pulang (OUT) akan merah jika kurang dari jam 17:00. Waktu total kerja (TOTAL) akan merah jika kurang dari 8:00.

Pada gambar diatas, terlihat panah biru pada sel I4 dimana tertulis rumus VLOOKUP dan digunakan untuk mencheck nama seorang karyawan dengan nomor K0004, dan rumus menemukan nama Michael Shoemaker. Lalu pada sel J4 terdapat rumus SUMIF yang menjumlah total jam kerja untuk Michael Shoemaker yaitu 17:09.

Pada gambar diatas juga terlihat panah kuning yang menunjukkan TOTAL dengan memakai rumus SUBTOTAL. Nilai ini adalah jumlah dari nilai di tabel pada kolom TOTAL dan akan berubah bergantung pada filter yang terapkan. Karena filter hanya menampilkan K0004, maka nilai pada sel L12 adalah 17:09 yaitu total untuk Michael Shoemaker.

Contoh sederhana dari rumus VLOOKUP yang digunakan untuk daftar nomor telpon sebagaimana dijelaskan diawal, juga tercantum pada buku Excel tersebut pada sheet simple.