Cara melakukan VLOOKUP di Excel dengan contoh dan trik tingkat lanjut

Pernahkah Anda mengalami Menavigasi antara baris dan kolom di Excel sambil mencoba menggabungkan data dari tabel berbeda dan tidak tahu cara mempercepat prosesnya? Jika Anda bekerja dengan lembar kerja yang rumit, Anda tahu betapa membosankannya mencari dan menghubungkan informasi secara manual. Untungnya, ada fungsi dasar yang menyelesaikan tugas ini dan menghemat banyak waktu Anda: fungsi VLOOKUP.

Dalam artikel ini, saya akan menunjukkan kepada Anda secara rinci cara menggunakan VLOOKUP di Excel, kelebihannya, cara menulis rumus dengan benar, berbagai kasus praktis, dan cara menyelesaikan masalah yang paling umum, semuanya dengan contoh yang jelas dan praktis. Selain itu, Anda akan menemukan Trik sehingga Anda tidak akan tersesat dalam sel lagi dan mendapatkan hasil maksimal dari data Anda, baik di Excel maupun di Google Lembar.

Apa fungsi VLOOKUP di Excel?

VLOOKUP, disebut VLOOKUP dalam bahasa Spanyol, adalah fungsi dari Microsoft Excel dan Google Sheets yang memungkinkan Anda mencari nilai di kolom pertama tabel dan mengembalikan data terkait dari kolom lain di baris yang sama. Istilah "pencarian vertikal" mengacu pada pencarian ke bawah kolom.

Fitur ini penting bagi mereka yang perlu menghubungkan data dari daftar yang berbeda tanpa harus membandingkannya satu per satu. Misalnya, Anda dapat mencari harga suatu produk berdasarkan kodenya, nama siswa berdasarkan nomor registrasinya, atau departemen karyawan berdasarkan ID-nya.Kuncinya adalah kolom pencarian berisi nilai unik yang mengidentifikasi setiap item.

Bayangkan Anda memiliki selembar berisi pesanan bahan-bahan untuk restoran Anda dan lembar lain berisi pemasok yang menyediakan bahan-bahan yang sama. Dengan VLOOKUP, Anda dapat mengambil nama pemasok, nomor telepon, atau tanggal pengiriman untuk setiap bahan dalam hitungan detik, tanpa harus menyalin apa pun dengan tangan.

Bagaimana rumus VLOOKUP bekerja?

Rumus VLOOKUP disusun sebagai berikut:

=BUSCARV(valor_búsqueda; intervalo; índice_columna; )

Setiap elemen fungsi memiliki fungsi spesifik:

• nilai_pencarian: Ini adalah data yang ingin Anda cari, biasanya sel yang berisi kunci unik, misalnya kode produk atau nama orang.
• interval: Ini adalah rentang sel tempat data berada. Kolom tempat data berada nilai_pencarian HARUS SELALU menjadi kolom pertama dalam rentang tersebut.
• indeks_kolom: Ini adalah nomor kolom, dalam rentang, tempat Anda ingin mengembalikan hasil. Ingat bahwa kolom pencarian adalah nomor 1.
• : Ini bersifat opsional. Tunjukkan jika Anda menginginkan kecocokan yang tepat (PALSU atau 0) atau perkiraan satu (NYATA atau 1). Secara default, ini adalah kecocokan perkiraan, tetapi dalam praktiknya, Anda hampir selalu ingin menggunakan kecocokan persis.

Contoh dasar untuk mencari harga buah dalam tabel adalah:

=BUSCARV("Manzana"; A2:C10; 3; FALSO)

Dengan rumus ini, Excel akan mencari "Apple" di kolom pertama rentang A2:C10. Jika menemukannya, Excel akan mengembalikan nilai di kolom ketiga baris tersebut (misalnya, harga).

Ingat: VLOOKUP selalu mencari dari kiri ke kanan. VLOOKUP tidak dapat mencari di kolom di sebelah kiri kolom pencarian. Jika Anda perlu mencari secara terbalik, Anda perlu mengatur ulang data atau menggunakan rumus yang lebih canggih.

Untuk apa VLOOKUP digunakan? Contoh praktis

VLOOKUP sangat berguna ketika Anda mengelola data dalam jumlah besar dan perlu melakukan referensi silang informasi antara tabel yang berbeda. Berikut ini adalah beberapa contoh penggunaan umum:

• Data karyawan relasional: Anda memiliki daftar shift dan daftar nama serta posisi lainnya. VLOOKUP membantu Anda mengisi posisi secara otomatis pada tabel shift dengan menggunakan nomor karyawan sebagai kuncinya.
• Cocokkan inventaris dengan harga: Dari daftar produk yang tersedia, Anda dapat menambahkan harga masing-masing produk dengan mencarinya di tabel harga.
• Perbarui data secara otomatis: Setiap kali informasi dalam tabel referensi berubah (misalnya, pemasok), data yang Anda masukkan dengan VLOOKUP akan diperbarui secara otomatis.
• Cari informasi tentang siswa, buku, klien, produk, dll. dengan cepat dan otomatis.

VLOOKUP adalah sekutu terbaik Anda untuk berhenti menyalin dan menempel serta mengotomatiskan proses berulang di Excel, yang secara dramatis meningkatkan produktivitas Anda.

Langkah demi langkah untuk membuat rumus VLOOKUP

Mari kita lihat cara membuat rumus VLOOKUP dari awal menggunakan skenario kehidupan nyata. Katakanlah Anda mengelola pesanan bahan di restoran dan memiliki dua tab:

• Pemesanan Bahan: Daftar barang yang harus dibeli.
• Daftar pemasok: Sertakan nama pemasok, nomor telepon, tanggal pengiriman, dan informasi lain yang terkait dengan setiap bahan.

Kami ingin menambahkan tiga kolom ke daftar pesanan: nama pemasok, nomor telepon, dan tanggal pengiriman. Untuk melakukannya:

1. Pada lembar Pesanan Bahan, navigasikan ke sel tempat Anda ingin nama pemasok muncul.
2. Tekan “=” untuk mulai mengetik rumus.
3. Menulis Pencarian VLOOKUP( o VLOOKUP( jika Excel Anda berbahasa Inggris.
4. Pilih sel dengan nama bahan yang ingin Anda cari (misalnya, B5).
5. Ketik koma dan pilih rentang tempat data pemasok berada (misalnya, tabel di lembar Daftar Pemasok, dari A3 hingga G13).
6. Tekan F4 untuk menjadikan rentang sebagai referensi absolut (tanda $ akan muncul).
7. Tulis koma, tunjukkan nomor kolom yang berisi data yang ingin Anda bawa (misalnya, nama pemasok ada di kolom 2, nomor telepon di kolom 7, tanggal pengiriman di kolom 5...)
8. Akhirnya, tulis PALSU untuk mencari kecocokan yang tepat saja dan menutup tanda kurung.

Rumus akhir Anda untuk nama pemasok mungkin terlihat seperti ini: =BUSCARV(B5,'Lista de Proveedores'!$A$3:$G$13,2,FALSO)

Untuk telepon, cukup ubah nomor kolom (misalnya, 7), dan untuk hari pengiriman, masukkan indeks yang sesuai.

Trik kecil: jika Anda menyalin dan menempelkan rumus di bawah ini, pastikan referensi ke tabel pencarian terkunci untuk menghindari kesalahan (itulah sebabnya kami menggunakan F4 dan $).

Detail penting tentang sintaks dan argumen VLOOKUP

Mari kita uraikan setiap bagian argumen tersebut sehingga kita tidak membuat kesalahan dan memahami sepenuhnya apa yang kita perkenalkan:

• Nilai yang harus dicari: Itu bisa berupa teks, angka, referensi ke sel lain… Yang penting itu unik di kolom pertama rentang. Contoh: “102” atau B5.
• Jangkauan pencarian: Sertakan kolom dengan data yang ingin Anda cari dan semua kolom tempat Anda ingin mengekstrak informasi. Contoh: A2:D10.
• Indeks kolom: Ini adalah bilangan bulat dan selalu mulai menghitung dari kolom paling kiri rentang (1 = kolom pencarian, 2 = berikutnya, dst.). Tidak boleh kurang dari 1 atau lebih besar dari jumlah kolom dalam rentang.
• Kecocokan tepat atau perkiraan: SELALU disarankan untuk menyetel FALSE guna menghindari kejutan. Hanya gunakan TRUE jika kolom pencarian Anda diurutkan dan Anda mencari rentang.

Dan di Google Sheets? Semua hal di atas 100% berlaku, meskipun dalam Sheets argumen terkadang memiliki sedikit variasi, seperti sudah diurutkan.

Contoh VLOOKUP yang sangat berguna

Berikut adalah beberapa contoh untuk skenario yang berbeda:

• Pencarian Teks: =BUSCARV("Manzana";B4:D8;3;FALSO) → Mengembalikan harga apel
• Pencarian berdasarkan referensi sel:=BUSCARV(G9;B4:D8;3;FALSO) → Temukan nilai G9 dalam daftar
• Cari berdasarkan perkiraan kecocokan: =BUSCARV(102;A4:D8;2;VERDADERO) → Jika 102 tidak ada, maka akan diberikan nilai terdekat kurang dari 102
• Dengan indeks kolom variabel: =BUSCARV(G3;B4:D8;2;FALSO) → Temukan kuantitas berdasarkan nilai G3
• Menggabungkan kriteria (di Google Sheets): Jika Anda perlu mencari berdasarkan nama depan dan belakang, Anda dapat membuat kolom pembantu yang menghubungkan keduanya dan menggunakannya sebagai kunci unik.

Jika Anda memiliki beberapa baris yang cocok dengan pencarian Anda, VLOOKUP akan selalu mengembalikan kecocokan PERTAMA yang ditemukan.

Kesalahan umum dan cara mengatasinya

Kesalahan VLOOKUP yang paling umum adalah #N/A, yang berarti nilai pencarian tidak ada di kolom pertama rentang. Mari kita lihat alasan paling umum dan cara memperbaikinya:

• Data duplikat: Jika Anda memiliki beberapa catatan dengan kunci yang sama, hanya yang pertama yang akan ditampilkan. Hapus duplikat dari kolom pencarian Anda untuk menghindari kebingungan.
• Spasi awal/akhir: Jika terdapat spasi tak terlihat sebelum atau sesudah data Anda, Excel tidak akan mengembalikan kecocokan. Gunakan fungsi SPACES untuk membersihkan data Anda.
• Referensi tabel salah: Jika menyalin rumus menggeser rentang, pencarian akan gagal. Solusi: Gunakan referensi absolut (dengan $).
• Indeks kolom di luar jangkauan: Jika Anda memasukkan angka yang lebih besar dari kolom yang telah Anda pilih, kesalahan #REF! akan muncul.
• Urutan salah: Jika Anda menggunakan pencocokan perkiraan tanpa mengurutkan kolom pencarian dari terendah ke tertinggi, Anda mungkin mendapatkan hasil yang salah. Selalu tentukan FALSE kecuali Anda yakin dengan apa yang Anda lakukan.

Anda dapat menyesuaikan kesalahan #N/A dengan menggabungkan VLOOKUP dengan IFNA:

• =SI.ERROR(BUSCARV(...), "No encontrado") di excel
• =SI.ND(BUSCARV(...), "No encontrado") di Google Spreadsheet

Ini akan menampilkan pesan yang lebih bersahabat daripada kesalahan biasa, yang berguna jika Anda berbagi lembar kerja dengan orang lain.

Kiat dan trik tingkat lanjut untuk menguasai VLOOKUP

1. Gunakan referensi absolut dalam rentang
Setiap kali Anda menyalin rumus ke bawah, pastikan rentang pencarian tidak berubah. Jadi, setelah memilih rentang, tekan F4 untuk memasukkan tanda $. Ini memastikan bahwa Excel/Sheets tidak mengubahnya saat Anda menyalin rumus.

2. Selalu urutkan kolom pencarian jika Anda menggunakan pencocokan perkiraan
Jika Anda benar-benar perlu mencari rentang (misalnya, menghitung komisi berdasarkan rentang), urutkan kolom tempat VLOOKUP mencari dari terendah ke tertinggi.

3. Bekerja dengan data yang bersih
Sebelum menggunakan fungsi ini, hapus spasi dan pastikan angka atau tanggal tidak disimpan sebagai teks. Ini mencegah hasil yang tidak diharapkan.

4. VLOOKUP hanya melihat ke kanan
Jika Anda perlu mencari nilai di sebelah kiri, Anda harus mengatur ulang kolom Anda atau menggunakan fungsi seperti INDEX dan MATCH, atau beralih ke XLOOKUP, yang tersedia di versi Excel yang lebih baru.

5. Gunakan wildcard untuk kecocokan parsial
Jika Anda ingin mencari nama yang dimulai dengan nama yang sama tetapi tidak tahu bagaimana nama tersebut berakhir, Anda dapat menggunakan tanda bintang (*) atau tanda tanya (?) pada nilai pencarian Anda dengan VLOOKUP, selalu menggunakan pencocokan persis (FALSE). Contoh: BUSCARV("La*";...; FALSO) akan mengembalikan data pertama yang dimulai dengan "La".

6. VLOOKUP pada lembar atau buku yang berbeda
Anda dapat mencari dalam tabel yang terletak di tab (lembar) lain atau bahkan di berkas Excel lain. Cukup tentukan nama lembar dan rentangnya seperti ini: 'Hoja2'!A1:F20Untuk buku kerja lainnya, buka terlebih dahulu dan pilih rentang; Excel akan secara otomatis menambahkan jalur.

VLOOKUP di Google Sheets: persamaan dan perbedaan

Jika Anda pengguna Google Sheets, fungsi VLOOKUP bekerja hampir sama dengan Excel, meskipun ada sedikit perubahan dalam nama argumen.:

• nilai_pencarian: apa yang ingin Anda cari.
• selang: rentang yang dicari dan menghasilkan hasil.
• indeks: kolom tempat membawa data, dalam interval (1 adalah kolom pertama dari rentang yang dipilih).
• sudah diurutkan: apakah Anda mencari kecocokan yang tepat (SALAH) atau kecocokan perkiraan (BENAR).

Selain itu, Google Sheets menyertakan fungsi SI.ND() untuk menyesuaikan pesan ketika nilai yang dicari tidak ditemukan dan SI.ERROR() untuk kesalahan umum lainnya.

Detail menarik lainnya adalah Anda dapat memberi nama rentang alih-alih menggunakan sel, yang menyederhanakan rumus dan membuatnya lebih mudah dibaca.

Keterbatasan dan alternatif untuk VLOOKUP

Meskipun VLOOKUP sangat kuat, ia memiliki beberapa batasan:

• Anda tidak dapat mencari di sebelah kiri kolom pencarian.
• Hanya mengembalikan nilai pertama yang cocok.
• Dapat menjadi lambat jika volume data besar.
• Ini tidak memungkinkan Anda untuk mencari nilai dalam urutan menurun jika rentang Anda diurutkan secara berbeda.

Dalam versi Excel saat ini, Anda dapat menggunakan SEARCHX (XLOOKUP), yang memecahkan banyak masalah ini: memungkinkan Anda mencari di kiri dan kanan, menemukan hasil di kolom mana pun, dan lebih fleksibel.

Praktik terbaik untuk bekerja dengan VLOOKUP

• Gunakan kunci unik: Jika kolom pencarian Anda berisi duplikat, bersihkan data sebelum menerapkan VLOOKUP.
• Jaga rentang tetap terurut hanya jika Anda menggunakan pencocokan perkiraan. Hal ini tidak diperlukan untuk mendapatkan kecocokan yang tepat, tetapi tidak ada salahnya untuk memiliki data yang bersih.
• Referensi rentang kunci dengan $ sebelum menyalin rumus ke sel lain, ini akan mencegah kesalahan dan pergeseran yang tidak diinginkan.
• Pastikan tanggal dan angka diformat dengan benar (tidak sebagai teks).
• Gunakan IF.ERROR, IF.ND atau IFNA untuk menyesuaikan pesan kesalahan, terutama jika Anda berbagi lembar dengan lebih banyak pengguna.

Artikel terkait:

Cara Menggunakan VLOOKUP dan XLOOKUP di Excel: Panduan Lengkap dan Contoh Praktis

Isaac

Penulis yang bersemangat tentang dunia byte dan teknologi secara umum. Saya suka berbagi ilmu melalui tulisan, dan itulah yang akan saya lakukan di blog ini, menunjukkan kepada Anda semua hal paling menarik tentang gadget, perangkat lunak, perangkat keras, tren teknologi, dan banyak lagi. Tujuan saya adalah membantu Anda menavigasi dunia digital dengan cara yang sederhana dan menghibur.