Pencarian data dengan rumus VLOOKUP akan lebih powerfull jika dilengkapi tabel lookup yang bersifat dinamis. Tabel lookup yang dinamis memungkinkan kita mengganti tabel rujukan tanpa perlu mengutak-atik rumus untuk merubah referensi. Cukup hanya dengan merubah data input maka informasi dari tabel yang sesuai dapat dengan cepat segera diperoleh. Untuk menjadikan tabel lookup bersifat dinamis, kita cukup menggabungkan fungsi VLOOKUP dengan fungsi INDIRECT.
Contoh Soal:
Anggaplah sudah tersedia dua buah tabel penjualan produk, masing-masing tabel terdiri atas 2 kolom. Tabel 1 berisi data penjualan di bulan Januari dan Tabel 2 berisi data penjualan di bulan Februari. Selanjutnya anda harus membuat rumus yang dapat mengambil data penjualan dari kedua tabel tersebut. Namun rumus tersebut nantinya harus fleksibel dan berlaku umum, dengan artian tidak perlu merubah referensi tabel lookup ketika rumus dicopy ke sel di bawahnya. Untuk lebih jelasnya, contoh soal excel ini dapat dilihat dalam gambar di bawah ini:
Sebelum melangkah ke contoh rumus VLOOKUP dengan tabel referensi yang bersifat dinamis, mari kita coba terlebih dahulu menggunakan rumus yang biasa. Misalnya di sel F3, kita ketikan rumus berikut untuk mencari nilai penjualan buah apel di bulan Januari.
=VLOOKUP(D3,$A$3:$B$5,2,0)
Jika rumus tersebut di copy ke sel di bawahnya (sel F4) maka rumus nya akan menjadi
=VLOOKUP(D4,$A$3:$B$5,2,0)
Perhatikan parameter yang di-highlight kuning yaitu range tabel lookup $A$3:$B$5. Supaya lebih mudah dibaca selanjutnya saya tulis A3:B5. Referensi ini ketika di copy ke sel dibawahnya masih sama dan mengacu pada tabel bulan Januari. Padahal kita menginginkan data di bulan Februari.
Untuk mendapatkan hasil yang benar, tentu saja kita harus merubah referensi tersebut menjadi $A$9:$B$11 atau A9:B11 yang mengacu ke tabel di bulan Februari. Jika ada banyak tabel maka kita harus merubah secara manual setiap kali mencopy rumus. Cara ini mungkin tidak dikehendaki.
Solusi: Jadikan Tabel Lookup Dinamis Menggunakan INDIRECT.
Untuk menjadikan tabel dinamis, bisa saja di kolom E (kolom Bulan), langsung diketikan referensi A3:B5 untuk menggantikan Januari serta referensi A9:B11 untuk menggantikan Februari. Tetapi penggunaan kode referensi secara langsung akan menyulitkan memahami formula. Oleh karena itu sangat disarankan untuk membuat nama pada masing-masing tabel.
Misalnya: Tabel 1 kita nama “Januari”
Caranya seleksi range A3:B5 kemudian ketikan "Januari" (tanpa tanda kutip) pada kotak nama yang terletak di bagian sebelah kiri atas (disamping formula bar)
Lakukan proses yang sama pada tabel 2 (range A9:B11) dan beri nama "Februari"
Sampai tahap ini, kita sudah memberikan nama pada masing-masing tabel. Nama inilah yang pada tahap selanjutnya akan dijadikan referensi bagi fungsi INDIRECT.
Sehingga kita bisa mengetikan rumus berikut untuk mendapatkan data penjualan sesuai data produk dan bulan yang diinput pada kolom D dan kolom E
=VLOOKUP(D3,INDIRECT(E3),2,0)
Penjelasan Rumus:
Kita ketahui, secara umum rumus VLOOKUP dapat dituliskan sebagai berikut:
=VLOOKUP(kataKunci,TabelLookup,NomorKolomHasil,0)
Sehingga rumus =VLOOKUP(D3,INDIRECT(E3),2,0) dapat dijelaskan secara rinci melalui argumen rumus yang digunakan yaitu:
- kataKunci merupakan nilai yang diinput pada sel D3 (contoh “Apel”)
- tabelLookup diwakili oleh fungsi INDIRECT(E3). Rumus ini akan mereferensikan nama range yang diinput di sel E3. Jika diinput Januari, maka akan mereferensikan range yang bernama Januari
- nomorKolomHasil adalah nomor 2 yaitu nomor urut kolom data penjualan dalam tabel.
Dengan menggunakan bantuan fungsi INDIRECT ini maka jika kita merubah inputan nama bulan pada kolom E, maka hasil rumus akan mengikuti dan menyesuaikan dengan bulan yang sesuai. Rumus tersebut juga bisa dicopy ke sel dibawahnya dengan aman tanpa harus mengutak-atik referensi lagi untuk menghasilkan nilai yang benar.
Demikian pembahasan singkat mengenai penggunaan fungsi INDIRECT untuk menjadikan tabel dinamis dalam rumus pencarian data menggunakan VLOOKUP.
Contoh Rumus Excel Lainnya:
Contoh Rumus Excel Lainnya:
Artikel ini sangat membantu, terima kasih atas penjelasannya. Semangat selalu...
ReplyDelete