Fungsi VLOOKUP dengan Data table_array Dinamis

"Ad Blocker" terdeteksi

Untuk kenyamanan bersama, mohon nonaktifkan "Ad Blocker" anda.

Misal ada data seperti gambar dibawah.

rumus fungsi vlookup dengan data table_array dinamis 01

Berapakah diskon untuk masing-masing penjualan. Untuk menyelesaikannya digunakan fungsi VLOOKUP.

Letakkan kursor dicell H3.

Ketik formula berikut:

=VLOOKUP(G3,$A$3:$C$4,3,FALSE)

Kopi cell H3 kemudian paste dirange H3:H8. Hasilnya seperti gambar dibawah.

rumus fungsi vlookup dengan data table_array dinamis 02

Diskon masing-masing penjualan diketahui.

Jika ada data penjualan baru, diskon bisa dihitung dengan mengkopi formula yang ada diatasnya. Masalah muncul jika ada penjualan dengan kategori yang belum terdaftar diskonnya.

rumus fungsi vlookup dengan data table_array dinamis 03

Diskon penjualan untuk kategori baru menghasilkan error #N/A, menambah data diskon untuk kategori baru juga tidak menyelesaikan masalah, karena table_array formula mengarah kerange $A$3:$C$4, sedangkan informasi diskon untuk kategori baru berada diluar range tersebut.

Solusinya adalah dengan mengubah data table_array supaya diskon untuk kategori baru masuk didalamnya.

Edit formula dicell H3, ubah formula menjadi:

=VLOOKUP(G3,$A$3:$C$5,3,FALSE)

Kopi cell H3 kemudian paste dirange H3:H10. Hasilnya seperti gambar dibawah.

rumus fungsi vlookup dengan data table_array dinamis 04

Diskon penjualan untuk kategori baru bisa diketahui.

Permasalahan ini akan selalu muncul jika ada penjualan untuk kategori baru. Setiap ada diskon untuk kategori baru selalu harus mengubah formula.

Ada cara untuk mencegah mengubah formula setiap ada diskon untuk kategori baru, yaitu dengan menggunakan dynamic named range.

Solusi ini akan mengubah data table_array menjadi dinamis. Jika data diskon hanya ada 2 maka range hanya mempunyai dua baris. Jika ada penambahan data diskon maka range akan berubah jumlah barisnya menyesuaikan dengan jumlah data baru yang dimasukkan.

Solusi ini menggunakan bantuan Fungsi OFFSET dan Fungsi COUNTA. Fungsi OFFSET digunakan untuk membentuk sebuah range. Fungsi COUNTA bertugas menjadikan range menjadi dinamis dengan mencek apakah ada penambahan data diskon atau tidak.

Dynamic Named Range

Berikut langkah untuk membuat Dynamic Named Range

Buat Range A3:C5

Beri Nama Range
Caranya dengan mengklik name box yang ada dipojok kiri atas, kemudian ketik TABDISKON, kemudian tekan tombol ENTER.

rumus fungsi vlookup dengan data table_array dinamis 05

Hasilnya adalah sebuah range dengan nama TABDISKON. Jika nama range digunakan didalam formula maka akan sama dengan range A3:C5. Jika ada penambahan data dimensi range masih tetap, untuk menjadikan dinamis digunakan fungsi OFFSET dan Fungsi COUNTA.

Edit range TABDISKON

Caranya Dengan menekan tombol CTRL+F3, muncul dialog name manager. Menampilkan semua nama range yang pernah dibuat.

rumus fungsi vlookup dengan data table_array dinamis 06

Pilih range TABDISKON kemudian klik edit, muncul dialog edit name.

rumus fungsi vlookup dengan data table_array dinamis 07

Untuk menjadikan range dinamis ubah bagian refres to dengan fungsi OFFSET dan COUNTA.

=OFFSET(‘Fungsi VLOOKUP’!$A$3,0,0,COUNTA(‘Fungsi VLOOKUP’!$A:$A)-2,3)

*’Fungsi VLOOKUP’ adalah nama worksheet, bisa berbeda-beda untuk masing-masing file.

Klik tombol OK. Jadilah sebuah range dinamis, jika ada penambahan data otomatis dimensi range akan berubah.

Untuk menggunakan didalam formula edit cell H3, ubah formula menjadi:

=VLOOKUP(G3,TABDISKON,3,FALSE)

Kopi cell H3 kemudian paste dirange H3:H10.

Jika ada penambahan data diskon bisa langsung diketahui dengan menambah data diskon kategori dan mengkopi formula diatasnya, tanpa harus mengubah formula.

rumus fungsi vlookup dengan data table_array dinamis 08

Artikel lain yang berhubungan dengan tips dan trik ini

 

ADA KOMENTAR? 🙂

GRATISS!!!

Kumpulan shortcut excel dalam file PDF,

langsung ke email anda

NB: Pastikan email anda valid dan aktif. Kami sering gagal mengirim file karena alamat typo atau tidak aktif

TERIMA KASIH

Silahkan cek inbox anda, jika email kami tidak muncul

cek folder spam atau tab promotion (GMail)