Cara Menggunakan Fungsi XLOOKUP di Microsoft Excel
XLOOKUP baru Excel akan menggantikan VLOOKUP, memberikan pengganti yang kuat untuk salah satu fungsi Excel yang paling populer. Fungsi baru ini memecahkan beberapa batasan VLOOKUP dan memiliki fungsionalitas tambahan. Inilah yang perlu Anda ketahui.
Apa itu XLOOKUP?
Fungsi XLOOKUP baru memiliki solusi untuk beberapa batasan terbesar VLOOKUP. Plus, itu juga menggantikan HLOOKUP. Misalnya, XLOOKUP dapat melihat ke kiri, default untuk kecocokan persis, dan memungkinkan Anda menentukan rentang sel alih-alih nomor kolom. VLOOKUP tidak semudah ini digunakan atau serbaguna. Kami akan menunjukkan cara kerjanya.Untuk saat ini, XLOOKUP hanya tersedia untuk pengguna di program Insiders. Siapa pun dapat bergabung dengan program Orang Dalam untuk mengakses fitur Excel terbaru segera setelah tersedia. Microsoft akan segera mulai meluncurkannya ke semua pengguna Office 365.
Cara Menggunakan Fungsi XLOOKUP
Mari selami contoh XLOOKUP yang sedang beraksi. Ambil contoh data di bawah ini. Kami ingin mengembalikan departemen dari kolom F untuk setiap ID di kolom A.
Ini adalah contoh pencarian pencocokan persis klasik. Fungsi XLOOKUP hanya membutuhkan tiga informasi.
Gambar di bawah ini menunjukkan XLOOKUP dengan enam argumen, tetapi hanya tiga argumen pertama yang diperlukan untuk pencocokan tepat. Jadi mari kita fokus pada mereka:
Lookup_value: Apa yang Anda cari.
Lookup_array: Tempat mencarinya.
Return_array: rentang yang berisi nilai untuk kembali.
Rumus berikut akan berfungsi untuk contoh ini: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ F $ 2: $ F $ 8)
Mari sekarang jelajahi beberapa keunggulan yang dimiliki XLOOKUP dibandingkan VLOOKUP di sini.
Tidak Ada Lagi Nomor Indeks Kolom
Argumen ketiga VLOOKUP yang terkenal adalah untuk menentukan nomor kolom dari informasi yang akan dikembalikan dari array tabel. Ini bukan lagi masalah karena XLOOKUP memungkinkan Anda memilih rentang untuk kembali dari (kolom F dalam contoh ini).
Dan jangan lupa, XLOOKUP dapat melihat data yang tersisa dari sel yang dipilih, tidak seperti VLOOKUP. Lebih lanjut tentang ini di bawah ini.
Anda juga tidak lagi memiliki masalah rumus yang rusak ketika kolom baru dimasukkan. Jika itu terjadi di spreadsheet Anda, rentang pengembalian akan disesuaikan secara otomatis.
Pencocokan Tepat adalah Default
Selalu membingungkan ketika mempelajari VLOOKUP mengapa Anda harus menentukan kecocokan persis yang diinginkan.Untungnya, XLOOKUP default ke pencocokan tepat — alasan yang jauh lebih umum untuk menggunakan rumus pencarian). Ini mengurangi kebutuhan untuk menjawab argumen kelima dan memastikan lebih sedikit kesalahan oleh pengguna yang baru menggunakan rumus ini.
Jadi singkatnya, XLOOKUP mengajukan lebih sedikit pertanyaan daripada VLOOKUP, lebih ramah pengguna, dan juga lebih tahan lama.
XLOOKUP dapat Melihat ke Kiri
Mampu memilih rentang pencarian membuat XLOOKUP lebih fleksibel daripada VLOOKUP. Dengan XLOOKUP, urutan kolom tabel tidak menjadi masalah.VLOOKUP dibatasi dengan mencari kolom paling kiri dari sebuah tabel dan kemudian kembali dari jumlah kolom yang ditentukan ke kanan.
Dalam contoh di bawah ini, kita perlu mencari ID (kolom E) dan mengembalikan nama orang tersebut (kolom D).
Formula berikut ini dapat mencapai ini: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D $ 8)
Apa yang Harus Dilakukan Jika Tidak Ditemukan
Pengguna fungsi pencarian sangat terbiasa dengan pesan kesalahan # N / A yang menyambut mereka ketika VLOOKUP atau fungsi MATCH mereka tidak dapat menemukan apa yang dibutuhkan. Dan seringkali ada alasan logis untuk ini.Karenanya, pengguna dengan cepat meneliti cara menyembunyikan kesalahan ini karena itu tidak benar atau tidak berguna. Dan, tentu saja, ada cara untuk melakukannya.
XLOOKUP hadir dengan argumen bawaan “jika tidak ditemukan” untuk menangani kesalahan tersebut. Mari kita melihatnya beraksi dengan contoh sebelumnya, tetapi dengan ID yang salah ketik.
Rumus berikut akan menampilkan teks "ID Salah" dan bukan pesan kesalahan: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D $ 8, "ID Salah")
Menggunakan XLOOKUP untuk Pencarian Rentang
Meskipun tidak umum seperti pencocokan persis, penggunaan rumus pencarian yang sangat efektif adalah untuk mencari nilai dalam rentang. Ambil contoh berikut. Kami ingin mengembalikan diskon tergantung pada jumlah yang dibelanjakan.Kali ini kami tidak mencari nilai tertentu. Kita perlu tahu di mana nilai-nilai di kolom B termasuk dalam rentang di kolom E. Itu akan menentukan diskon yang didapat.
XLOOKUP memiliki argumen kelima opsional (ingat, default untuk kecocokan persis) bernama mode kecocokan.
Anda dapat melihat bahwa XLOOKUP memiliki kemampuan yang lebih besar dengan perkiraan kecocokan dari pada VLOOKUP.
Ada opsi untuk menemukan kecocokan terdekat lebih kecil dari (-1) atau terdekat lebih besar dari (1) nilai yang dicari. Ada juga opsi untuk menggunakan karakter wildcard (2) seperti? atau *. Pengaturan ini tidak aktif secara default seperti pada VLOOKUP.
Rumus dalam contoh ini menghasilkan nilai terdekat yang kurang dari nilai yang dicari jika kecocokan yang tepat tidak ditemukan: = XLOOKUP (B2, $ E $ 3: $ E $ 7, $ F $ 3: $ F $ 7 ,, - 1)
Namun, ada kesalahan dalam sel C7 di mana kesalahan # N / A dikembalikan (argumen 'jika tidak ditemukan' tidak digunakan). Ini seharusnya mengembalikan diskon 0% karena pengeluaran 64 tidak mencapai kriteria untuk diskon apa pun.
Keuntungan lain dari fungsi XLOOKUP adalah tidak memerlukan rentang pencarian dalam urutan menaik seperti halnya VLOOKUP.
Masukkan baris baru di bagian bawah tabel pencarian dan kemudian buka formula. Perluas rentang yang digunakan dengan mengklik dan menyeret sudut.
Formula segera memperbaiki kesalahan. Ini bukan masalah dengan memiliki "0" di bagian bawah kisaran.
Secara pribadi, saya masih akan mengurutkan tabel berdasarkan kolom pencarian. Memiliki "0" di bagian bawah akan membuatku gila. Tetapi fakta bahwa formula itu tidak pecah adalah brilian.
XLOOKUP Mengganti Fungsi HLOOKUP Juga
Seperti yang disebutkan, fungsi XLOOKUP juga ada di sini untuk menggantikan HLOOKUP. Satu fungsi untuk menggantikan dua. Luar biasa!Fungsi HLOOKUP adalah pencarian horisontal, digunakan untuk mencari di sepanjang baris.
Tidak dikenal sebagai saudara VLOOKUP, tetapi berguna untuk contoh seperti di bawah ini di mana header berada di kolom A, dan datanya ada di sepanjang baris 4 dan 5.
XLOOKUP dapat melihat di kedua kolom arah - bawah dan juga di sepanjang baris. Kita tidak lagi membutuhkan dua fungsi yang berbeda.
Dalam contoh ini, rumus ini digunakan untuk mengembalikan nilai penjualan yang berkaitan dengan nama di sel A2. Tampaknya di sepanjang baris 4 untuk menemukan nama, dan mengembalikan nilai dari baris 5: = XLOOKUP (A2, B4: E4, B5: E5)
XLOOKUP Dapat Melihat Dari Bawah ke Atas
Biasanya, Anda perlu memburu daftar untuk menemukan kemunculan nilai pertama (seringkali hanya). XLOOKUP memiliki argumen keenam bernama mode pencarian. Ini memungkinkan kami untuk mengalihkan pencarian untuk memulai dari bawah dan mencari daftar untuk menemukan kemunculan nilai terakhir sebagai gantinya.Dalam contoh di bawah ini, kami ingin menemukan tingkat stok untuk setiap produk di kolom A.
Tabel pencarian dalam urutan tanggal, dan ada beberapa pemeriksaan stok per produk. Kami ingin mengembalikan tingkat stok sejak terakhir kali diperiksa (kejadian terakhir dari ID Produk).
Argumen keenam fungsi XLOOKUP menyediakan empat opsi. Kami tertarik untuk menggunakan opsi "Cari terakhir-ke-pertama".
Rumus lengkap ditampilkan di sini: = XLOOKUP (A2, $ E $ 2: $ E $ 9, $ F $ 2: $ F $ 9 ,,, - 1)
Dalam formula ini, argumen keempat dan kelima diabaikan. Ini opsional, dan kami ingin default dari kecocokan yang tepat.
Pembulatan
Fungsi XLOOKUP adalah penerus yang sangat ditunggu-tunggu untuk fungsi VLOOKUP dan HLOOKUP.Berbagai contoh digunakan dalam artikel ini untuk menunjukkan keunggulan XLOOKUP. Salah satunya adalah bahwa XLOOKUP dapat digunakan di seluruh lembar, buku kerja dan juga dengan tabel. Contoh-contoh itu dibuat sederhana dalam artikel untuk membantu pemahaman kita.
Karena array dinamis yang diperkenalkan ke Excel segera, itu juga dapat mengembalikan berbagai nilai. Ini jelas sesuatu yang perlu ditelusuri lebih lanjut.
Hari-hari VLOOKUP diberi nomor. XLOOKUP ada di sini dan akan segera menjadi formula pencarian de facto.