Belajar Microsoft Excel: Vlookup Excel- Rumus Jitu Yang Wajib Dikuasai

Belajar Formula Excel VLOOKUPDiakui atau tidak , masih banyak pengguna excel yang belum mengerti atau bahkan belum tau apa itu rumus VLOOKUP.

 Padahal fungsi excel yang satu ini sungguh penting dan sungguh berkhasiat utamanya jikalau kita melakukan pekerjaan dengan banyak data yang terdiri atas bayak baris.

Untuk pembaca yang sudah pakar , hal ini niscaya tidak asing. Mohon dikoreksi saja jikalau ada yang salah dalam postingan bimbingan VLOOKUP Excell ini.

Nah ini kira-kira apa yang mau kita diskusikan wacana rumus VLOOKUP.

Apa itu Rumus VLOOKUP
Rumus VLOOKUP antar Sheet
Rumus VLOOKUP antar Workbook atau File Excel Yang Berbeda
Referensi Nama Range Cell dalam Rumus VLOOKUP
Rumus VLOOKUP untuk Klasifikasi Grade Nilai
Bisakah Rumus VLOOKUP Bekerja dari Kanan Ke Kiri?

……

Berikut gambaran tumpuan permasalahan dimana fungsi VLOOKUP diperlukan.

Misalnya: ada data nama penerima suatu kegiatan dan disimpan dalam suatu tabel terdiri atas 4 kolom yakni Nomor , Nama , Jenis Kelamin dan Alamat.

Kemudian seseorang meminta anda untuk mendapatkan data yang terkait dengan nomor urut tertentu.

Bagaimana caranya agar untuk menerima data dimaksud , secara cepat memakai rumus.

Bagaimana Menggunakan Rumus VLOOKUP

Jawabnya : ya pakelah rumus VLOOKUP , bukan diPELUK yach …:-)
VLOOKUP merupakan abreviasi dari Vertikal Lookup , atau secara harfiah lookup sanggup diartikan selaku menyaksikan dengan seksama. Makara fungsi VLOOKUP dipakai untuk menyaksikan dengan seksama tabel secara vertikal (dari atas ke-bawah). Adapun manfaatnya yakni untuk mendapat nilai dari kolom lain yang terkait dengan data yang sudah dikenali dalam baris yang sama.

Berikut syntax atau cara penulisan fungsi VLOOKUP dalam lembar kerja m s excel
=VLOOKUP(lookup_value ,table_array ,col_index_num ,[range_lookup])
Ah sok inggris , baiknya kita coba indonesiakan saja… , mungkin menyerupai ini jadinya:
=VLOOKUP(nilai_dilihat ,tabel_dilihat ,nomor_kolom ,[lingkup_lihat]
Ups.. agak beratakan terjemahan bahasa indonesia nya.  Tapi tak apalah , yang penting saya dan anda sanggup dapat memahaminya.
  • Nilai_dilihat yakni data berupa angka maupun text yang mau dicari
  • Tabel_dilihat yakni tabel menyaksikan nilai_dilihat dimana posisinya pada kolom pertama (paling kiri) dari tabel tersebut
  • Nomor_kolom yakni nomor urut kolom dalam tabel_dilihat untuk mendapat data tertentu yang terkait dengan nilai_dilihat
  • Lingkup_lihat yakni cara penelusuran apakah mengharapkan data yang persis sama atau pendekatan. Parameter ini bersifat opsional tetapi sungguh penting. Nilai 0 atau FALSE jikalau mengharapkan hasil yang persis sama atau nilai 1 atau TRUE jikalau mengharapkan data pendekatan. Secara default jikalau parameter ini diabaikan maka akan memakai nilai 1 atau TRUE. Dalam prakteknya mungkin kita akan lebih sering memakai 0 atau FALSE. Oleh alasannya yakni itu parameter ini wajib diisi walaupun bersifat opsional.
Kembali ke tumpuan kasus:

Bagaimana cara mendapat data Nama , Jenis Kelamin dan Alamat dari tabel data penerima dengan segera memakai rumus?

Berikut gambaran jawabannya:

Rumus VLOOKUP Cepat
Belum Faham ?

Kira-kira beginilah penjelasannya dengan analogi:

Lihat Seksama Dengan Mata Kepala

Lihat Seksama Dengan Rumus VLOOKUP
Kita ingin menyaksikan Nama Peserta nomor 10
Kita ingin menyaksikan Nama Peserta nomor 10 mengunakan pemberian rumus excel VLOOKUP
Lihat dengan seksama dari atas ke bawah pada kolom Nomor hingga ditemui 10
Rumus menganalisa satu persatu dari atas kebawah kolom nomor (paling kiri) tabel A7:D22 hingga ditemui 10
Setelah nomor 10 ditemui , Mata kita bergeser ke sebelah kanannya , pada kolom selanjutnya yakni kolom ke-2 yang berisi Nama
Begitu 10 ditemui , excel menganalisa pada kolom urutan ke-2 sesuai rumus  =VLOOKUP(10 ,A7:D22 ,2 ,0)
Berteriaklah : Ya Aku Tahu Kau Nomor 10 , Namamu Wati kan?
Excel menginformasikan anda: nomor urut 10 kolom ke-2 yakni si Wati
Anda Penasaran Si Wati itu pria atau perempuan. Lihat kolom sebelahnya lagi , kolom ke-3
Modifikasi rumus untuk mendapat data jenis kelamin yang terletak pada kolom ke-3 dari tabel
=VLOOKUP(10 ,A7:D22 ,3 ,0)
Anda Tersenyum : Oh si Wati itu wanita toh
Excel menginformasikan anda: Nomor urut 10 , kolom ke-3 yakni perempuan
Anda masih ingin tau : Si Wati itu orang mana ya? Sambil menyaksikan kolom sebelahnya lagi , kolom ke-4
Modifikasi rumus untuk mendapat data Alamat yang terletak pada kolom ke-4 dari tabel
=VLOOKUP(10 ,A7:D22 ,4 ,0)
Anda tersenyum lebar sambil berfikir dan membayangkan dalam peta. Cirebon itu dimana ya
Excel menginformasikan anda: Nomor urut 10 , kolom ke-4 yakni Cirebon
Selanjutnya: Terserah anda 🙂
Selanjutnya : Terserah anda juga.:-)

Karena kita ingin mencari data secara cepat pada banyak sekali nomor urut , maka angka 10 itu terletak dalam suatu cell. Misal cell C1 ,  maka rumusnya menjadi selaku berikut:
Nama ,  =VLOOKUP(C1 ,A7:D22 ,2 ,0)

Jenis Kelamin ,  =VLOOKUP(C1 ,A7:D22 ,3 ,0)

Alamat , =VLOOKUP(C1 ,A7:D22 ,4 ,0)
Cobalah:  ganti angka nomor urut di sel C1 , maka kita akan dapati data terkait nomor urut tersebut secara otomatis. 
Sampai disini semestinya cara kerja rumus VLOOKUP sudah sanggup difahami.

Rumus VLOOKUP Beda Sheet

Pada tumpuan diatas , kita memakai rumus vlookup  untuk menyaksikan seksama nilai tertentu pada tabel yang berada pada sheet yang serupa dengan rumus dibuat.

Bagaimana jikalau tabel terletak pada sheet yang beda , umpamanya pada sheet dengan nama “Data” , pada range A7:D22
Bagaimana rumus atau formulanya?
Tidak Sulit , cukup ketikan rumus dengan referensi tabel_dilihat pada sheet data
Nama =VLOOKUP(C1 ,DATA!A7:D22 ,2 ,0)
Jenis Kelamin =VLOOKUP(C1 ,DATA!A7:D22 ,2 ,0)
Alamat   =VLOOKUP(C1 ,DATA!A7:D22 ,2 ,0)
Perhatikan formula di atas , untuk parameter nilai_dilihat dan tabel_dilihat mengacu pada  referensi. Sebenarnya tidak perlu mengetikan eksklusif , cukup pilih referensi yang cocok memakai mouse.
Contoh cara mengetik rumus VLOOKUP antar sheet.
Ketik =VLOOKUP(  
Pilih referensi nilai_dilihat memakai mouse yakni sel C1
Ketik koma ( ,
Masuk atau Pilih sheet “DATA” 
Pilih tabel pada range A7:D22  di dalam sheet “DATA
Ketik koma ( ,
ketik parameter nomor_kolom , misal 2 (kolom ke-2)
Ketik koma ( ,
Ketik parameter lingkup_lihat , umpamanya angka atau False
Ketik tutup kurung
Oh iya … Saya lupa ,  anda bersamaan sudah paham untuk hal remeh cara mengetik rumus menyerupai ini. Tapi tak apalah , mudah-mudahan postingan ini agak panjang sikit. 🙂

Rumus VLOOKUP antar Workbook Atau File Excel Yang Berbeda

Bagaimana jikalau tabel data terletak di workbook atau file excel lainnya? Bagaimana rumus atau formula-nya?
Untuk yang ini juga tidak terlampau sulit. Yakin ! terlebih kalau sudah sudah biasa dengan excel dan sering memakai formula. Hampir sama dengan pengerjaan rumus VLOOKUP antar sheet.
Cukup ketikan =VLOOKUP( , kemudian pilih referensi nya memakai mouse , untuk parameter nilai_dilihat dan tabel_lihat.
Misalnya tabel penerima terletak dalam suatu file excel berjulukan  peserta.xlsx.  Maka rumus vlookup menjadi:
Nama  =VLOOKUP(C1 ,[peserta.xlsx]DATA!$A$7:$D$22 ,2 ,0)
Jenis Kelamin =VLOOKUP(C1 ,[peserta.xlsx]DATA!$A$7:$D$22 ,3 ,0)
Alamat =VLOOKUP(C1 ,[peserta.xlsx]DATA!$A$7:$D$22 ,4 ,0)
Perhatikan penggunaan tanda $. Biasanya jikalau kita mengetikan formula dengan referensi dari workbook atau file lain maka otomatis referensi  menggunakan atribut $ atau absolute.  Artinya range referensi dikunci dan akan tetap walaupun rumus di copy ke sel lain.

Referensi Nama Range Cell dalam Rumus VLOOKUP

Cara lain yang lebih simple dan lebih gampang dibaca (readable) merupakan penggunakan nama range selaku referensi. Tata cara penamaan range cell sudah dibahas dalam postingan yang lain , silahkan dibaca Memberikan Nama Cell dan Range Menggunakan Name Box
Atau kalau anda tidak mau repot membuka postingan lain , ikuti saja langkah berikut:
Membuat nama range cell yakni dengan cara memilih range cell (misal tabel) kemudian ketikan nama range pada name box yang umumnya terletak pada kiri atas lembar kerja excel (disebelah kiri tanda fx). Misalnya nama range tabel data penerima kita namakan dataPeserta

Lalu apa kaitan nama range sel dengan rumus vlookup?
Menggunakan nama sel atau nama range dalam rumus vlookup memicu rumus lebih readable , atau lebih gampang dibaca dan dipahami alurnya.
Penggunaan nama range cell juga memicu referensi yang sama. Tidak peduli lokasi tabel baik dalam sheet yang serupa atau tidak , tetapi masih dalam satu file.
Misalnya: 
Dengan menamai tabel data penerima dengan nama dataPeserta , maka rumus vlookup dalam satu sheet maupun antar sheet menjadi sama yaitu:
Nama  =VLOOKUP(C1 ,dataPeserta ,2 ,0)
Jenis kelamin  =VLOOKUP(C1 ,dataPeserta ,3 ,0)
Alamat  =VLOOKUP(C1 ,dataPeserta ,4 ,0)
Secara analogi , sanggup diterangkan bahwa rumus VLOOKUP diatas yakni mencari data sesuai nilai pada C1. Pencarian ditangani pada kolom pertama dari tabel berjulukan dataPeserta. Selanjutnya mengembalikan nilai dari kolom ke 2 (nama) , 3 (Jenis Kelamin) dan 4 (Alamat) pada baris yang serupa dengan nilai ditemukan.

Rumus VLOOKUP untuk Klasifikasi Grade Nilai

Kebanyakan pengguna excel sudah familiar dengan cara melakukan pembagian terorganisir perihal data memakai fungsi IF. Namun kalau kita menciptakan rumus IF bertingkat , kita akan membutuhkan rumus IF yang makin panjang dengan makin banyaknya persyaratan yang digunakan.
Semakin panjang rumus , pastinya makin susah untuk mengikuti alur logikanya. Dalam kata lain , penggunaan rumus IF dengan persyaratan yang banyak memicu rumus tidak readable.
Contoh Kasus:

Dalam mengklasifikasikan grade nilai cobaan mahasiswa di suatu sekolah tinggi tinggi , diputuskan persyaratan selaku berikut:
Nilai Grade
< 20 E
20-29 E+
30-39 D
40-49 D+
50-59 C
60-69 C+
70-79 B
80-89 B+
90-100 A
Bagaimana cara merumuskan grade nilai cobaan mahasiswa tersebut?
Bagi anda yang maniak dengan fungsi IF mungkin akan eksklusif mengatakan: Wah ini mesti memakai rumus IF nich…!
Rumus IF vs VLOOKUP
Perhatikan bahwa untuk pembagian terorganisir perihal nilai menjadi 9 grade (E , E+ , D , D+ , C , C+ , B , B+ , A ) , kita membutuhkan fungsi IF bertingkat 8.
=IF(G4<20 ,"E" ,IF(G4<30 ,"E+" ,IF(G4<40 ,"D" ,IF(G4<50 ,"D+" ,IF(G4<60 ,"C" ,IF(G4<70 ,"C+" ,IF(G4<80 ,"B" ,IF(G4<90 ,"B+" ,"A"))))))))
Rumus di atas tidak sanggup dipakai pada excel 2003 alasannya yakni excel model tersebut cuma membolehkan fungsi IF bertingkat hingga level 7.

Bahkan , walaupun anda sudah memakai model excel 2007 , 2010 , 2013 atau 2016 , penggunaan rumus tersebut tetap saja menurut saya kurang praktis.

Kenapa?

Karena rumus terlalu panjang. Rumus ini juga kurang fleksibel , umpamanya jikalau ada pergeseran dari persyaratan grade , maka kita mesti melakukan edit kembali pada rumus IF.

Apa Solusinya?

Gunakan rumus VLOOKUP


Dalam hal ini memakai VLOOKUP TRUE , artinya nilai parameter range_lookup atau lingkup_lihat mesti bernilai TRUE atau 1.

Caranya:

  • Buat tabel bantu nilai dan grade , 
  • Kolom pertama berisi nilai minimum masing-masing grade dan mesti berurut dari terkecil ke tertinggi.
  • Kolom kedua berisi grade
  • Perhatikan range berwarna kuning (C3:D11) dalam gambaran berikut yakni referensi persyaratan grade nilai mahasiswa yang mesti dimasukan kedalam rumus
Rumus VLOOKUP dengan Lingkup Range TRUE

  • Perhatikan sel D15 pada ilustrasi.  Rumus pada sel tersebut adalah:

=VLOOKUP(C15 ,$C$3:$D$11 ,2)
Atau

=VLOOKUP(C15 ,$C$3:$D$11 ,2 ,1)
Atau

=VLOOKUP(C15 ,$C$3:$D$11 ,2 ,TRUE)
Terbukti..! Rumus VLOOKUP sungguh jauh lebih pendek dibandingkan memakai rumus IF.  
Kita juga sanggup memberi nama pada referensi tabel persyaratan nilai grade mahasiswa sehingga rumus menjadi lebih readable. Misalnya tabel persyaratan kita beri nama kriteriaGrade , maka rumus tersebut menjadi:
=VLOOKUP(C15 ,kriteriaGrade ,2)

atau

=VLOOKUP(C15 ,kriteriaGrade ,2 ,1)

atau
=VLOOKUP(C15 ,kriteriaGrade ,2 ,TRUE)
Anda amati bahwa rumus VLOOKUP ini sungguh simple. Rumus inipun tidak akan bertambah panjang dengan bertambahnya persyaratan yang digunakan. Hal ini alasannya yakni persyaratan terletak dalam tabel lain yang dijadikan referensi tabel_dilihat. Hal ini sungguh berlainan dengan rumus IF yang mau bertambah panjang dengan bertambahnya kriteria.

Namun pada jadinya terserah anda untuk memakai rumus yang mana. Jika lebih tenteram dengan rumus IF , silahkan saja dengan resiko mesti menulis formula yang panjang tentunya.

Kalau saya sich , lebih senang yang simple dan fleksible. Maka saya memutuskan VLOOKUP.

Bisakah Rumus VLOOKUP melakukan pekerjaan dari Kanan Ke Kiri.

Pertanyaan ini sering timbul diantara pengguna excel dan acapkali dilontarkan dalam forum-forum diskusi. Para pakar excel dalam grup umumnya eksklusif menyarankan untuk memakai fungsi INDEX MATCH selaku alternative pengganti VLOOKUP.
Rumus INDEX MATCH memang banyak memiliki keistimewaan dibanding VLOOKUP , Namun , alasannya yakni kita sedang membahas VLOOKUP , maka penyelesaian yang mau dipersiapkan yakni tetap memakai rumus VLOOKUP.

Lalu bagaimana caranya? alasannya yakni VLOOKUP cuma sanggup melakukan pekerjaan mulai dari kolom pertama (kolom paling kiri) suatu tabel untuk mencari suatu nilai.

Tenang…
Ternyata nomor urut kolom dalam suatu tabel sanggup kita manipulasi.
Caranya?  Gunakan fungsi CHOOSE
Contoh kasus:
Dalam data mahasiswa menyerupai tumpuan gambaran sebelumnya dari kolom pertama hingga kolom ke-4 yakni NIM , Nama Mahasiswa , Nilai , Grade.
Seandainya kita mengenali nama mahasiswa tetapi belum tahu NIM Nya. Bagai mana cara mendapat NIM dengan rumus? padahal kolom NIM berada disebelah kiri Kolom Nama.
Disinilah kita mesti mengutus VLOOKUP agar sanggup melakukan pekerjaan dari kanan ke kiri.
Cara VLOOKUP Dari Kanan Ke Kiri
Perhatikan utamanya rumus VLOOKUP untuk mendapat Nomor Induk Mahasiswa (NIM) yaitu:
=VLOOKUP(B29 ,CHOOSE({1 ,2} ,B15:B25 ,A15:A25) ,2 ,0)
Data yang sudah dikenali yakni nama mahasiswa terletak kolom kedua pada tabel. Namun dengan memakai pemberian fungsi CHOOSE , kita sanggup memicu kolom tersebut seperti selaku kolom pertama.
Perhatikan tumpuan rumus yang dengan font warna biru dan merah. Fungsi CHOOSE mendefinisikan kolom ke-1 yakni range B15:B25 yang berisi nama mahasiswa. Sedangkan kolom ke-2 yakni range A15:A25 yang berisi data NIM 

Penting dalam penggunaan fungsi CHOOSE

Perhatikan angka dalam tanda kurung kurawal {} yakni 1 dan 2 yang dipisahkan tanda koma ( , ). Jika seting komputer anda memakai tanda koma selaku pemisah desimal , pastinya rumus tersebut akan menciptakan error. 
Pada permasalahan demikian maka tanda koma dalam kurung kurawal mesti diganti tanda back slash ( \ ) dan koma pemisah parameter rumus diganti dengan tanda semi colon ( ; )
Sehingga rumusnya menjadi selaku berikut:
=VLOOKUP(B29;CHOOSE({1\2};B15:B25;A15:A25);2;0)
Sampai pada tahap ini sanggup ditarik kesimpulan bahwa dalam fungsi VLOOKUP excel sanggup melakukan pekerjaan dari kanan ke kiri. Tentunya dengan pemberian fungsi CHOOSE.
……………….

Sebagai epilog postingan ini. Perlu ditekankan kembali bahwa VLOOKUP merupakan salah satu fungsi Excel yang sungguh diusulkan untuk dikuasai. Penulis sendiri sungguh mencicipi faedah dari rumus ini utamanya pada sewaktu melakukan pekerjaan dengan banyak data , apakah itu untuk pengelompokan data , mencari padanan atau data terkait , mencari nama lain dari suatu data dan sebagainya.

“Penggunaan Rumus VLOOKUP Terbukti Dapat Menghemat Waktu”

Untuk itu , jikalau pembaca gres mengenal rumus ini , penulis sungguh menyarankan untuk mempelajarainya lebih lanjut.

Demikian , mudah-mudahan bermanfaat
Salam

Referensi:
https://support.office.com/en-US/article/VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1

Baca Juga  Belajar Microsoft Excel: Cara Menggunakan Rumus Untuk Data Validation
Need Help? Chat with us