Vlookup Excel- Rumus Jitu Yang Wajib Dikuasai

Diakui atau tidak, masih banyak pengguna excel yang belum memahami atau bahkan belum tau apa itu rumus VLOOKUP.

 Padahal fungsi excel yang satu ini sangat penting dan sangat mempunyai kegunaan terutama jikalau kita bekerja 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 artikel tutorial VLOOKUP Excell ini.

Nah ini kira-kira apa yang akan kita bahas ihwal 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 teladan masalah dimana fungsi VLOOKUP diperlukan.

Misalnya: ada data nama penerima sebuah aktivitas dan disimpan dalam sebuah tabel terdiri atas 4 kolom yaitu Nomor, Nama, Jenis Kelamin dan Alamat.

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

Bagaimana caranya supaya untuk memperoleh data dimaksud, secara cepat memakai rumus.

Jawabnya : ya pakelah rumus VLOOKUP, bukan diPELUK yach ...:-)

Apa Rumus VLOOKUP itu ?

VLOOKUP merupakan kependekan dari Vertikal Lookup, atau secara harfiah lookup sanggup diartikan sebagai melihat dengan seksama. Makara fungsi VLOOKUP dipakai untuk melihat dengan seksama tabel secara vertikal (dari atas ke-bawah). Adapun kegunaannya yaitu untuk mendapat nilai dari kolom lain yang terkait dengan data yang sudah diketahui 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 yaitu data berupa angka maupun text yang akan dicari
• Tabel_dilihat yaitu tabel melihat nilai_dilihat dimana posisinya pada kolom pertama (paling kiri) dari tabel tersebut
• Nomor_kolom yaitu nomor urut kolom dalam tabel_dilihat untuk mendapat data tertentu yang terkait dengan nilai_dilihat
• Lingkup_lihat yaitu cara pencarian apakah menginginkan data yang persis sama atau pendekatan. Parameter ini bersifat opsional tetapi sangat penting. Nilai 0 atau FALSE jikalau menginginkan hasil yang persis sama atau nilai 1 atau TRUE jikalau menginginkan 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 yaitu itu parameter ini wajib diisi meskipun bersifat opsional.

Kembali ke teladan kasus:

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

Berikut gambaran jawabannya:

Belum Faham ?

Kira-kira beginilah penjelasannya dengan analogi:

Lihat Seksama Dengan Mata Kepala		Lihat Seksama Dengan Rumus VLOOKUP
Kita ingin melihat Nama Peserta nomor 10		Kita ingin melihat Nama Peserta nomor 10 mengunakan tunjangan rumus excel VLOOKUP
Lihat dengan seksama dari atas ke bawah pada kolom Nomor hingga dijumpai 10		Rumus mengecek satu persatu dari atas kebawah kolom nomor (paling kiri) tabel A7:D22 hingga dijumpai 10
Setelah nomor 10 dijumpai, Mata kita bergeser ke sebelah kanannya, pada kolom berikutnya yaitu kolom ke-2 yang berisi Nama		Begitu 10 dijumpai, excel mengecek pada kolom urutan ke-2 sesuai rumus  =VLOOKUP(10,A7:D22,2,0)
Berteriaklah : Ya Aku Tahu Kau Nomor 10, Namamu Wati kan?		Excel memberitahu anda: nomor urut 10 kolom ke-2 yaitu 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 memberitahu anda: Nomor urut 10 , kolom ke-3 yaitu perempuan
Anda masih ingin tau : Si Wati itu orang mana ya? Sambil melihat 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 memberitahu anda: Nomor urut 10 , kolom ke-4 yaitu 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 sebuah cell. Misal cell C1,  maka rumusnya menjadi sebagai 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 seharusnya cara kerja rumus VLOOKUP sudah sanggup difahami.

Rumus VLOOKUP Beda Sheet

Pada teladan diatas, kita memakai rumus vlookup  untuk melihat seksama nilai tertentu pada tabel yang berada pada sheet yang sama dengan rumus dibuat.

Bagaimana jikalau tabel terletak pada sheet yang beda, contohnya pada sheet dengan nama “Data”, pada range A7:D22

Bagaimana rumus atau formulanya?

Tidak Sulit, cukup ketikan rumus dengan rujukan 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 langsung, cukup pilih rujukan yang sesuai memakai mouse.

Contoh cara mengetik rumus VLOOKUP antar sheet.

• Ketik =VLOOKUP(  

• Pilih rujukan nilai_dilihat memakai mouse yaitu 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, contohnya angka 0 atau False

• Ketik tutup kurung

Oh iya ... Saya lupa,  anda sesungguhnya sudah paham untuk hal remeh cara mengetik rumus menyerupai ini. Tapi tak apalah, supaya artikel 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 terlalu sulit. Yakin ! apalagi kalau sudah terbiasa dengan excel dan sering memakai formula. Hampir sama dengan pembuatan rumus VLOOKUP antar sheet.

Cukup ketikan =VLOOKUP( , kemudian pilih rujukan nya memakai mouse, untuk parameter nilai_dilihat dan tabel_lihat.

Misalnya tabel penerima terletak dalam sebuah 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 rujukan dari workbook atau file lain maka otomatis rujukan  menggunakan atribut $ atau absolute.  Artinya range rujukan dikunci dan akan tetap meskipun rumus di copy ke sel lain.

Referensi Nama Range Cell dalam Rumus VLOOKUP

Cara lain yang lebih simple dan lebih gampang dibaca (readable) ialah penggunakan nama range sebagai referensi. Tata cara penamaan range cell sudah dibahas dalam artikel lainnya, silahkan dibaca Memberikan Nama Cell dan Range Menggunakan Name Box

Atau kalau anda tidak mau repot membuka artikel lain, ikuti saja langkah berikut:

Membuat nama range cell yaitu dengan cara menyeleksi range cell (misal tabel) kemudian ketikan nama range pada name box yang biasanya 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 mengakibatkan rumus lebih readable, atau lebih gampang dibaca dan dimengerti alurnya.

Penggunaan nama range cell juga mengakibatkan rujukan yang sama. Tidak peduli lokasi tabel baik dalam sheet yang sama 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 dijelaskan bahwa rumus VLOOKUP diatas yaitu mencari data sesuai nilai pada C1. Pencarian dilakukan pada kolom pertama dari tabel berjulukan dataPeserta. Selanjutnya mengembalikan nilai dari kolom ke 2 (nama), 3 (Jenis Kelamin) dan 4 (Alamat) pada baris yang sama dengan nilai ditemukan.

Rumus VLOOKUP untuk Klasifikasi Grade Nilai

Kebanyakan pengguna excel sudah familiar dengan cara melaksanakan pembagian terstruktur mengenai data memakai fungsi IF. Namun kalau kita menciptakan rumus IF bertingkat, kita akan memerlukan rumus IF yang semakin panjang dengan semakin banyaknya kriteria yang digunakan.

Semakin panjang rumus, tentunya semakin sulit untuk mengikuti alur logikanya. Dalam kata lain, penggunaan rumus IF dengan kriteria yang banyak mengakibatkan rumus tidak readable.

Contoh Kasus:

Dalam mengklasifikasikan grade nilai ujian mahasiswa di sebuah perguruan tinggi tinggi, ditentukan kriteria sebagai 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 ujian mahasiswa tersebut?

Bagi anda yang maniak dengan fungsi IF mungkin akan eksklusif mengatakan: Wah ini harus memakai rumus IF nich...!

Perhatikan bahwa untuk pembagian terstruktur mengenai nilai menjadi 9 grade (E , E+ , D , D+ , C , C+ , B , B+ , A ), kita memerlukan 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 yaitu excel versi tersebut hanya mengizinkan fungsi IF bertingkat hingga level 7.

Bahkan, meskipun anda sudah memakai versi excel 2007, 2010, 2013 atau 2016, penggunaan rumus tersebut tetap saja berdasarkan saya kurang praktis.

Kenapa?

Karena rumus terlalu panjang. Rumus ini juga kurang fleksibel, contohnya jikalau ada perubahan dari kriteria grade, maka kita harus melaksanakan edit kembali pada rumus IF.

Apa Solusinya?

Gunakan rumus VLOOKUP

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

Caranya:

• Buat tabel bantu nilai dan grade, 
• Kolom pertama berisi nilai minimum masing-masing grade dan harus berurut dari terkecil ke tertinggi.
• Kolom kedua berisi grade
• Perhatikan range berwarna kuning (C3:D11) dalam gambaran berikut yaitu rujukan kriteria grade nilai mahasiswa yang harus dimasukan kedalam rumus

• 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 sangat jauh lebih pendek dibandingkan memakai rumus IF.  

Kita juga sanggup memberi nama pada rujukan tabel kriteria nilai grade mahasiswa sehingga rumus menjadi lebih readable. Misalnya tabel kriteria 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 perhatikan bahwa rumus VLOOKUP ini sangat simple. Rumus inipun tidak akan bertambah panjang dengan bertambahnya kriteria yang digunakan. Hal ini alasannya yaitu kriteria terletak dalam tabel lain yang dijadikan rujukan tabel_dilihat. Hal ini sangat berbeda dengan rumus IF yang akan bertambah panjang dengan bertambahnya kriteria.

Namun pada akibatnya terserah anda untuk memakai rumus yang mana. Jika lebih nyaman dengan rumus IF, silahkan saja dengan resiko harus menulis formula yang panjang tentunya.

Kalau saya sich, lebih suka yang simple dan fleksible. Maka saya menentukan VLOOKUP.

Bisakah Rumus VLOOKUP bekerja dari Kanan Ke Kiri.

Pertanyaan ini sering muncul diantara pengguna excel dan acapkali dilontarkan dalam forum-forum diskusi. Para pakar excel dalam grup biasanya eksklusif menyarankan untuk memakai fungsi INDEX MATCH sebagai alternative pengganti VLOOKUP.

Rumus INDEX MATCH memang banyak mempunyai keunggulan dibanding VLOOKUP, Namun, alasannya yaitu kita sedang membahas VLOOKUP, maka solusi yang akan ditawarkan yaitu tetap memakai rumus VLOOKUP.

Lalu bagaimana caranya? alasannya yaitu VLOOKUP hanya sanggup bekerja mulai dari kolom pertama (kolom paling kiri) sebuah tabel untuk mencari sebuah nilai.

Tenang...

Ternyata nomor urut kolom dalam sebuah tabel sanggup kita manipulasi.

Caranya?  Gunakan fungsi CHOOSE

Contoh kasus:

Dalam data mahasiswa menyerupai teladan gambaran sebelumnya dari kolom pertama hingga kolom ke-4 yaitu NIM, Nama Mahasiswa, Nilai, Grade.

Seandainya kita mengetahui nama mahasiswa namun belum tahu NIM Nya. Bagai mana cara mendapat NIM dengan rumus? padahal kolom NIM berada disebelah kiri Kolom Nama.

Disinilah kita harus memerintahkan VLOOKUP supaya sanggup bekerja dari kanan ke kiri.

Perhatikan terutama rumus VLOOKUP untuk mendapat Nomor Induk Mahasiswa (NIM) yaitu:

=VLOOKUP(B29,CHOOSE({1,2},B15:B25,A15:A25),2,0)

Data yang sudah diketahui yaitu nama mahasiswa terletak kolom kedua pada tabel. Namun dengan memakai tunjangan fungsi CHOOSE, kita sanggup mengakibatkan kolom tersebut seolah-olah sebagai kolom pertama.

Perhatikan teladan rumus yang dengan font warna biru dan merah. Fungsi CHOOSE mendefinisikan kolom ke-1 yaitu range B15:B25 yang berisi nama mahasiswa. Sedangkan kolom ke-2 yaitu range A15:A25 yang berisi data NIM 

Penting dalam penggunaan fungsi CHOOSE

Perhatikan angka dalam tanda kurung kurawal {} yaitu 1 dan 2 yang dipisahkan tanda koma ( , ). Jika seting komputer anda memakai tanda koma sebagai pemisah desimal, tentunya rumus tersebut akan menghasilkan error. 

Pada masalah demikian maka tanda koma dalam kurung kurawal harus diganti tanda back slash ( \ ) dan koma pemisah parameter rumus diganti dengan tanda semi colon ( ; )

Sehingga rumusnya menjadi sebagai berikut:

=VLOOKUP(B29;CHOOSE({1\2};B15:B25;A15:A25);2;0)

Sampai pada tahap ini sanggup disimpulkan bahwa dalam fungsi VLOOKUP excel sanggup bekerja dari kanan ke kiri. Tentunya dengan tunjangan fungsi CHOOSE.

...................

Sebagai epilog artikel ini. Perlu ditekankan kembali bahwa VLOOKUP merupakan salah satu fungsi Excel yang sangat disarankan untuk dikuasai. Penulis sendiri sangat mencicipi manfaat dari rumus ini terutama pada ketika bekerja dengan banyak data, apakah itu untuk pengelompokan data, mencari padanan atau data terkait, mencari nama lain dari sebuah data dan sebagainya.

"Penggunaan Rumus VLOOKUP Terbukti Dapat Menghemat Waktu"

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

Demikian, semoga bermanfaat
Salam

Artikel terkait
Lebih Lanjut Memahami Rumus VLOOKUP Dengan Studi Kasus
Rumus Excel Tidak Berfungsi ? Inilah Penyebabnya

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