Bagaimana Menggunakan Function VLOOKUP Di Dalam Excel

Indonesian (Bahasa Indonesia) translation by Yosef Andreas (you can also view the original English article)

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.

Combining two lists is a a great situation to use VLOOKUP — Menyisiri dua daftar adalah situasi sempurna untuk menggunakan VLOOKUP.

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.

Example VLOOKUP formula used to look up employee data — Contoh formula VLOOKUP yang digunakan untuk mencari data karyawan.

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.

Excel VLOOKUP list ingredient order and supplier list example — Pada bagian atas, kita memiliki data pesanan, yang berada di tab "Ingredient Orders". Di bawahnya adalah "Supplier List", yang akan bertindak sebagai daftar yang dicari.

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.

Select the Excel lookup list — Arahkan Excel ke daftar lookup.

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)

Pulldown formula is working perfectly in Excel — Formula kita berkerja dengan sempurna, menarik data supplier untuk Kentang.

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.

Copying and pasting formula in Excel Lookup Moved — Copy dan paste lookup dengan penyesuaian di dalam Excel.

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.

Lookup List Column numbers need to be updated — Kita perlu memperbarui "2" di dalam formula Excel ke "7" untuk menarik data dari kolom ke-7 di dalam daftar lookup.

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

Our Excel vlookup formula is Updated for Supplier Phone — Perhatikan bahwa nomor telefon supplier sekarang berkerja dengan baik setelah kita memperbarui formula untuk menggunakan kolom ke-7 dari daftar lookup kita. Ssekarang, cukup tarik formula ke bawah untuk memperbarui untuk semua cell.

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.

Final supplier delivery day lookup formula applied — Penerapan formula akhir pencarian hari pengiriman supplier.

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.

Troubleshooting Example of Excel VLOOKUP not working — VLOOKUP kita mengatakan bahwa Carrie Richard adalah Director Marketing kita, namun kita tahu beliau baru-baru ini dipromosikan ke Direktur Utama. Mengapa lookup kita tidak berkerja?

Lookup problem with two listings conflicting in our VLOOKUP — Whoops, sepertinya ada dua daftar untuk Carrie Richard di dalam daftar lookup - satu untuk Direktur Marketing, dan satunya untuk Direktur Utama. Setelah kita menghapus baris "Direktur Marketing" dari daftar lookup, data kita menjadi benar.

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".

Trailing Space VLOOKUP Example Problem — VLOOKUP ini ditulis secara sempurna, namun tidak berkerja. Kamu dapat melihat dengan jelas nama di dalam daftar lookup, namun Excel tidak mengembalikan kecocokannya. Lanjutkan membaca untuk menemukan mengapa.

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.

Resolving Excel VLOOKUP Trailing Space Issue — Tampaknya ada "trailing space", atau sebuah spasi setelah nama di dalam cell. Itu tidak mungkin dilihat, namun itu dapat merusak VLOOKUP karena Excel memperlakukan "Alyssa Reddall" dan "Alyssa Reddall(spasi)" secara berbeda. Setelah kamu menghapus spasi, VLOOKUP akan berkerja secara normal.

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.

Bad reference VLOOKUP problem — Di dalam screenshot ini, saya menarik VLOOKUP ke bawah daftar untuk menerapkannya pada cell lainnya. Bagaimana pun juga, perhatikan lookup reference berubah dari A2 ke B15, A16 ke B29, menyebabkan mengapa formula tidak berkerja. Daftar lookup saya sebenarnya ada di dalam A2 hingga B15, sehingga lookup sekarang rusak.

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.

Updated Excel VLOOKUP Formula with Absolute Reference — Perhatikan bahwa formula (ditunjukkan pada bagian atas screenshot ini di dalam formula bar) sekarang mencantumkan lambang dollar di dalam acuan daftar lookup, yang menunjukkan itu menggunakan **absolute reference**. Sekarang, ketika saya menarik formula ke bawah, itu mengunci formula untuk daftar lookup dan berkerja secara sempurna.

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.

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