27.04.2011 – Fungsi VLOOKUP, IF, dan menghilangkan #N/A pada Microsoft Excel

Beberapa hari terakhir ini, saya sibuk dengan hobi baru. Membuat formula untuk excel. Hmm, semacam low level programming ?. Ternyata mengasyikan :), saya terlarut cukup lama dengan ini. Konsepnya, semua hal pasti bisa dilakukan asal bisa dibayangkan. Dan efeknya, saya penuh imajinasi, pasti bisa kalo di-begini-kan, pasti bisa kalau di-begini-in. Suka. Meski, yah kadang-kadang idenya terlalu ekstrim dan ternyata tidak bisa di-cover oleh excelnya sendiri, he he.

Dan berikut beberapa hasil oprekan formula yang saya buat. oia, untuk contoh saya tampilkan data-data motor Honda karena kebetulan saya bekerja disana, jadi tutorial ini emang sengaja saya tulis sambil bekerja, gak masalah kan ;). Nah, sebelum memulai, perlu anda perhatikan, sering error yang terjadi adalah kesalahan penggunaan karakter ; (titik koma) dan , (koma) untuk memisahkan antarperintah dalam formula. Ini bergantung pada setting regional komputer anda. Karakter ; (titik koma) digunakan pada regional Indonesia. Sedangkan karakter , (koma) digunakan pada regional English (United States). Jadi, kalo formula anda tidak berfungsi, jangan dulu panik, coba deh cek untuk hal ini.

Fungsi VLOOKUP

Idenya, saya punya database berisi nilai-nilai dan keterangannya. Berdasarkan database tersebut, saya tampilkan keterangan saat formula menemukan nilai yang sama dengan database tadi. Fungsi yang saya gunakan adalah VLOOKUP.

=VLOOKUP(k3,master_plafond!$B:$C,2,0), dimana



VLOOKUP : Nama fungsi yang akan digunakan. awalan v menunjukkan data yang akan diambil berada dalam kolom vertikal. Kalau datanya ingin diambil dari kolom horizontal ? ya, namanya jadi hlookup dunks :). Perlu diperhatikan, setiap fungsi pada excel membutuhkan karakter () yang mengapit formula bersangkutan.
K3 : Kolom berisi patokan nilai yang akan dicari datanya dari database.
master_plafond!$B:$C : Database dengan kolom berisi nilai yang sesuai dengan patokan nilai yang akan dicari. Apabila diperhatikan, penulisan master_plafond! terjadi pada saat nilai yang diambil tidak berada dalam worksheet yang sama dengan kolom dimana formula ini dituliskan.
2 : Posisi kolom ke- dari range kolom yang di-sort dimana berisi data yang akan ditampilkan manakala ditemui nilai yang sama dengan patokan K3.
0 : Nah, saya belum ngedapetin penjelasan teknis tentang angka 0. Mungkin semacam angka default yang harus ada. nanti saya cari deh, sebenernya angka ini teh representasi apa ;) .

Masih bingung ? Berikut saya kasih ilustrasinya. Jadi kondisinya begini, saya ingin saat saya tulis nama motor di kolom Nama_Motor pada worksheet Olahan_Data, secara otomatis kolom Kode_Tipe akan menampilkan kode motor tersebut yang databasenya diambil dari worksheet Master_Plafond. Dan berikut capture screen-nya.

Fungsi_VLOOKUP #1


Fungsi_VLOOKUP #2


Fungsi_VLOOKUP #3

Fungsi IF

Idenya, saya berada dalam 2 kondisi. Pada saat formula menemukan kondisi 1, saya ingin menampilkan data UVW dari worksheet S kolom D. Sementara itu, apabila formula menemukan kondisi 2, saya ingin menampilkan data XYZ dari worksheet S kolom F. Dan fungsi yang saya gunakan adalah IF.

=IF(K3=”-”;”silakan isi nama motor”;VLOOKUP(K3;Master_Plafond!$B:$C;2;0)), dimana


IF : Nama fungsi yang digunakan pada kondisi ini.
K3=”-” : Kondisi yang menunjukkan situasi pada saat pada kolom K3 memiliki nilai -. Kondisi ini juga dijadikan dasar pengambilan keputusan.
“silakan isi nama motor” : Statement ini akan muncul pada saat kolom K3 memiliki nilai -. Atau dapat juga diartikan, manakala kondisi terpenuhi, maka eksekusi dengan pilihan ini akan dilakukan.
VLOOKUP(K3;Master_Plafond!$B:$C;2;0) : Apabila kolom K3 memiliki nilai dan sesuai dengan database pada master_plafond, maka data yang sesuai akan ditampilkan. Apabila kolom terisi, tapi nilainya tidak sesuai dengan database, maka statement #N/A akan ditampilkan. Perlu diketahui juga, section ini dapat diganti dengan statement “nama motor tidak sesuai dgn master”, misalnya. Dan secara otomatis, statement atau hasil eksekusi fungsi VLOOKUP tersebut akan ditampilkan manakala kondisi tidak terpenuhi.

Masih bingung juga ? Jadi untuk fungsi ini, begini ilustrasinya. Saya ingin, pada saat saya mengisi kolom K3 dengan -, maka kolom O3 akan menampilkan tulisan silakan isi nama motor. Apabila, nama motor terisi dengan kalimat selain -, maka kolom O3 akan menampilkan hasil VLOOKUP. Dan apabila dari hasil VLOOKUP, tidak ditemui kesamaan, kolom O3 akan menampilkan kalimat #N/A. Kalau kolom K3 tidak diisi samasekali, gimana ? Yaa, hasil VLOOKUP juga pasti gagal dan nilai yang akan ditampilkan #N/A juga.
Nah, berikut capture screen-nya,

Fungsi_IF



Menghilangkan #N/A


Idenya, saya agak terganggu dengan tulisan #N/A saat formula yang saya gunakan tidak menemui hasil. Kenapa ? Sederhana ajah sih, karena kata-katanya ga user friendly, he he he. Lalu, bisa ga kalo #N/A ini diganti dengan kalimat lain ? Ooohh, tentu bisaa ;). Dan yang saya lakukan adalah menyisipkan statement ISNA dalam formula saya.

=IF(ISNA(VLOOKUP(K3;Master_Plafond!$B:$C;2;0));”tipe blm ada di master”;VLOOKUP(K3;Master_Plafond!$B:$C;2;0)), dimana

ISNA(VLOOKUP(K3;Master_Plafond!$B:$C;2;0)) : Kalimat ISNA ditempatkan sebagai suatu fungsi dan diletakkan didepan apitan formula yang ga saya ingin apabila ia gagal lalu menampilkan #N/A.
“tipe blm ada di master” : Statement ini akan menggantikan kalimat #N/A pada saat formula gagal memperoleh data.
VLOOKUP(K3;Master_Plafond!$B:$C;2;0) : Formula yang dimanfaatkan untuk mengambil data.

Nah, sekarang saya tampilkan screenshoot-nya yah…

Menghilangkan #N/A



Well Done. Semoga bermanfaat yah… ^-^

25 thoughts on “27.04.2011 – Fungsi VLOOKUP, IF, dan menghilangkan #N/A pada Microsoft Excel

  1. wah, terimakasih update infonya mas rio.
    btw, keterangan dari mas rio, mudah2an bisa membantu yang kesulitan memahami inisialisasi angka2 dalam vlookup yah.

  2. klo misalkan pada suatu kolom kode,,dimana kodenya telah terisi kode2,,dan kt ingin mndptkan harga dri kode yg telah kita masukkan seluruhnya lebih awal,,knapa angkanya sering sama y??padahl,,kodenya beda,,hargany jg beda//trims

  3. Sepengetahuan saya, excel akan mengikuti regional setting pada komputer anda. Anda bisa mengaksesnya melalui Control Panel. Semoga membantu yah, mbak Lisa :)

    Mbak Rita, kalau memungkinkan silakan coba sampel file excelnya dikirimkan ke email saya, nda1st@yahoo.com. Nanti saya coba lihat ada kesalahan dimana dan cara untuk menyelesaikannya. :)

  4. mohon petunjuk mas bro,

    biasanya stlh saya isi cell dg Vlookup ke sumber yang akan di ambil, itu berhasil, tp 1 hari ini setelah di Vlookup dan dicopy ke baris dibawahnya, hasilnya sama..apakah ada perintah pada “option” yang berubah ato ada masalah pada excellnya. please feedback-nya mas bro..ASAP
    Thanks.

  5. @aq : thanks yah penjelasannya, kemungkinan itu benar.

    @JIA : mudahnya, #N/A pasti muncul kalo nilai yang diminta tidak ditemui

    @Wishnew : untuk analisa ini, saya perlu sample filenya mas. Mungkin kalo ada, bisa dikirim via japri ke nda1st@yahoo.com, nanti saya coba bantu cari pemecahannya

  6. siang,
    maaf sblmnya mau tanya. saya lagi pake rumus vlookup saat ini
    #N/A tetap muncul padahal kalau dilihat data atau kunci utama benar tdk ada yg beda
    tapi ttp muncul kenapa yah?

    sblm2nya saya blm pernah sih kyk gini, biasanya jg kalau hasilnya #N/A pasti bisa dicek data sbgai kunci utamanya
    tks

  7. Format cell-nya sudah disesuaikan, mbak Intan ?
    Sebetulnya lebih enak kalo saya liat sampel excelnya, jadi bisa saranin lebih tepat..
    Kalo berkenan, bisa dikirim aja ke nda1st@yahoo.com, nanti saya coba buat cari kenapa masalahnya…

    Salam,

  8. mohon infonya apabila ingin menghilangkan #NA menjadi Blank ( tidak ada tulisan didalam cel ) itu bagai mana caranya y…
    terimakasih atas bantuan dan kerjasamanya.
    mohon balasan dikirim ke jaja.grt@gmail.com

    • Hi Mas Jaja Miharja,

      Sorry for very late reply.

      Untuk menghilangkan #N/A menjadi blank dalam artian tidak terlihat tulisan apa2,
      mas Jaja bisa menyisipkan spasi (” “).
      Berikut contoh formulanya,

      =IF(ISNA(VLOOKUP(K3;Master_Plafond!$B:$C;2;0));” “;VLOOKUP(K3;Master_Plafond!$B:$C;2;0))

      Semoga membantu yah.

  9. Mba nanda, saya mau nanya…
    ada dua buah kondisi, pada tabel 1 kondisinya sudah tertera,, sebagai contoh yah, pembuatan sertifikat misalnya akte tanah statusnya completed, akte lahir In Progres, dan seterusnya.. dan pada tabek ke dua, kita buat kolom untuk status sertifikatnya. jika dia komplit, maka secara otomatis statusnya akan tertera di komlom Completed dengan inisial C, jika masih inprogres, maka statusnya akan tertera di kolom inprogres. Untuk membuat seperti itu rumus apa yang kita gunakan mba.. thanks…atas bantuannya,,,,

  10. Numpang Nanya nich Mas.
    1. kenapa pada lembar kerja excel saya setiap kursor diarahkan ke sembarang Cell selalu diikuti
    dengan comment.
    2. Masalah gak bisa insert module pada VBA Project

    Mohon Pencerahan

    Tks

  11. It was hard to find your blog in google search results.
    I found it on 20 place, you have to build some quality backlinks , it will help you to get
    more visitors. I know how to help you, just search in google – k2 seo tips

  12. Gan,, gmana kalo sumber tabelnya ada yg sama? Mis nama motornya sama, tapi kode tipenya berbeda,,, kalo gt gmana gan?

  13. I read a lot of interesting content here. Probably you spend a lot of time writing, i know how to save you
    a lot of time, there is an online tool that creates high quality,
    google friendly posts in minutes, just search in google –
    laranitas free content source

  14. I read a lot of interesting articles here. Probably you spend a lot of time writing, i know how to save
    you a lot of time, there is an online tool that creates unique, google friendly articles in minutes, just search in google – k2seotips unlimited content

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>