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

Pernahkah Anda merasa tersesat di antara ribuan baris dan kolom lembar kerja saat mencari data tertentu? Jika Anda rutin menggunakan Excel, Anda mungkin pernah mendengar tentang VLOOKUP dan XLOOKUP, dua fungsi yang membuat perbedaan dalam hal mengotomatisasi pencarian dan menyederhanakan tugas. Menguasai fungsi-fungsi ini merupakan lompatan kualitas dalam kehidupan sehari-hari setiap pengguna, mulai dari mereka yang mengelola daftar kontak sederhana hingga mereka yang menganalisis daftar kontak besar. database di perusahaan.

Artikel ini adalah panduan utama Anda untuk memahami dan menggunakan VLOOKUP dan XLOOKUP di Excel seperti seorang profesional. Di sini kami uraikan semua rahasia, keuntungan, keterbatasan dan Trik praktis, dimulai dari dasar-dasar dan mendalami contoh, sintaksis, serta cara memanfaatkannya secara maksimal, sehingga pencarian di Excel tidak akan menghentikan Anda lagi.

Apa itu VLOOKUP dan XLOOKUP di Excel?

VLOOKUP dan XLOOKUP termasuk dalam keluarga fungsi pencarian dan referensi di Excel. Ini adalah alat yang memudahkan pencarian data tertentu dalam daftar atau tabel besar, mengembalikan informasi terkait dengan cepat dan tanpa kesalahan. Penggunaannya beragam dan diperlukan: faktur, inventaris, laporan, sumber daya manusia, CRM, akuntansi... Jika Anda perlu menemukan sesuatu di lautan data, fitur-fitur ini adalah teman terbaik Anda.

VLOOKUP adalah rumus klasik untuk mencari data secara vertikal., yaitu di dalam kolom. Ini telah menjadi andalan di Excel selama bertahun-tahun. Namun, dengan versi baru hadir XLOOKUP, fungsi yang lebih canggih dan fleksibel yang mengatasi banyak keterbatasan VLOOKUP.

Mengapa begitu penting untuk mempelajari keduanya? Karena banyak perusahaan masih bekerja dengan versi Excel lama, di mana XLOOKUP tidak ada, dan karena VLOOKUP masih berguna dan sangat kompatibel. Sekarang, jika Anda memiliki versi yang tepat, SEARCHX membuka dunia kemungkinan baru untuk Anda.

Apa kegunaan masing-masing? Penggunaan dasar dan situasi kehidupan nyata

Fungsi VLOOKUP digunakan untuk menemukan data dengan merujuk ke nilai di kolom paling kiri suatu tabel., mengembalikan sesuatu yang terkait yang ada di kolom lain di baris yang sama. Misalnya: Anda memiliki daftar produk dengan kode, deskripsi, dan harga, dan Anda ingin deskripsi dan harga tersebut ditampilkan secara otomatis saat Anda memasukkan kode. VLOOKUP mempermudahnya bagi Anda.

SEARCHX membawa konsep ini ke tingkat berikutnya: memungkinkan Anda mencari secara vertikal dan horizontal, di kolom atau baris mana pun, dan mengembalikan satu atau beberapa data tanpa batasan VLOOKUP. Selain itu, Anda dapat menyesuaikan perilaku pencarian, hasil pencarian jika terjadi kesalahan, pencarian dengan karakter pengganti, dan fitur lanjutan lainnya.

Mari kita lihat ini dengan contoh praktis yang sangat umum: manajemen faktur. Bayangkan Anda adalah UKM yang menjual peralatan medis. Anda tidak memiliki sistem penagihan yang canggih dan perlu mengekstrak data dari daftar yang berisi lebih dari 3.000 item setiap kali pesanan masuk. Mengisi data secara manual akan memakan waktu lama dan rawan kesalahan. Dengan VLOOKUP atau XLOOKUP Anda dapat mengotomatiskan prosesnya sehingga, hanya dengan kode produk, deskripsi, presentasi, dan harga terisi secara otomatis.

Perbedaan Utama: Apa yang Membatasi VLOOKUP dan Mengapa XLOOKUP Merupakan Evolusi?

VLOOKUP adalah fungsi yang hebat, tetapi memiliki beberapa keterbatasan. yang tidak nyaman dalam lembaran yang rumit:

• Hanya mencari dari kiri ke kanan. Data referensi harus selalu berada di kolom pertama rentang yang ditunjukkan. Jika data yang Anda cari tidak ada, Anda harus mengatur ulang tabel atau membuat kombinasi tambahan.
• Mengembalikan satu nilai. Jika Anda perlu mengekstrak beberapa item data terkait (misalnya, nama depan, nama belakang, dan departemen), Anda perlu menggunakan beberapa rumus.
• Anda harus menunjukkan nomor kolom yang akan dikembalikan. Jika kolom-kolom kemudian disusun ulang dalam tabel, rumus mungkin rusak atau mengembalikan hasil yang salah.
• Hasil default untuk data yang tidak ditemukan adalah kesalahan #N/A.. Itu harus dikombinasikan dengan YA. KESALAHAN untuk menampilkan pesan yang dipersonalisasi.
• Tidak mendukung karakter pengganti atau pencarian terbalik (bottom-up), atau pencarian biner tingkat lanjut.

XLOOKUP menghapus hampir semua batasan ini dan menambahkan perbaikan yang menentukan:

• Memungkinkan Anda mencari di kolom atau baris mana pun, apa pun posisinya. Anda dapat mencari di kolom tengah dan mengembalikan sesuatu dari kolom pertama, misalnya.
• Mengembalikan beberapa nilai saat menggunakan rentang multi-kolom, memfasilitasi penelusuran untuk basis data yang lebih kompleks.
• Anda tidak perlu menghitung kolom. Anda langsung memilih rentang hasil.
• Anda dapat menyesuaikan pesan tidak ditemukan tanpa rumus tambahan.
• Pencarian tepat, perkiraan, dan wildcard tersedia untuk menemukan bagian atau apa yang sedang Anda cari.
• Kemungkinan pencarian terbalik (bottom-up) dan pencarian biner (ideal untuk data yang diurutkan).
• Mencari beberapa tabel secara bersamaan dan menggunakan XLOOKUP bersarang, yang memperluas kemungkinan otomatisasi.

Versi Excel mana yang memperbolehkan masing-masing fungsi?

Fungsi VLOOKUP tersedia di semua versi Excel modern., dari yang terlama hingga yang terbaru. Anda akan menemukannya di Microsoft 365 seperti pada versi Excel abadi untuk desktop.

XLOOKUP, di sisi lain, hanya tersedia mulai dari Excel versi 1910 dan seterusnya.. Jika Anda memiliki Excel 2016 atau salah satu versi awal 2019, Anda mungkin tidak memilikinya. Untuk mengetahui apakah Anda dapat menggunakan XLOOKUP, ketik saja =XLOOKUP di bilah rumus dan lihat apakah itu muncul dalam daftar saran.

Jika versi Anda belum mendukungnya, jangan putus asa. Kuasai VLOOKUP selagi Anda bisa, dan jika saatnya tiba, beralihlah ke XLOOKUP untuk memanfaatkan berbagai peningkatannya.

Sintaksis VLOOKUP: Memecah Setiap Argumen

Mari kita uraikan rumus VLOOKUP untuk mengetahui cara kerjanya secara tepat.:

=VLOOKUP(nilai_pencarian; array_tabel; indikator_kolom; )

• nilai lookup: Data yang ingin Anda cari (dapat ditulis langsung atau di sel referensi).
• tabel_array: Rentang penuh untuk pencarian, termasuk kolom tempat data referensi berada dan kolom informasi yang akan diambil.
• kolom_indikator: Angka (dimulai dengan 1) kolom tempat Anda ingin mengekstrak hasil dalam rentang yang dipilih. Jika Anda menginginkan data dari kolom kedua rentang tersebut, Anda akan meletakkan angka 2 di sini.
• : Opsional. Jika BENAR atau dihilangkan, menemukan kecocokan yang mendekati; Jika SALAH, hanya mengembalikan kecocokan yang tepat.

Contoh tipikalAnda memiliki tabel pelanggan dalam rentang A1:D16. Anda ingin mencari nomor telepon (yang ada di kolom 4, yaitu kolom D) untuk pelanggan dengan ID 8. Rumusnya adalah:

=VLOOKUP(8;A1:D16;4;SALAH)

Jika data tidak ada dan Anda meminta kecocokan yang tepat, kesalahan #N/A akan muncul. Jika Anda mengizinkan pencocokan perkiraan, maka akan ditampilkan hasil yang paling mendekati di bawah.

Trik dan Varian VLOOKUP Tingkat Lanjut

VLOOKUP tidak terbatas pada pencarian berdasarkan kolom pertama: Anda dapat mendefinisikan ulang rentang untuk mencari berdasarkan kolom mana pun. Misalnya, jika Anda memiliki nama di kolom B dan Anda perlu mencari berdasarkan nama, bukan ID, cukup tentukan rentang dari kolom B dan seterusnya, lalu atur indikator kolom:

=VLOOKUP("Miguel Perea Ramos";B1:D16;3;SALAH)

Ini akan mencari berdasarkan nama dan mengembalikan, misalnya, nomor telepon yang sesuai.

Kombinasi lain yang berguna adalah VLOOKUP dengan daftar drop-down: Anda dapat membiarkan pengguna memilih nilai dari daftar dan secara otomatis meminta sel lain menampilkan informasi relevan menggunakan VLOOKUP. Ideal untuk berkas, laporan otomatis, katalog, dll.

Dan jika Anda ingin menghindari kesalahan yang buruk, gabungkan VLOOKUP dengan YA. KESALAHAN untuk menampilkan pesan khusus jika datanya tidak ada:

=IF(VLOOKUP(8;A1:D16;4;FALSE);»Pelanggan tidak ditemukan»)

Sintaksis XLOOKUP: Argumen dan Kekuatannya

Rumus XLOOKUP disusun seperti ini:

=XLOOKUP(nilai_pencarian; array_pencarian; array_yang_dikembalikan; ; ; )

• nilai lookup: Data yang ingin Anda temukan (sel atau nilai langsung).
• array_pencarian: Rentang tempat Excel harus mencari data tersebut.
• array yang dikembalikan: Rentang untuk mengekstrak hasil. Bisa satu kolom atau beberapa jika Anda menginginkan beberapa bagian data sekaligus, tidak seperti VLOOKUP.
• : Opsional. Jika nilainya tidak ditemukan, Anda dapat menentukan pesan atau data yang ingin dimunculkan (misalnya, "Tidak Ditemukan").
• : Opsional. Menentukan bagaimana nilai dicari: 0 untuk tepat, -1 untuk tepat atau lebih rendah berikutnya, 1 untuk tepat atau lebih tinggi berikutnya, 2 untuk pencocokan karakter pengganti.
• : Opsional. Memungkinkan Anda memilih cara melintasi data: 1 dari atas ke bawah, -1 dari bawah ke atas, 2 pencarian biner menaik, -2 pencarian biner menurun.

Contoh umum penggunaan dengan XLOOKUP: Anda ingin mencari produk dalam daftar (kode di kolom A dan harga di kolom E), menampilkan harga, dan menyesuaikan pesan jika produk tidak ada. Rumusnya adalah:

=XLOOKUP(B2;Harga!$A$1:$A$7000;Harga!$E$1:$E$7000; "Tidak ditemukan"; 0)

Di sini, jika elemen tidak ada, maka akan langsung menunjukkan “Tidak Ditemukan” tanpa perlu YA. KESALAHAN.

Rincian semua argumen XLOOKUP opsional

Salah satu keuntungan besar XLOOKUP adalah fleksibilitas argumen opsionalnya. Mari kita lihat satu per satu:

• : Anda dapat mengembalikan teks, angka, atau bahkan sel kosong dengan memasukkan “”. Lupakan kesalahan visual #N/A dan personalisasikan pengalamannya.
• :
  • 0: Kecocokan tepat (default).
  • -1: Kecocokan tepat atau nilai terendah berikutnya.
  • 1: Kecocokan tepat atau nilai tertinggi berikutnya.
  • 2: Pencocokan karakter pengganti (* untuk beberapa karakter, ? untuk satu karakter, ~ untuk mengabaikan karakter khusus).
• :
  • 1: Dari baris pertama hingga terakhir (default).
  • -1: Dari terakhir ke pertama (pencarian terbalik).
  • 2: Pencarian biner menaik (memerlukan data yang diurutkan dari terendah ke tertinggi).
  • -2: Pencarian biner menurun (memerlukan data yang diurutkan dari tertinggi ke terendah).

Contoh praktis XLOOKUP dalam aksi

Kami menunjukkan kepada Anda bagaimana LOOKUPX memperoleh keuntungan dalam berbagai skenario kehidupan nyata:

• Cari satu item data: Mencari nama negara dan mengembalikan kode negaranya.
• Mencari beberapa data: Tidak seperti VLOOKUP, Anda dapat mengembalikan seluruh baris informasi (nama, departemen, dll.) dengan satu rumus.
• Kustomisasi bebas kesalahan: Jika Anda ingin menghindari #N/A yang umum saat data tidak ada, Anda cukup menggunakan argumen .
• Pencarian gabungan (vertikal dan horizontal): Anda dapat menumpuk XLOOKUP untuk menyilangkan nilai dalam tabel di kedua dimensi, mirip dengan menggabungkan INDEX dan MATCH.
• Rentang dinamis: Jika Anda memiliki dua rentang data (beberapa tabel), Anda dapat menggabungkan keduanya dalam rumus dan XLOOKUP akan mencari keduanya secara bersamaan.
• Pencarian wildcard parsial: Temukan data berdasarkan kecocokan sebagian menggunakan *, ? atau ~ dalam nilai pencarian Anda.

Perbandingan argumen cepat: VLOOKUP vs. XLOOKUP

VLOOKUP selalu membutuhkan nomor kolom di mana merupakan data yang akan dikembalikan dan hanya mengizinkan pencarian pada kolom pertama rentang. Alih-alih, XLOOKUP tidak bergantung pada urutan kolom atau posisinya, cukup tentukan rentang yang tepat untuk mencari dan mengembalikan.

Selain itu, XLOOKUP mendukung array dinamis.: Saat returns_array mengelompokkan beberapa kolom, Anda dapat mengembalikan serangkaian data yang bersebelahan dalam beberapa sel sekaligus, tanpa harus mengulang rumus.

Contoh argumen dan kasus terpecahkan

Bayangkan tabel negara dengan data populasi, harapan hidup, dan kode. Jika Anda hanya menginginkan populasi, Anda mendefinisikan return_matrix sebagai kolom populasi. Jika Anda menginginkan beberapa data, Anda mendefinisikan returns_array sebagai beberapa kolom. Dengan menempatkan rumus di sel G6, data di kolom H6 dan I6 juga akan terisi secara dinamis.

Apa yang terjadi jika Anda mencari data yang tidak ada? Dengan VLOOKUP, jika Anda mencari Uruguay (dan tidak ada dalam daftar) dan Anda tidak menggunakan YA. KESALAHAN, Anda akan melihat kesalahan #N/A. Dengan XLOOKUP, Anda memutuskan pesan mana yang muncul, jumlah atau hasil yang Anda inginkan.

Lebih dari sekadar pencarian pasti: mode pencocokan dan kegunaannya

• Untuk menemukan nilai terkecil berikutnya, gunakan -1. Misalnya: Mencari 46.520 dalam tabel diskon, jika tidak ada, akan mengembalikan diskon untuk jumlah terendah berikutnya.
• Untuk nilai yang lebih tinggi berikutnya, gunakan 1. Berguna jika Anda mencari rentang progresif.
• Untuk menggunakan karakter pengganti, gunakan 2. Misalnya, cari “*selatan*” dan Anda akan mendapatkan “Selatan”, “Tenggara”, “Utara-selatan”…

Bagaimana cara kerja wildcard di XLOOKUP?

Tanda bintang (*) menggantikan sejumlah karakter apa pun. Jika Anda mencari “*timur”, Anda akan melihat “Timur”, “Tenggara”, “Timur Laut”…

Tanda tanya (?) hanya menggantikan satu karakter. Apakah Anda memiliki pertanyaan tentang "María" atau "Mario"? Cobalah "María" dan keduanya akan ditemukan.

Tilde (~) menggantikan nilai wildcard. Jika Anda mencari "Bagaimana~?" tidak akan menemukan "Bagaimana", tetapi hanya "Bagaimana?".

Apa pencarian biner yang ditawarkan XLOOKUP?

Ketika Anda memiliki daftar besar yang diurutkan, pencarian biner memungkinkan Anda menemukan data dengan cepat.. Bayangkan sebuah buku setebal 100 halaman: dengan biner Anda membaginya menjadi dua, memilih blok tempat buku itu berada, dan mengurangi jumlah pemeriksaan. Tentu saja, Anda perlu rentang yang diurutkan dengan baik (naik atau turun tergantung pada mode yang dipilih), jika tidak, hasilnya tidak akan memiliki keandalan.

Bisakah saya mencari beberapa tabel atau berdasarkan lebih dari satu kriteria dalam satu waktu?

XLOOKUP memungkinkan Anda untuk mencari beberapa rentang yang digabungkan sekaligus menggunakan sintaksis yang sesuai. Misalnya, jika Anda memiliki dua tabel negara, lookup_array dapat berupa kedua area yang dipisahkan oleh titik dua, seperti halnya return_array. Semua dalam formula yang sama dan tanpa usaha ekstra.

Bagaimana jika saya ingin melakukan referensi silang pada beberapa kriteria? Di sinilah XLOOKUP bersarang berperan: Anda dapat menggunakan satu pencarian awal untuk menentukan rentang kolom dan pencarian lainnya untuk baris, yang mengembalikan data pasti pada perpotongan, seperti versi lanjutan dari INDEX + MATCH.

Seberapa banyak saya dapat menyesuaikan hasil XLOOKUP?

Keuntungan XLOOKUP yang jarang diketahui adalah Anda dapat mengembalikan beberapa jenis hasil jika terjadi kesalahan.. Anda dapat menampilkan teks khusus, angka, atau membiarkan sel kosong tergantung kebutuhan Anda. Selain itu, saat kami mengembalikan beberapa kolom, hasilnya menempati sel yang berdekatan, yang tidak dapat diedit satu per satu dari Excel, tetapi dapat dimodifikasi dengan menghapus rumus utama.