VLOOKUP, XLOOKUP, atau Database? Saat Rumus Mulai Berat
VLOOKUP adalah rumus untuk mencari sebuah nilai di kolom paling kiri sebuah tabel, lalu mengambil data dari kolom lain di baris yang sama. Misalnya mencari kode produk di tabel master untuk mengambil harganya. Rumus ini ada di Excel dan Google Sheets, dan hampir semua admin pernah memakainya.
Masalahnya muncul saat rumus lookup dipakai ribuan kali di satu file. File jadi lambat dibuka, setiap perubahan butuh beberapa detik untuk dihitung ulang, dan sesekali hasilnya salah tanpa ada yang sadar. Artikel ini membahas cara kerja VLOOKUP, kapan XLOOKUP lebih baik, dan kapan sebaiknya berhenti mengandalkan rumus.
Cara kerja VLOOKUP
Susunan rumusnya:
=VLOOKUP(nilai_dicari; tabel; nomor_kolom; [pencocokan])
- nilai_dicari: nilai yang ingin dicari, misalnya kode produk “BRG-001”.
- tabel: rentang tabel referensi. Nilai yang dicari harus ada di kolom paling kiri rentang ini.
- nomor_kolom: kolom ke berapa yang ingin diambil, dihitung dari kiri.
- pencocokan: FALSE untuk harus sama persis, TRUE untuk mencari yang paling mendekati.
Catatan: pemisah argumen di Excel dan Google Sheets berbahasa Indonesia biasanya titik koma (;), sedangkan di pengaturan bahasa Inggris memakai koma (,).
Contoh di sebuah distributor: sheet “Penjualan” berisi kode produk dan jumlah. Harga ada di sheet “Master Produk”. Rumus =VLOOKUP(A2; 'Master Produk'!A:D; 3; FALSE) mengambil harga dari kolom ketiga tabel master.
Tiga jebakan yang paling sering terjadi:
- Lupa mengisi FALSE. Kalau argumen terakhir dikosongkan, VLOOKUP memakai pencocokan terdekat. Pada data yang tidak diurutkan, hasilnya bisa salah tanpa pesan error.
- Nomor kolom tetap. Kalau seseorang menyisipkan kolom baru di tabel master, nomor kolom 3 bisa menunjuk ke data yang salah.
- Tidak bisa melihat ke kiri. Nilai yang dicari harus di kolom paling kiri. Kalau kolom kuncinya ada di tengah, tabel harus diatur ulang.
Kelebihan XLOOKUP
XLOOKUP dibuat untuk menutup kelemahan VLOOKUP. Rumusnya tersedia di Excel untuk Microsoft 365, Excel 2021, dan Excel 2024, serta di Google Sheets. Excel 2016 dan 2019 tidak punya XLOOKUP.
=XLOOKUP(nilai_dicari; kolom_dicari; kolom_hasil; [jika_tidak_ada])
Keunggulannya:
- Pencocokan persis jadi bawaan. Tidak ada lagi hasil salah karena lupa FALSE.
- Kolom hasil dirujuk langsung tanpa nomor kolom. Menyisipkan kolom tidak merusak rumus.
- Bisa mencari ke kiri maupun ke kanan.
- Ada argumen untuk nilai tidak ditemukan, misalnya menampilkan “Tidak ada” alih-alih error #N/A.
- Bisa mencari dari bawah, berguna untuk mengambil transaksi terakhir seorang pelanggan.
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Pencocokan bawaan | Terdekat (kalau argumen dikosongkan) | Persis |
| Arah pencarian | Hanya ke kanan | Ke mana saja |
| Rujukan kolom hasil | Nomor kolom | Rentang kolom langsung |
| Penanganan tidak ditemukan | Perlu IFERROR | Argumen bawaan |
| Tersedia di | Semua versi Excel dan Google Sheets | Excel 365, 2021, 2024, Google Sheets |
Satu hal yang perlu diingat: kalau file Anda dibuka rekan yang masih memakai Excel 2016 atau 2019, rumus XLOOKUP tidak akan berjalan di sana. Pastikan semua pengguna file memakai versi yang mendukung.
Kenapa file jadi lambat
XLOOKUP lebih aman, tapi tidak otomatis lebih cepat. File yang lambat biasanya bukan karena rumus yang dipilih, melainkan karena cara pemakaiannya. Penyebab paling umum:
- Rujukan kolom penuh seperti A:D di ribuan baris, sehingga setiap rumus memeriksa rentang yang sangat besar.
- Lookup berlapis. Hasil VLOOKUP dipakai lagi untuk VLOOKUP lain, lalu dipakai lagi di SUMIF.
- Rumus yang selalu dihitung ulang, seperti INDIRECT, OFFSET, TODAY, atau NOW. Setiap perubahan kecil memicu perhitungan ulang di seluruh file.
- Menarik data antar file. Di Google Sheets, IMPORTRANGE yang digabung dengan lookup di banyak sel bisa sangat berat. Kami membahasnya di artikel IMPORTRANGE Google Sheets.
- Data historis bertahun-tahun di satu file. Transaksi tiga tahun lalu tetap ikut dihitung setiap hari.
Beberapa perbaikan cepat sebelum memikirkan langkah besar:
- Batasi rentang ke baris yang benar-benar terisi, atau pakai tabel bernama.
- Tambah satu kolom bantu untuk lookup, lalu rujuk kolom itu, daripada mengulang lookup yang sama di banyak kolom.
- Ubah hasil lookup untuk data lama jadi nilai statis (paste as values).
- Pisahkan data arsip ke file terpisah.
Alternatif: QUERY dan Power Query
Kalau yang Anda lakukan sebenarnya adalah menggabungkan dua tabel, ada alat yang lebih cocok daripada ribuan rumus lookup.
QUERY di Google Sheets. Fungsi QUERY memungkinkan Anda memfilter, mengelompokkan, dan menjumlahkan data dengan bahasa yang mirip SQL, dalam satu rumus. Misalnya menghitung total penjualan per produk untuk bulan ini tanpa SUMIF di setiap baris. Satu rumus QUERY sering bisa menggantikan puluhan kolom rumus bantu.
Power Query di Excel. Power Query mengambil data dari berbagai sumber, membersihkannya, lalu menggabungkan tabel lewat fitur Merge Queries. Hasilnya tabel jadi yang tinggal di-refresh. Tidak ada lagi ribuan rumus yang terus dihitung ulang. Kalau belum pernah mencoba, mulai dari artikel Power Query Excel.
Keduanya menggeser pola kerja dari “rumus di setiap sel” ke “satu proses yang mengolah seluruh tabel”. Ini biasanya langkah paling efektif untuk mengatasi file yang lambat.
Tanda data harus pindah ke database
Spreadsheet sangat bagus untuk analisis cepat dan data skala kecil sampai menengah. Tapi ada batasnya. Google Sheets punya batas jumlah sel per file (cek angka terbarunya di halaman bantuan Google), dan Excel punya batas 1.048.576 baris per sheet. Jauh sebelum batas itu tercapai, pemakaian sehari-hari biasanya sudah terasa berat.
Tanda-tanda sudah waktunya memindahkan data ke database:
- File butuh lebih dari beberapa detik setiap kali ada perubahan, dan sudah dioptimalkan.
- Banyak orang mengedit bersamaan dan sering terjadi data tertimpa atau baris terhapus tanpa jejak.
- Anda memakai lookup untuk menghubungkan lebih dari tiga tabel, seperti pelanggan, produk, transaksi, dan pembayaran. Itu sebenarnya pekerjaan database.
- Data dipakai oleh aplikasi lain, misalnya website, dashboard, atau workflow otomatisasi.
- Butuh riwayat perubahan dan hak akses per bagian yang tidak bisa dipenuhi dengan berbagi file.
Pindah ke database tidak berarti tim harus berhenti memakai spreadsheet. Pola yang umum: data utama disimpan di database seperti PostgreSQL atau MySQL, lalu spreadsheet dan dashboard membaca ringkasannya. Penggabungan tabel dikerjakan dengan SQL, yang memang dirancang untuk itu. Penjelasan dasarnya ada di artikel SQL adalah.
Kalau file spreadsheet Anda sudah jadi tulang punggung operasional dan mulai sering lambat, Aqilatic bisa membantu menilai apakah cukup dirapikan atau perlu dipindah ke database. Lihat layanan kami, atau mulai dari audit proses gratis.
Pertanyaan yang sering muncul
Apakah saya harus mengganti semua VLOOKUP dengan XLOOKUP?
Tidak perlu sekaligus. Rumus lama yang berjalan benar boleh dibiarkan. Pakai XLOOKUP untuk rumus baru, dan ganti rumus lama saat Anda memang sedang merapikan file tersebut.
Kenapa VLOOKUP saya menghasilkan #N/A padahal datanya ada?
Penyebab paling umum adalah spasi tersembunyi, perbedaan format angka dan teks (misalnya kode “001” sebagai teks dan 1 sebagai angka), atau rentang tabel yang tidak mencakup baris tersebut.
Apakah INDEX MATCH masih relevan?
Masih. INDEX MATCH bisa mencari ke kiri dan berjalan di semua versi Excel. Kalau rekan kerja Anda masih memakai Excel 2016 atau 2019, INDEX MATCH adalah alternatif yang aman untuk XLOOKUP.