Rumus VLOOKUP Excel, Contoh & Cara Menggunakan Fungsi Vlookup Excel Lengkap

 

Belajar Rumus Vlookup Excel yuk! Pada tutorial excel kali ini, Kelas Excel akan mengajak anda untuk belajar cara menggunakan fungsi Vlookup pada microsoft excel.

Fungsi vlookup merupakan salah satu fungsi atau rumus dasar excel yang termasuk dalam kategori lookup & reference yang akan sering Anda butuhkan saat menggunakan microsoft excel.

Rumus excel VLOOKUP merupakan rumus excel yang berfungsi untuk mengambil suatu nilai pada sebuah tabel berdasarkan kata kunci tertentu.

Bagaimana cara menggunakan rumus vlookup di excel? berikut tutorial lengkapnya.

 

Pengertian Rumus VLOOKUP Excel

 

Sebelum mempelajari cara menggunakan vlookup excel, kita perlu tahu apa itu fungsi vlookup pada microsoft excel.

Fungsi atau Rumus VLOOKUP Excel adalah salah satu fungsi pada Microsoft Excel yang digunakan untuk mencari data pada kolom pertama sebuah tabel data, kemudian mengambil atau menghasilkan nilai dari sel mana pun di baris yang sama pada tabel data tersebut.

Dengan menggunakan rumus Vlookup pada Microsoft Excel ini kita dapat mengisi data pada sebuah tabel berdasarkan data pada tabel referensi lain dengan acuan sebuah nilai kunci yang spesifik.

Salah satu contoh penggunaan fungsi Vlookup adalah untuk Konversi Rentang Nilai Angka ke Huruf Pada Excel

Awalan huruf "V" didepan kata Vlookup merupakan singkatan dari kata Vertical. Istilah vertikal ini merujuk pada bentuk tabel referensi yang digunakan, dimana header atau judul tabel terletak dibagian atas dan field datanya tersusun kebawah secara vertikal.

Fungsi rumus Vlookup Excel ini tersedia disemua versi Ms. Excel, Baik versi Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP maupun Excel 2000.

 

Cara Menggunakan Fungsi VLOOKUP Excel

 

Lalu bagaimana cara menggunakan vlookup pada excel ini?

Cara menggunakan rumus vlookup excel adalah sebagai berikut:

Rumus Excel

VLOOKUP(NilaiYangDiCari; TabelReferensi; NomorKolom; [RangeLookup])

Fungsi Vlookup excel memiliki empat argumen:

·         NilaiYangDiCari : Merupakan nilai yang akan kita cari pada kolom pertama sebuah tabel atau kolom paling kanan dari TabelReferensi. Argument ini dapat berupa angka, teks, tanggal maupun nilai tertentu baik ditulis langsung maupun sebuah referensi sel. Bisa juga berupa nilai yang dihasilkan oleh rumus excel lainnya.

·         TabelReferensi : Sebuah tabel referensi data yang terdiri dari 2 kolom atau lebih, dimana NilaiYangDicari berada pada kolom pertama tabel data ini.

·         NomorKolom : Merupakan Nomor kolom dari argumen TabelReferensi yang mengandung nilai data dan akan kita ambil nilai yang sebaris dengan posisi NilaiYangDicari pada kolom pertama.

·         RangeLookup : Diisi dengan nilai Boolean TRUE atau FALSE. Nilai logika True/false ini menujukkan tipe pencarian. Nilai logika pada rumus Vlookup digunakan sebagai acuan untuk menetapkan apakah kita ingin VLOOKUP menemukan hasil yang sama persis atau cukup menemukan nilai mendekati.

1.     TRUE: Vlookup akan menemukan NilaiYangDiCari Pada TabelReferensi pencarian yang sama persis atau mendekati. Jika Nilai yang sama persis tidak ditemukan maka nilai terbesar berikutnya yang kurang dari NilaiYangDiCari yang akan dihasilkan.

2.     FALSE: Vlookup akan menemukan NilaiYangDiCari Pada TabelReferensi pencarian yang sama persis saja. Jika tidak ditemukan maka Vlookup akan menghasilkan pesan error #NA!

Selain menggunakan Vlookup Excel, untuk hasil yang sama perlu juga anda pertimbangkan untuk menggunakan rumus excel Index-Match yang bisa anda gunakan sebagai rumus alternatif pengganti VLOOKUP Excel.

Untuk lebih jelasnya tentang cara menggunakan fungsi Vlookup excel ini silahkan perhatikan contoh rumus vlookup berikut:

Contoh Rumus VLOOKUP Excel

Perhatikan contoh sederhana Rumus Vlookup excel berikut ini:

Rumus Excel

 =VLOOKUP(C9;A2:E7;2;FALSE)

 

Pada contoh pertama (warna kuning) fungsi Vlookup mencari nilai dari cel C9 (3) pada kolom Nomor. Angka 3 ditemukan pada baris ke-4 dari tabel data A2:E7. dan hasilnya adalah nilai sel yang sebaris pada kolom ke 2.

Untuk rumus berikutnya hanya berbeda nomor kolom yang diambil , yakni kolom nomor 3,4 dan 5.

Perhatikan contoh kedua (warna biru) diatas. Apabila RangeLookup diatur "False" dan data yang dicari tidak ditemukan maka fungsi Vlookup excel akan menghasilkan nilai error #NA.

 

Rumus Excel

 =VLOOKUP(C15;A2:E7;2)

 

Rumus Vlookup diatas mencari nilai angka 7 (Sel C15) pada kolom Nomor. Karena menggunakan RangeLookup "False" dan data tidak ditemukan maka Rumus Vlookup menghasilkan pesan kesalahan #NA

Jika RangeLookup adalah "TRUE" atau dikosongkan, nilai-nilai di kolom pertama TabelReferensi harus ditempatkan dalam urutan naik, jika tidak, VLOOKUP kemunginan tidak menghasilkan nilai yang benar. Perhatikan contoh berikut.

 Untuk rumus Vlookup excel pada contoh diatas menggunakan TRUE sebagai parameter Range Lookup. Angka 6 tidak ditemukan pada kolom pertama. Sehingga, Vlookup mengambil nilai yg mendekati nilai yg dicari. Hal sama berlaku jika range Lookup dikosongkan atau tidak diisi.

 

Hal lain yang perlu diperhatikan saat menggunakan rumus Vlookup adalah jika nilai yang kita berikan untuk argumen NilaiYangDiCari lebih kecil dari nilai terkecil di kolom pertama argumen TabelReferensi, Rumus VLOOKUP akan menghasilkan nilai kesalahan #N/A. Lihat contoh berikut:

 

Pada fungsi Vlookup excel, argumen NilaiYangDiCari dapat berupa teks, angka, atau nilai logika. Jika bernilai Teks maka huruf besar dan huruf kecil akan dianggap sama.

 

Perlu diperhatikan juga bahwa argument nomor kolom harus bernilai lebih dari 0 (nol). Jika Argumen NomorKolom kurang dari 1 (0 atau negatif), rumus VLOOKUP akan menhasilkan nilai kesalahan #VALUE!. Dan apabila NomorKolom lebih besar dari jumlah kolom dalam TabelReferensi, VLOOKUP mengembalikan nilai kesalahan #REF!. Perhatikan contoh Vlookup berikut:

 

Saat menggunakan rumus excel Vlookup dan mencari nilai teks di kolom pertama TabelReferensi, pastikan bahwa data di kolom pertama TabelReferensi tidak berisi spasi awal, spasi akhir, penggunaan tanda kutip lurus ( ' atau " ) dan lengkung ( ‘ atau “) secara inkonsisten, atau karakter noncetak. Dalam kasus ini, VLOOKUP kemungkinan akan menghasilkan nilai yang tidak tepat atau tidak diharapkan.

 

Ketika mencari nilai angka atau tanggal, pastikan bahwa data di kolom pertama TabelReferensi tidak disimpan sebagai nilai teks. Dalam kasus ini, cara Vlookup excel mungkin akan menghasilkan nilai yang salah atau tidak diharapkan juga.

 

Saat menerapkan rumus excel Vlookup, kita juga dapat menggunakan karakter wildcard pada nilai pencarian fungsi Vlookup jika RangeLookup FALSE dan NilaiYangDiCari adalah teks.

 

Karakter wildcard tanda tanya (?) cocok dengan karakter tunggal apa pun, sedangkan tanda bintang (*) cocok dengan urutan karakter apa pun. Perhatikan contoh vlookup berikut:

 

Download File Latihan Fungsi VLOOKUP

File latihan Vlookup Excel untuk pembahasan kali ini bisa anda download melalaui link di bawah ini:

 

Rumus VLOOKUP Tingkat Lanjut

 

Sebagai catatan terakhir bahwa fungsi excel vlookup ini hanya mengambil data pertama yang ditemukan, sehingga jika data yang dicari ternyata lebih dari satu maka untuk megambil data ganda tersebut perlu menggunakan cara lain.

 

Catatan lain bahwa Fungsi Vlookup hanya berlaku jika tabel kita susun secara Vertikal. Lalu bagaimana dengan tabel yang disusun secara Horisontal? ini akan kita bahas pada artikel berikutnya. Pada artikel berikutnya kita akan belajar tentang HLOOKUP.

 

Untuk cara penggunaan fungsi Vlookup tingkat lanjut bisa anda pelajari pada beberapa halaman tutorial berikut:

 

Untuk cara penggunaan fungsi Vlookup tingkat lanjut bisa anda pelajari pada beberapa halaman tutorial berikut:

 

1.     Fungsi Vlookup Excel: Contoh Dan Cara Menggunakannya

2.     Rumus Vlookup Sebagian Teks (Vlookup, Left, Mid, Right)

3.     Rumus Vlookup Ke Arah Kiri

4.     Rumus Vlookup Dengan 2 Kriteria/Kondisi

5.     Rumus Vlookup Dengan 2 Tabel Data

6.     Rumus Vlookup Dengan Banyak Kriteria

7.     Rumus VLookUp Dengan 2 Hasil Pencarian atau Lebih

8.     Index-Match : Rumus Alternatif Pengganti VLookup

9.     Cara Konversi Rentang Nilai Angka ke Huruf Dengan Vlookup Excel

10.Rumus Terbilang Manual Dengan Fungsi Vlookup Excel

 

Selamat berlatih. Jangan lupa untuk share dan meningalkan komentar. Semoga penjelasan cara menggunakan rumus vlookup di atas tidak malah membuat anda semakin bingung. Kalau memang demikian silahkan sampaikan kebingungan anda pada kolom komentar yang tersedia.

 

 

 

 

 

 

 

Read More

Rumus Vlookup Sebagian Kata atau Teks (VLOOKUP - LEFT, MID, RIGHT)

 

Bagaimanakah rumus excel Vlookup sebagian kata atau rumus Vlookup sebagian isi dari sebuah sel excel?

Bagi setiap pengguna excel, khususnya yang aktif membaca tutorial excel di blog Kelas Excel ini tentunya sudah tahu bahwa fungsi Vlookup pada excel digunakan untuk mencari nilai teks atau angka pada sebuah tabel vertikal kemudian menampilkan data yang sebaris dari hasil pencarian nilai tersebut.

Sedangkan untuk mengambil sebagian teks, excel telah menyediakan fungsi LEFT, MID dan RIGHT.

Sehingga untuk melakukan vlookup sebagian kata atau teks maka anda perlu menggabungkan fungsi Vlookup pada excel dengan Fungsi Left, Mid atau Right pada sebuah rumus excel.

Misalnya seperti contoh excel berikut:

Pada kolom B anda mempunyai beberapa data Kode Tiket. Kode tersebut disusun dengan ketentuan:

·         2 Digit pertama menunjukkan jenis Maskapai penerbangan

·         1 Digit angka berikutnya menunjukkan Kelas Penerbangan, dan

·         3 Digit terakhir dari kode tersebut menunjukkan Lokasi Tujuan

Pada Kolom-kolom yang lain anda juga sudah mempunyai beberapa tabel yang menunjukkan arti dari kode-kode tersebut.

Selanjutnya bagaimanakah rumus Vlookup yang digunakan untuk mengisi sel-sel excel yang masih kosong pada kolom C, D dan E?

Rumus VLOOKUP, LEFT, MID, dan RIGHT

Sebelum membahas lebih lanjut mengenai rumus kombinasi Vlookup dengan Rumus Left, Mid, dan Right untuk menjawab soal excel di atas, saya kira perlu untuk saya ingatkan kembali bagaimana cara menggunakan masing-masing fungsi tersebut.

Rumus VLOOKUP Excel

Fungsi atau Rumus VLOOKUP digunakan untk mencari data pada kolom pertama sebuah tabel, kemudian mengambil nilai dari sel mana pun di baris yang sama dengan hasil pencarian pada tabel data tersebut.

Cara Menggunakan Fungsi Vlookup pada rumus excel adalah sebagai berikut:

VLOOKUP(NilaiYangDiCari; TabelReferensi; NomorKolom; [RangeLookup])

Dimana argumen NilaiYangDicari merupakan nilai sel akan dicari pada TabelRefrensi sesuai dengan informasi NomorKolom pada tabel tersebut.

Sedangkan argumen RangeLookup menunjukkan tipe pencarian pada rumus Vlookup untuk menemukan hasil yang sama persis atau cukup menemukan nilai yang mendekati atau terdekat apabila NilaiYangDicari tidak ditemukan.

Rumus LEFT Excel

Fungsi atau Rumus Left merupakan salah satu Fungsi Excel yang digunakan untuk mengambil beberapa karakter teks dari arah kiri atau dari awal sebuah teks sesuai dengan informasi yang diberikan.

Cara Menggunakan Fungsi Left pada rumus excel adalah sebagai berikut:

=LEFT(Teks; [JumlahKarakter])

Argumen JumlahKarakter merupakan informasi banyaknya karakter yang akan diambil dari awal Teks.

Rumus MID Excel

Fungsi atau Rumus MID bisa digunakan untuk mengambil beberapa karakter di tengah teks. Fungsi MID Mirip dengan fungsi LEFT yang digunakan untuk mengambil sebagian kata dalam teks dari arah kiri ke kanan, namun posisi awal pengambilan karakter atau kata tersebut bisa kita tentukan sendiri.

Cara Menggunakan Fungsi Mid pada rumus excel adalah sebagai berikut:

=MID(Teks; AwalKarakter; JumlahKarakter)

Argumen AwalKarakter menunjukkan posisi awal pengambilan teks sedangkan JumlahKarakter merupakan informasi banyaknya karakter yang akan diambil dari Teks.

Rumus RIGHT Excel

Fungsi atau Rumus Right mirip dengan fungsi Left. Bedanya jika fungsi Left megambil karakter dari awal teks, Sedangkan fungsi Right mengambil sejumlah karakter dari akhir teks atau dari arah kanan ke kiri.

=RIGHT(Teks; [JumlahKarakter])

Argumen JumlahKarakter merupakan informasi banyaknya karakter yang akan diambil dari akhir Teks.

 

Menggabungkan Rumus Excel VLOOKUP, LEFT, MID, RIGHT

Setelah kembali mengingat bagaimana cara menggunakan fungsi VLookup, Left, Mid, dan Right berikutnya kita akan kembali ke topik utama tentang bagaimana menggunakan rumus gabungan atau kombinasi dari fungsi-fungsi di atas.

Sebelum itu perlu anda ketahui bahwa pada rumus-rumus dibawah ini, tabel-tabel referensi yang digunakan sudah saya diberi nama (Named Range) untuk mempermudah penggunaan pada rumus excel yang akan digunakan.

Tabel Nama Range

NAMA RANGE	REFERENSI
Tabel Maskapai	$G$2:$H$7
Tabel Class	$J$2:$K$5
Tabel Tujuan	$M$2:$N$9

Rumus Excel Vlookup-Left

Kolom C pada tabel Kode Tiket akan diisi dengan Nama Maskapai. Untuk mencari nama maskapai dari kode tersebut rumus VLookup yang dipakai adalah:

Advertisements

=VLOOKUP(Kode;TabelMaskapai;2;FALSE)

Tentunya jika diterapkan rumus diatas akan menghasilkan pesan error #NA, sebab TabelMaskapai hanya memuat 2 digit pertama dari kode tersebut.

Untuk mengambil 2 digit pertama kode tersebut gunakanlah fungsi Left dengan rumus excel sebagai berikut:

LEFT(Kode;2)

Teks Hasil dari fungsi Left tadi kemudian kita gunakan sebagai argumen NilaiYangDiCari pada fungsi VLookup, sehingga rumusnya menjadi:

=VLOOKUP(LEFT(Kode;2);TabelMaskapai;2;FALSE)

Jika rumus excel tersebut diterapkan pada tabel KODE TIKET, maka Pada sel C3 gabungan rumus Vlookup-Left nya adalah:

=VLOOKUP(LEFT(B3;2);TabelMaskapai;2;FALSE)

Selanjutnya anda tinggal melakukan copy paste rumus tersebut pada sel-sel lain dibawahnya. Jika anda tidak menggunakan Named Range, rumus pada sel C3 bisa juga diisi dengan:

=VLOOKUP(LEFT(B3;2);$G$2:$H$7;2;FALSE)

Rumus Excel Vlookup-Mid

Untuk Kolom D akan diisi dengan salah satu jenis Kelas Penerbangan yakni: Firts Class, Business Class atau Economy Class.

Untuk menentukan jenis Kelas dari kode tersebut rumus VLookup yang dipakai adalah:

VLOOKUP(Kode;TabelKelas;2;FALSE)

Pada Kode tersebut Kode Kelas merupakan 1 karakter setelah kode Maskapai atau 1 karakter dimulai pada posisi ke-3.

Untuk mengambil kode Kelas tersebut tentunya yang digunakan adalah fungsi MID.

MID(Kode;3;1)

Jika kedua rumus ini digabungkan maka menjadi:

VLOOKUP(MID(Kode;3;1);TabelKelas;2;FALSE)

Dan jika diterapkan pada Sel D3 maka rumus excelnya menjadi:

=VLOOKUP(MID(B3;3;1);TabelKelas;2;FALSE)

Loh, kog Error #NA?

Ya disinilah terkadang kita juga harus lebih jeli saat menyusun sebuah kode-kode tabel.

Pada tabel tersebut kode Kelas merupakan angka 1,2 dan 3. Sedangkan hasil dari fungsi MID termasuk juga fungsi LEFT dan RIGHT adalah sebuah teks. Sehingga hasil rumus MID tadi jika dicari pada tabel kelas menggunakan fungsi VLookup maka dinyatakan tidak ditemukan atau menghasilla nilai error #NA. Sebab parameter yang kita gunakan adalah pencarian persis(FALSE).

Supaya tidak muncul error #NA maka hasil fungsi MID perlu kita ubah dari nilai teks menjadi nilai numerik menggunakan fungsi VALUE sehingga rumus excelnya menjadi:

=VLOOKUP(VALUE(MID(B3;3;1));TabelKelas;2;FALSE)

atau bisa juga anda isi dengan rumus berikut:

=VLOOKUP(VALUE(MID(B3;3;1));$J$2:$K$5;2;FALSE)

Selanjutnya anda tinggal Copy paste rumus diatas pada sel-sel lain dibawahnya untuk mendapatkan Jenis kelas penerbangan untuk kode-kode lainnya.

Bagaimana? mudah bukan? Selanjutnya kita akan mengisi kolom E

Rumus Excel Vlookup-Right

Untuk kolom E atau kolom tujuan di dapatkan dari Vlookup 3 digit terakhir terkahir kode. Dengan mengetahui kriteria ini tentunya anda sudah bisa menyimpulkan bahwa rumus apa yang akan digunakan.

Jika anda menyimpulkan untuk menggunakan rumus gabungan fungsi Vlookup-Right, maka anda benar. Jadi pada kolom E3 rumus excelnya adalah:

=VLOOKUP(RIGHT(B3;3);TabelTujuan;2;FALSE)

Pada kolom E atau untuk mendapatkan Tujuan Penerbangan ini sebenarnya anda bisa juga menggunakan rumus kombinasi fungsi Vlookup-Mid.

Lho bisa ya?

Bisa, Jika anda lebih jeli lagi kode Tujuan bisa juga anda dapatkan dengan mengambil karakter kode dari posisi tengah teks. Yakni dimulai pada karater ke-4 sebanyak 3 karakter teks.

Apabila anda menggunakan cara ini, maka rumus excel pada Sel E3 bisa juga anda isi dengan rumus:

=VLOOKUP(MID(B3;4;3);TabelTujuan;2;FALSE)

Download File Latihan Rumus Gabungan VLookup Dengan Fungsi Left, Mid, Right Excel

File latihan untuk pembahasan kali ini bisa anda dapatkan melalui link download di bawah ini :

Apabila masih ada pertanyaan silahkan disampaikan pada kolom komentar yang tersedia dan jika anda rasa artikel tutorial ini bermanfaat jangan lupa untuk di share agar lebih banyak lagi yang akan ikut mendapatkan manfaatnya. Salam Kelas Excel.

Berikut beberapa tutorial ex

Read More