Cara Menggunakan Fungsi VLOOKUP di Microsoft Excel [+ Video Tutorial]

Mengkoordinasikan data dalam jumlah besar di Microsoft Excel memakan waktu lama. Untungnya, Anda tidak perlu melakukannya. Fungsi VLOOKUP dapat membantu Anda mengotomatiskan tugas ini dan menghemat banyak waktu.

Apa sebenarnya yang dilakukan VLOOKUP? Berikut penjelasan sederhananya: Fungsi VLOOKUP mencari nilai tertentu dalam data Anda, dan setelah mengidentifikasi nilai tersebut, fungsi tersebut dapat menemukan — dan menampilkan — beberapa informasi lain yang terkait dengan nilai tersebut.

Fungsi VLOOKUP Microsoft Excel lebih mudah digunakan dari yang Anda kira. Terlebih lagi, ini sangat kuat, dan pastinya merupakan sesuatu yang ingin Anda miliki di gudang senjata analitis Anda.

Lewati ke:

Bagaimana cara kerja VLOOKUP?

VLOOKUP adalah singkatan dari “pencarian vertikal.” Di Excel, ini berarti tindakan mencari data secara vertikal di seluruh spreadsheet, menggunakan kolom spreadsheet — dan pengidentifikasi unik di dalam kolom tersebut — sebagai dasar pencarian Anda. Saat Anda mencari data, data tersebut harus dicantumkan secara vertikal di mana pun data tersebut berada.

Rumus VLOOKUP Excel

Microsoft menjelaskan rumus atau fungsi VLOOKUP sebagai berikut:

=VLOOKUP(nilai pencarian, rentang berisi nilai pencarian, nomor kolom dalam rentang berisi nilai kembalian, Perkiraan pencocokan (TRUE) atau Pencocokan tepat (FALSE)).

Ini membantu untuk mengatur data Anda sedemikian rupa sehingga nilai yang ingin Anda cari berada di sebelah kiri nilai kembalian yang ingin Anda temukan.

Rumusnya selalu mencari ke kanan.

Saat melakukan VLOOKUP di Excel, pada dasarnya Anda mencari data baru di spreadsheet berbeda yang dikaitkan dengan data lama di data Anda saat ini. Saat VLOOKUP menjalankan pencarian ini, ia selalu mencari data baru di sebelah kanan data Anda saat ini.

Misalnya, jika satu spreadsheet memiliki daftar nama vertikal, dan spreadsheet lain memiliki daftar nama dan alamat emailnya yang tidak terorganisir , Anda dapat menggunakan VLOOKUP untuk mengambil alamat email tersebut sesuai urutan yang Anda miliki di spreadsheet pertama Anda. Alamat email tersebut harus dicantumkan di kolom di sebelah kanan nama di spreadsheet kedua, atau Excel tidak akan bisa menemukannya. (Lihat gambar… )

Rumusnya memerlukan pengidentifikasi unik untuk mengambil data.

Rahasia cara kerja VLOOKUP? Pengidentifikasi unik.

Pengidentifikasi unik adalah informasi yang dibagikan oleh kedua sumber data Anda, dan — sesuai dengan namanya — pengidentifikasi tersebut unik (yaitu pengidentifikasi hanya dikaitkan dengan satu catatan di database Anda). Pengidentifikasi unik mencakup kode produk, unit penyimpanan stok (SKU), dan kontak pelanggan.

Baiklah, cukup penjelasannya: mari kita lihat contoh aksi VLOOKUP lainnya!

Contoh VLOOKUP Excel

Dalam video di bawah ini, kami akan menunjukkan contoh tindakan, menggunakan fungsi VLOOKUP untuk mencocokkan alamat email (dari sumber data kedua) dengan data terkait di lembar terpisah.

Catatan Penulis: Ada banyak versi Excel yang berbeda, jadi apa yang Anda lihat dalam video di atas mungkin tidak selalu sama persis dengan apa yang Anda lihat di versi Anda. Itu sebabnya kami mendorong Anda untuk mengikuti petunjuk tertulis di bawah ini.

Cara Menggunakan VLOOKUP di Excel

1. Identifikasi kolom sel yang ingin Anda isi dengan data baru.
2. Pilih 'Fungsi' (Fx) > VLOOKUP dan masukkan rumus ini ke dalam sel yang Anda sorot.
3. Masukkan nilai pencarian yang ingin Anda ambil data barunya.
4. Masukkan susunan tabel pada spreadsheet tempat data yang Anda inginkan berada.
5. Masukkan nomor kolom data yang Anda ingin Excel kembalikan.
6. Masukkan rentang pencarian Anda untuk menemukan nilai pencarian yang sama persis atau perkiraan.
7. Klik 'Selesai' (atau 'Masuk') dan isi kolom baru Anda.

Sebagai referensi Anda, berikut tampilan sintaks fungsi VLOOKUP:

VLOOKUP(nilai_pencarian, array_tabel, col_index_num, rentang_pencarian)

Pada langkah-langkah di bawah ini, kami akan menetapkan nilai yang tepat untuk masing-masing komponen ini, menggunakan nama pelanggan sebagai pengidentifikasi unik kami untuk menemukan MRR setiap pelanggan.

1. Identifikasi kolom sel yang ingin Anda isi dengan data baru.

Ingat, Anda ingin mengambil data dari sheet lain dan menyimpannya ke sheet ini. Dengan mengingat hal tersebut, beri label pada kolom di sebelah sel yang informasi selengkapnya ingin Anda berikan dengan judul yang tepat di sel atas, seperti “MRR”, untuk pendapatan berulang bulanan. Kolom baru ini adalah tempat penyimpanan data yang Anda ambil.

2. Pilih 'Fungsi' (Fx) > VLOOKUP dan masukkan rumus ini ke dalam sel yang Anda sorot.

Di sebelah kiri bilah teks di atas spreadsheet, Anda akan melihat ikon fungsi kecil yang terlihat seperti skrip: Fx . Klik pada sel kosong pertama di bawah judul kolom Anda dan kemudian klik ikon fungsi ini. Sebuah kotak berjudul Formula Builder atau Insert Function akan muncul di sebelah kanan layar Anda (tergantung pada versi Excel yang Anda miliki).

Cari dan pilih “VLOOKUP” dari daftar opsi yang disertakan dalam Pembuat Formula. Lalu, pilih OK atau Sisipkan Fungsi untuk mulai membuat VLOOKUP Anda. Sel yang saat ini Anda soroti di spreadsheet Anda sekarang akan terlihat seperti ini: “ =VLOOKUP() “

Anda juga dapat memasukkan rumus ini ke dalam panggilan secara manual dengan memasukkan teks tebal di atas tepat ke dalam sel yang Anda inginkan.

Dengan teks =VLOOKUP dimasukkan ke dalam sel pertama Anda, saatnya mengisi rumus dengan empat kriteria berbeda. Kriteria ini akan membantu Excel mempersempit lokasi data yang Anda inginkan dan apa yang harus dicari.

3. Masukkan nilai pencarian yang ingin Anda ambil data barunya.

Kriteria pertama adalah nilai pencarian Anda — ini adalah nilai spreadsheet Anda yang memiliki data terkait dengannya, yang Anda ingin Excel temukan dan kembalikan untuk Anda. Untuk memasukkannya, klik sel yang berisi nilai yang Anda cari kecocokannya. Dalam contoh kita, yang ditunjukkan di atas, itu ada di sel A2. Anda akan mulai memigrasikan data baru ke D2, karena sel ini mewakili MRR nama pelanggan yang tercantum di A2.

Ingatlah bahwa nilai pencarian Anda bisa berupa apa saja: teks, angka, tautan situs web, apa saja. Selama nilai yang Anda cari cocok dengan nilai dalam spreadsheet pengarah — yang akan kita bicarakan pada langkah berikutnya — fungsi ini akan mengembalikan data yang Anda inginkan.

4. Masukkan susunan tabel pada spreadsheet tempat data yang Anda inginkan berada.

Di samping bidang “array tabel”, masukkan rentang sel yang ingin Anda cari dan lembar tempat sel-sel tersebut berada, menggunakan format yang ditunjukkan pada gambar di atas. Entri di atas berarti data yang kita cari ada dalam spreadsheet berjudul “Halaman” dan dapat ditemukan di mana saja antara kolom B dan kolom K.

Lembar tempat data Anda berada harus berada dalam file Excel Anda saat ini. Ini berarti data Anda bisa berada di tabel sel berbeda di suatu tempat di spreadsheet Anda saat ini, atau di spreadsheet berbeda yang ditautkan di bagian bawah buku kerja Anda, seperti yang ditunjukkan di bawah ini.

Misalnya, jika data Anda terletak di “Sheet2” antara sel C7 dan L18, entri array tabel Anda akan menjadi “Sheet2!C7:L18.”

5. Masukkan nomor kolom data yang ingin dikembalikan oleh Excel.

Di bawah kolom array tabel, Anda akan memasukkan “nomor indeks kolom” dari array tabel yang Anda cari. Misalnya, jika Anda berfokus pada kolom B hingga K (notasi “B:K” saat dimasukkan dalam bidang “array tabel”), namun nilai spesifik yang Anda inginkan ada di kolom K, Anda akan memasukkan “10” di kolom “nomor indeks kolom”, karena kolom K adalah kolom ke 10 dari kiri.

6. Masukkan rentang pencarian Anda untuk menemukan nilai pencarian yang sama persis atau perkiraan.

Dalam situasi seperti ini, yang berkaitan dengan pendapatan bulanan, Anda ingin menemukan pencocokan tepat dari tabel yang Anda telusuri. Untuk melakukan ini, masukkan “FALSE” di bidang “pencarian rentang”. Ini memberi tahu Excel bahwa Anda hanya ingin menemukan pendapatan persis yang terkait dengan setiap kontak penjualan.

Untuk menjawab pertanyaan menarik Anda: Ya, Anda bisa mengizinkan Excel mencari perkiraan kecocokan , bukan kecocokan persis. Untuk melakukannya, cukup masukkan TRUE dan bukan FALSE pada kolom keempat yang ditunjukkan di atas.

Saat VLOOKUP disetel untuk perkiraan kecocokan, VLOOKUP akan mencari data yang paling mirip dengan nilai pencarian Anda, bukan data yang identik dengan nilai tersebut. Jika Anda mencari data yang terkait dengan daftar tautan situs web, misalnya, dan beberapa tautan Anda memiliki “https://” di awal, Anda mungkin perlu mencari perkiraan kecocokan untuk berjaga-jaga jika ada tautan yang tidak memiliki tag “https://” ini. Dengan cara ini, sisa tautan bisa cocok tanpa tag teks awal ini yang menyebabkan rumus VLOOKUP Anda menampilkan kesalahan jika Excel tidak dapat menemukannya.

7. Klik 'Selesai' (atau 'Masuk') dan isi kolom baru Anda.

Untuk secara resmi memasukkan nilai yang Anda inginkan ke kolom baru dari Langkah 1, klik “Selesai” (atau “Masuk”, tergantung pada versi Excel Anda) setelah mengisi kolom “pencarian rentang”. Ini akan mengisi sel pertama Anda. Anda dapat menggunakan kesempatan ini untuk melihat spreadsheet lain untuk memastikan nilainya benar.

Jika demikian, isi sisa kolom baru dengan setiap nilai berikutnya dengan mengeklik sel pertama yang terisi, lalu mengeklik kotak kecil yang muncul di sudut kanan bawah sel ini. Selesai! Semua nilai Anda akan muncul.

Tutorial VLOOKUP

Stuck setelah mencoba melakukan VLOOKUP sendiri dengan langkah di atas? Lihat tutorial praktis dari Microsoft ini yang memiliki tutorial praktis yang akan memandu Anda dalam menggunakan fungsi ini dengan benar.

Sumber Gambar

Jika kamu sudah mengikuti langkah-langkah di atas dan VLOOKUPmu masih tidak berfungsi, mungkin ada masalah pada:

• Sintaks (yaitu bagaimana Anda menyusun rumusnya)
• Nilai (yaitu apakah data yang dicarinya bagus dan diformat dengan benar)

Memecahkan Masalah Sintaks VLOOKUP

Mulailah dengan melihat rumus VLOOKUP yang sudah kamu tulis pada cell yang telah ditentukan.

• Apakah ini mengacu pada nilai pencarian yang tepat untuk pengidentifikasi kuncinya?
• Apakah itu menentukan rentang array tabel yang benar untuk nilai yang perlu diambil
• Apakah ini menentukan lembar yang benar untuk rentang tersebut?
• Apakah lembar itu dieja dengan benar?
• Apakah ini menggunakan sintaks yang benar untuk merujuk ke lembar? (misalnya Halaman!B:K atau 'Lembar 1'!B:K)
• Apakah nomor kolom yang benar telah ditentukan? (misal A bernilai 1, B bernilai 2, dan seterusnya)
• Apakah Benar atau Salah merupakan rute yang benar untuk pengaturan sheet Anda?

Memecahkan Masalah Nilai VLOOKUP

Jika sintaksisnya bukan masalahnya, Anda mungkin mengalami masalah dengan nilai yang Anda coba terima sendiri. Hal ini sering kali bermanifestasi sebagai kesalahan #N/A di mana VLOOKUP tidak dapat menemukan nilai yang direferensikan.

• Apakah nilai diformat secara vertikal dan dari kanan ke kiri?
• Apakah nilainya sesuai dengan cara Anda merujuknya?

Misalnya, jika Anda mencari data URL, setiap URL harus berupa baris dengan data terkait di sebelah kiri baris yang sama. Jika Anda memiliki URL sebagai header kolom dengan data bergerak secara vertikal, VLOOKUP tidak akan berfungsi.

Sesuai dengan contoh ini, format URL harus cocok di kedua lembar. Jika Anda memiliki satu sheet yang menyertakan “https://” dalam nilainya sementara sheet lainnya menghilangkan “https://”, VLOOKUP tidak akan bisa mencocokkan nilainya.

VLOOKUP sebagai Alat Pemasaran yang Ampuh

Pemasar harus menganalisis data dari berbagai sumber untuk mendapatkan gambaran lengkap tentang perolehan prospek (dan banyak lagi). Microsoft Excel adalah alat yang sempurna untuk melakukan hal ini secara akurat dan dalam skala besar, terutama dengan fungsi VLOOKUP.

Catatan Editor: Postingan ini pertama kali diterbitkan pada Maret 2019 dan telah diperbarui agar lebih komprehensif.