Bagaimana Menggunakan Function VLOOKUP Di Dalam Excel - Dengan Contoh Yang Berguna

Apakah VLOOKUP Itu Di Dalam Excel?

VLOOKUP, kependekan dari "vertical lookup", adalah sebuah formula di dalam Microsoft Excel untuk mencocokkan data dari dua daftar. Alih-alih melompat antara spreadsheet dan mengetikkan data yang cocok, kamu dapat menuliskan sebuah formula VLOOKUP untuk  mengotomatisasi proses.

Pada sisi kiri (dalam gambar di atas), kita memiliki informasi pergantian karyawan. Kita ingin menambahkan jabatan pekerjaan pada data pergantian. Dengan daftar terpisah antara karyawan dan jabatan pekerjaannya, kita dapat menuliskan formula VLOOKUP untuk menarik jabatan dari daftar yang dicari.

Sebuah VLOOKUP yang sukses memerlukan tiga hal:

• Key utama di dalam tiap daftar yang dapat kamu gunakan untuk mencocokkan data. Kedua daftar perlu berbagi paling tidak satu data yang sama (di dalam contoh VLOOKUP Excel di atas, ini adalah ID karyawan)
• Sebuah Lookup List, yang berisi "database", atau pada dasarnya informasi (daftar jabatan pekerjaan karyawan)
• data, yang ingin kamu tarik untuk dicocokkan (data pergantian)

Contoh Singkat Formula VLOOKUP Excel Beraksi

VLOOKUP adalah formula Microsoft Excel yang esensial untuk berkerja dengan banyak set data. Di dalam tutorial ini, saya akan mengajarimu bagaimana menguasai dan menggunakannya.

Dengan menggunakan contoh di atas, saya sekarang menuliskan sebuah formula VLOOKUP yang mencari ID karyawan dan memasukkan jabatan pekerjaan ke dalam data pergantian. Karena kedua sheet memiliki ID karyawan, Excel dapat mencari jabatan pekerjaan yang cocok. Bagian terbaik VLOOKUP adalah saya sekarang dapat menarik formula ke bawah dan itu akan mencari tiap jabatan pekerjaan unik.

Download spreadsheet Excel Gratis

Sebelum kita melanjutkan, pastikan untuk Mendownload Lampiran secara gratis untuk mengikuti tutorial ini. Itu adalah workbook spreadsheet contoh yang saya buat, dimana akan kita gunakan untuk mengikuti tutorial ini.

Tonton dan Pelajari: VLOOKUP

Untuk cara tercepat mempelajari dasar-dasar formula VLOOKUP, periksa screencast di bawah. Video berjalan melalui beberapa contoh formula VLOOKUP, dengan menggunakan workbook contoh.

Teruskan membaca untuk mengikuti instruksi tertulis, dan pelajari beberapa teknik tambahan yang tidak diliput di dalam screencast.

Bagaimana Menggunakan VLOOKUP di Dalam Excel: Petunjuk

Gunakan tab "Ingredient Orders" dan "Supplier List" untuk contoh VLOOKUP Excel ini.

Mari katakan bahwa kita mengelola sebuah restoran dan menempatkan order mingguan ke supplier. Chef telah memberikan daftar pesanan bahan, dan kita perlu memasukkan informasi tentang supplier.

Ada tiga bagian yang perlu kita tambahkan untuk tiap order:

• Nama Supplier
• Nomor Telefon Supplier
• Tanggal Pengiriman Supplier

Di dalam workbook ini, ada dua tab:

• Ingredient Orders - berisi daftar bahan yang diminta oleh chef.
• Supplier List - berisi informasi tentang supplier, seperti nama supplier dan nomor telefon.
  

Kolom yang sama antara kedua tabel tersebut adalah tab Ingredient. Mari gunakan itu untuk mencari tiap ketiga kolom dan menambahkannya ke daftar pesanan.

Langkah 1: Memulai Formula VLOOKUP Excel

Pd tab Ingredient Orders, mari klik cell kosong Supplier pertama, F5, dan tekan tanda sama dengan untuk memulai formula VLOOKUP. Kemudian, ketik "VLOOKUP(" untuk memulai formula.

 =VLOOKUP(

Ingatlah bahwa key primer kita—bagian data yang muncul pada kedua daftar—adalah ingredient, jadi kita akan menggunakan itu sebuah lookup. Klik pada cell B5, atau ketikkan itu ke dalam formula. Berikutnya, tambahkan sebuah koma setelah "B5" sehingga kita dapat memasukkan bagian berikutnya dari formula.

=VLOOKUP(B5,

Sekarang, kita perlu memberikan daftar lookup kepada formula. Dengan formula yang masih terbuka, klik pada tab Supplier List. Sekarang, klik pada cell A3, dan klik dan drag untuk menyoroti dan memilih A3 hingga G13, keseluruhan tabel lookup. Pastikan dan tekan tombol F4 pada keyboard untuk membuat formula sebagai absolute reference (lebih lanjut tentang ini nanti). Akhirnya, masukkan koma lainnya.

Setelah kamu memasukkan koma untuk cell lookup, ganti tab dan arahkan Excel ke daftar lookup. Klik dan drag antara cell A3 dan G3 untuk memilih data lookup. Pastikan dan tekan F4 pada keyboard selama tahap ini untuk membuat absolute reference, yang akan mengunci cell untuk digunakan sebagai lookup.

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,

Berikutnya, kita perlu memberi tahu Excel kolom mana untuk ditarik. Ingatlah bahwa item pertama kita untuk dimasukkan adalah nama Supplier, yang ada di dalam kolom kedua daftar lookup. Tambahkan angka 2 ke formula untuk menarik dari kolom kedua lookup, dan koma lainnya.

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,

Terakhir, kita akan menambahkan FALSE untuk sebuah lookup pasti, dan kemudian menutup tanda kurung:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,FALSE)

Kabar baiknya adalah kita tidak perlu menulis ulang formula yang sama ini berkali-kali—cukup dobel klik pada sudut kanan bawah cell F5 (kamu dapat melihat kotak hijau kecil pada sudut cell) untuk memperluas formula ke bawah (seperti yang ditunjukkan di atas).

Formula ini berkerja dengan sempurna! Oke, sekarang mari pindah ke menarik data untuk dua kolom lainnya: nomor telefon supplier dan hari pengiriman.

Langkah 2: Menarik Lebih Banyak Data Dengan VLOOKUP

Karena kita menggunakan absolute reference, pada dasarnya kita dapat menggunakan kembali formula yang sama dengan yang kita tuliskan dengan sedikit perubahan. Berikutnya mari tambahkan Nomor Telefon Supplier.

Kita akan memulai dengan menyalin dan menempelkan cell F5 (cell Supplier) ke cell G5. Saya biasanya hanya menggunakan shortcut keyboard Ctrl + C dan Ctrl + V untuk copy dan paste keseluruhan cell. Pada awalnya, ini tidak akan berkerja, dan kamu akan melihat sebuah N/A di dalam cell.

Lebih mudah untuk melakukan copy dan paste formula ke dalam cell lainnya, namun itu memerlukan beberapa modifikasi. Pada awalnya, Excel akan mencari cell C5 untuk lookup, namun kita perlu menyesuaikan itu ke "B5" di dalam formula bar. Setelah kita melakukan itu, lookup akan berkerja - hampir.

Kita perlu pergi ke formula bar, dan ganti bagian pertama formula dari C5 kembali ke B5. Ketika kita memindahkan formula sebanyak satu kolom, Excel mengupdate bagian lainnya formula. Kita mendapatkan sebuah "N/A" karena Excel berusaha untuk mencocokkan kuantitas (Kolom C) pada daftar lookup, namun daftar kita tidak mencantumkan kuantitas.

Sejauh ini, formula kita akan menjadi:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,FALSE)

Bagaimana pun juga, perhatikan di dalam screenshot di atas bahwa kita menarik data yang sedikit salah ke dalam kolom "Phone". Kita tetap menarik kolom kedua di dalam daftar lookup dengan "2" di dalam formula. Kita perlu mengubah ini ke angka kolom dengan telefon kontak supplier kita.

Langkah 3: Memperbaiki Permasalahan Error N/A Pada VLOOKUP

Mari kembali dan periksa daftar lookup. Kamu akan menyadari bahwa nomor telefon bukan berada di kolom ke-7 di dalam daftar lookup. Mari kembali ke formula Excel kita dan memperbarui lookup untuk menarik dari kolom ke-7.

Yang perlu kita lakukan adalah memperbarui bagian kolom daftar lookup dari "2" ke "7" dan sekarang itu berkerja dengan bagus!

Formula akhir untuk pencarian nomor telefon supplier:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,7,FALSE)

Langkah 4: Tambahkan Kolom Lainnya Untuk Menyelesaikan Formula VLOOKUP Kita

Terakhir, mari mengerjakan kolom Hari Pengiriman Supplier. Salin dan tempelkan cell G5 ke cell H5. Sekali lagi, kita perlu memperbaiki formula dengan mengubah C5 kembali ke B5 untuk menggunakan ingredient sebagai key primer. Kemudian, cukup perbarui "7" ke "5" untuk menggunakan kolom ke-5 dari tabel lookup.

Formula akhir kita untuk hari pengiriman supplier:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,5,FALSE)

Itu saja! Kita telah menulis satu formula dan memodifikasinya untuk menarik setiap data yang kita perlukan untuk meletakkan pesanan kita.

Troubleshooting VLOOKUP

Jadi, kamu telah menuliskan formula VLOOKUP mengikuti instruksi tahap demi tahap, namun itu tetap tidak berkerja. Excel menangani sesuatu secara sungguh-sungguh, jadi kita perlu berhati-hati dengan data dan formula VLOOKUP. Mari kita lihat beberapa ide untuk memperbaiki formula VLOOKUP yang tidak berkerja.

1. Banyak Kecocokan

Salah satu permasalahan umum dengan VLOOKUP adalah ketika ada banyak kecocokan di dalam daftar VLOOKUP. Ketika kamu menggunakan VLOOKUP, itu akan mencocokkan item pertama yang ada di dalam daftar.

Point dari VLOOKUP adalah mencari sebuah kecocokan sebuah item terhadap daftar. Daftar look tidak boleh berisi duplikat untuk key primer, item yang kamu gunakan untuk mencocokkan. Jika tidak, Excel menjadi bingung dan hanya menampilkan kecocokan pertama.

2. Spasi Awal atau Spasi Akhir

Permasalahan umum lainnya adalah data kita mungkin tidak sebenarnya cocok dalam mata Excel. Data dengan sebuah spasi sebelum itu disebut memiliki "leading" space, sementara data dengan spasi setelahnya memiliki sebuah "trailing space".

Jika data kecocokan memiliki spasi sebelum atau sesudahnya, Excel melihat dua bagian data ini sebagai yang benar-benar berbeda, dan tidak akan mengembalikan sebuah kecocokan. Excel meliha "_Andrew", "Andrew_" dan "Andrew" sebagai tiga bagian data yang berbeda yang tidak akan dicocokkan di dalam VLOOKUP.

Jika data memiliki leading atau trailing space, gunakan function TRIM di dalam Excel untuk membersihkannya. VLOOKUP akan berkerja dengan baik setelah itu digunakan untuk menghilangkan leading dan trailing space.

3. Mengunci Cell Reference

Seperti yang kamu mungkin  tahu,kamu dapat menarik cell ke bawah untuk melakukan paste formula ke dalam cell lainnya. Bagaimana pun juga, ini terkadang merusak VLOOKUP karena cell reference yang berubah.

Saat kamu menarik formula ke bawah, itu menarik reference untuk lookup table ke luar. Tiba-tiba, lookup table kehilangan baris dari lookup table dan VLOOKUP kita tidak berkerja.

Perbaikannya adalah membuatkan formula sebuah absolute reference, sehingga ketika kamu menarik formula ke bawah, daftarnya tidak berubah. Klik di dalam cell dimana kamu menuliskan VLOOKUP, dan kemudian klik di suatu tempat di dalam daftar lookup acuan. Kemudian, tekan F4. Kamu akan memperhatikan bahwa formula berubah mencantumkan lambang dollar.

Rekap dan Tetap Belajar

VLOOKUP adalah salah satu formula esensial untuk menjadi pengguna Excel yang produktif. Jelas tidak cukup banyak waktu untuk secara manual mencari data dan mengetiknya ulang dan berulang lagi, sehingga formula seperti VLOOKUP penting untuk dipelajari.

• Jika kamu ingin mempelajari kumpulan skill Excel tingkat lanjutan, periksa tutorial Bob Flisser tentang 12 Teknik Untuk Power User.
• Sebagai tambahan, course dari Bob Pengenalan Spreadsheet adalah panduan sempurna untuk memulai Excel.
• Terkadang, akan membantu jika mempelajari materi sejenis dari sumber lainnya. Dokumentasi resmi Microsoft Office tentang formula VLOOKUP adalah sumber bagus lainnya untuk menguasai VLOOKUP.

Jika kamu memiliki masalah dengan penggunaan formula VLOOKUP, silahkan meninggalkan komentar di bawah untuk troubleshooting.