Cara Menggunakan Fungsi XLOOKUP di Microsoft Excel

XLOOKUP baru Excel akan menggantikan VLOOKUP, menyediakan 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, ini juga menggantikan HLOOKUP. Misalnya, XLOOKUP bisa melihat ke kiri, defaultnya sama persis, dan memungkinkan Anda menentukan rentang sel, bukan nomor kolom. VLOOKUP tidak mudah digunakan atau serba guna. Kami akan menunjukkan kepada Anda bagaimana semuanya bekerja.

Untuk saat ini, XLOOKUP hanya tersedia untuk pengguna di program Insiders. Siapa pun dapat bergabung dengan program Insiders untuk mengakses fitur Excel terbaru segera setelah tersedia. Microsoft akan segera meluncurkannya untuk semua pengguna Office 365.

Cara Menggunakan Fungsi XLOOKUP

Mari selami langsung dengan contoh XLOOKUP 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 tepat klasik. Fungsi XLOOKUP hanya membutuhkan tiga bagian informasi.

Gambar di bawah ini memperlihatkan 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 dikembalikan.

Rumus berikut akan berfungsi untuk contoh ini: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Sekarang mari kita jelajahi beberapa keuntungan yang dimiliki XLOOKUP dibandingkan VLOOKUP di sini.

Tidak Ada Lagi Nomor Indeks Kolom

Argumen ketiga yang terkenal dari VLOOKUP adalah untuk menentukan nomor kolom informasi yang akan dikembalikan dari larik tabel. Ini tidak lagi menjadi masalah karena XLOOKUP memungkinkan Anda untuk memilih rentang untuk kembali dari (kolom F dalam contoh ini).

Dan jangan lupa, XLOOKUP dapat melihat data di kiri sel yang dipilih, tidak seperti VLOOKUP. Lebih lanjut tentang ini di bawah.

Anda juga tidak lagi memiliki masalah rumus rusak saat kolom baru disisipkan. Jika itu terjadi di spreadsheet Anda, rentang pengembalian akan menyesuaikan secara otomatis.

Pencocokan Tepat adalah Default

Itu selalu membingungkan ketika mempelajari VLOOKUP mengapa Anda harus menentukan pencocokan tepat yang diinginkan.

Untungnya, XLOOKUP secara default menggunakan pencocokan tepat — alasan yang jauh lebih umum untuk menggunakan rumus pencarian). Ini mengurangi kebutuhan untuk menjawab argumen kelima itu dan memastikan lebih sedikit kesalahan oleh pengguna yang baru mengenal rumus.

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 serbaguna daripada VLOOKUP. Dengan XLOOKUP, urutan kolom tabel tidak menjadi masalah.

VLOOKUP dibatasi dengan mencari kolom paling kiri dari 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).

Rumus berikut dapat mencapai ini: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Apa yang Harus Dilakukan Jika Tidak Ditemukan

Pengguna fungsi pencarian sangat familiar dengan pesan kesalahan # N / A yang menyambut mereka ketika VLOOKUP atau fungsi MATCH mereka tidak dapat menemukan apa yang dibutuhkannya. Dan seringkali ada alasan logis untuk ini.

Oleh karena itu, pengguna dengan cepat meneliti cara menyembunyikan kesalahan ini karena tidak benar atau berguna. Dan, tentu saja, ada cara untuk melakukannya.

XLOOKUP dilengkapi dengan argumen "jika tidak ditemukan" bawaannya sendiri untuk menangani kesalahan tersebut. Mari kita lihat beraksi dengan contoh sebelumnya, tetapi dengan ID yang salah ketik.

Rumus berikut akan menampilkan teks "ID Salah" alih-alih pesan kesalahan: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Menggunakan XLOOKUP untuk Pencarian Rentang

Meskipun tidak umum seperti pencocokan persis, penggunaan rumus pencarian yang sangat efektif adalah 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 di kolom B termasuk dalam kisaran di kolom E. Itu akan menentukan diskon yang diperoleh.

XLOOKUP memiliki argumen kelima opsional (ingat, defaultnya sama persis) bernama mode pencocokan.

Anda dapat melihat bahwa XLOOKUP memiliki kapabilitas yang lebih besar dengan perkiraan kecocokan daripada VLOOKUP.

Ada opsi untuk menemukan kecocokan terdekat yang 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 mengembalikan yang terdekat kurang dari nilai yang dicari jika kecocokan persis tidak ditemukan: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

Namun, ada kesalahan di 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 yang dilakukan VLOOKUP.

Masukkan baris baru di bagian bawah tabel pencarian, lalu buka rumusnya. Perluas rentang yang digunakan dengan mengklik dan menyeret sudut.

Rumus segera mengoreksi kesalahan. Tidak masalah jika memiliki angka "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 rusak itu brilian.

XLOOKUP Juga Menggantikan Fungsi HLOOKUP

Seperti yang disebutkan, fungsi XLOOKUP juga ada di sini untuk menggantikan HLOOKUP. Satu fungsi untuk menggantikan dua. Luar biasa!

Fungsi HLOOKUP adalah pencarian horizontal, digunakan untuk mencari di sepanjang baris.

Tidak terlalu dikenal sebagai saudara kandungnya VLOOKUP, tetapi berguna untuk contoh seperti di bawah ini di mana header berada di kolom A, dan datanya berada di sepanjang baris 4 dan 5.

XLOOKUP dapat melihat di kedua arah - kolom bawah dan juga sepanjang baris. Kita tidak lagi membutuhkan dua fungsi yang berbeda.

Dalam contoh ini, rumus digunakan untuk mengembalikan nilai penjualan yang berkaitan dengan nama di sel A2. Itu terlihat 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 mencari daftar untuk menemukan kemunculan pertama (sering kali hanya) dari suatu nilai. XLOOKUP memiliki argumen keenam bernama mode pencarian. Ini memungkinkan kita untuk mengalihkan pencarian untuk memulai dari bawah dan mencari daftar untuk menemukan kemunculan terakhir dari suatu nilai.

Dalam contoh di bawah ini, kami ingin mencari tingkat persediaan untuk setiap produk di kolom A.

Tabel pemeta dalam urutan tanggal, dan ada beberapa pemeriksaan stok per produk. Kami ingin mengembalikan level stok dari terakhir kali diperiksa (kemunculan terakhir dari ID Produk).

Argumen keenam dari fungsi XLOOKUP menyediakan empat opsi. Kami tertarik untuk menggunakan opsi "Telusuri dari yang terakhir ke yang pertama".

Rumus lengkapnya ditampilkan di sini: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

Dalam rumus ini, argumen keempat dan kelima diabaikan. Ini opsional, dan kami ingin default dari pencocokan tepat.

Pembulatan

Fungsi XLOOKUP adalah penerus yang ditunggu-tunggu untuk fungsi VLOOKUP dan HLOOKUP.

Berbagai contoh digunakan dalam artikel ini untuk mendemonstrasikan keunggulan XLOOKUP. Salah satunya adalah XLOOKUP dapat digunakan di seluruh lembar, buku kerja dan juga dengan tabel. Contoh-contohnya dibuat sederhana dalam artikel untuk membantu pemahaman kita.

Karena array dinamis segera diperkenalkan ke Excel, itu juga dapat mengembalikan berbagai nilai. Ini jelas sesuatu yang perlu ditelusuri lebih lanjut.

Hari-hari VLOOKUP diberi nomor. XLOOKUP telah hadir dan akan segera menjadi rumus pencarian de facto.