Advanced Vlookup: dengan range dinamis dan anti error

Kita tahu bahwa vlookup akan mencari suatu nilai tertentu di suatu tabel dan mengembalikan nilai sesuai dengan posisi yang diminta (baca http://eoditor.com/2012/03/vlookup-function/)

Kita lihat gambar dibawah ini

Dynamic Range

Rumus pertama

 

akan menemukan kata petty cash, namun perlu dilihat bahwa posisi kolom yang dicari diisi dengan mengetik angka 2 yang berarti jika letak kolom "Account Name" bergeser karena diinsert kolom sebelumnya maka formula ini akan gagal

Untuk itu maka angka 2 kita cari juga dengan rumus yaitu match

MATCH($F$1,$A$1:$B$1,0)

yang akan mencari kata "Account name" /cell $F$1di range $A$1:$B$1 (baris header) , yang akan menemukan di posisi ke 2 jadi mo diinsert berapapun kolom posisi account name tetep akan ketemu di posisi yang benar

Atau bisa juga kita ganti cell F1 dengan B1 atau kita ketik "Account Name" di formula

Jika Error?

Bagaiman jika tidak ketemu? excel akan memberikan nilai #N/A (Not Available) , agar penampilan bagus kita bisa memberikan nilai lain atau blank jika tidak ketemu, kita pake IFERROR (hanya ada di excel 2007 keatas), fungsi ini akan memberikan hasil sesuai formula yang diberikan, namun jika error maka akan memberikan nilai sesuai keinginan kita (argumen kedua)

Jita lihat rumus baru kita + iferror

=IFERROR(VLOOKUP(E6,$A$1:$B$16,MATCH(F3,$A$1:$B$1,0),FALSE), "tidak ketemu")

Fungsi diatas akan memproses rumus

VLOOKUP(E6,$A$1:$B$16,MATCH(F3,$A$1:$B$1,0),FALSE)

Namun jika error yaitu #N/A atau lainnya maka akan diberikan argumen yang kedua yaitu

"tidak ketemu"

Berikut akan diberikan gambar jika range table diinsert kolom baru

Rumus pertama (tidak dinamis) akan memberikan nilai 0 karena kolom kedua memang kosong

Rumus kedua & ketiga (dinamis) akan memberikan nilai yang benar

Rumus ke 4 dan ke 5 akan memberikan hasil yang berbeda jika tidak ketemu, rumus ke 5 (anti Error) akan memberikan hasil yang lebih descriptif

File bisa didonlot disini

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *