Fungsi VLOOKUP selalu menjadi salah satu fungsi Excel yang paling kuat. Ini memungkinkan Anda mencari nilai di kolom pertama tabel, dan mengembalikan nilai dari bidang di sebelah kanan. Tetapi Excel juga memiliki fungsi yang disebut XLOOKUP, yang memungkinkan Anda mencari nilai di kolom atau baris mana pun, dan mengembalikan data dari kolom lain.
Cara Kerja XLOOKUP
Fungsi XLOOKUP jauh lebih mudah digunakan daripada fungsi VLOOKUP, karena alih-alih menentukan nilai untuk kolom hasil, Anda dapat menentukan seluruh rentang.
Fungsi ini juga memungkinkan Anda untuk mencari kolom dan baris, menempatkan nilai pada sel yang berpotongan.
Parameter fungsi XLOOKUP adalah sebagai berikut:
=XLOOKUP (nilai_pencarian, array_pencarian, array_kembali, [mode_pertandingan], [mode_pencarian])
- lookup_value: Nilai yang ingin Anda cari
- lookup_array: Array (kolom) yang ingin Anda cari
- return_array: Hasil (kolom) yang ingin Anda ambil nilai dari
- match_mode (opsional): Pilih kecocokan persis (0), kecocokan persis atau nilai terkecil berikutnya (-1), atau kecocokan wildcard (2).
- search_mode (opsional): Pilih apakah akan mencari dimulai dengan item pertama di kolom (1), item terakhir di kolom (-1), pencarian biner naik (2) atau pencarian biner menurun (-2).
Berikut adalah beberapa pencarian paling umum yang dapat Anda lakukan dengan fungsi XLOOKUP.
Cara Mencari Hasil Tunggal Menggunakan XLOOKUP
Cara termudah menggunakan XLOOKUP adalah mencari satu hasil menggunakan titik data dari satu kolom.
-
Contoh spreadsheet ini adalah daftar pesanan yang dikirimkan oleh perwakilan penjualan, termasuk item, jumlah unit, biaya, dan total penjualan.
Image -
Jika Anda ingin menemukan penjualan pertama dalam daftar yang dikirimkan oleh perwakilan penjualan tertentu, Anda dapat membuat fungsi XLOOKUP yang mencari nama di kolom Rep. Fungsi akan mengembalikan hasil dari kolom Total. Fungsi XLOOKUP untuk ini adalah:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: Menunjuk ke Rep Nama sel pencarian
- C2:C44: Ini adalah kolom Rep, yang merupakan array pencarian
- G2:G33: Ini adalah kolom Total, yang merupakan array kembali
- 0: Memilih kecocokan yang tepat
- 1: Memilih kecocokan pertama dalam hasil
-
Saat Anda menekan Enter dan mengetik nama perwakilan penjualan, sel hasil Total akan menampilkan hasil pertama dalam tabel untuk perwakilan penjualan tersebut.
Image -
Jika Anda ingin mencari penjualan terbaru (karena tabel diurutkan berdasarkan tanggal dalam urutan terbalik), ubah argumen XLOOKUP terakhir ke - 1, yang akan dimulai pencarian dari sel terakhir dalam array pencarian dan memberi Anda hasil itu sebagai gantinya.
Image -
Contoh ini menunjukkan pencarian serupa yang dapat Anda lakukan dengan fungsi VLOOKUP dengan menggunakan kolom Rep sebagai kolom pertama dari tabel pencarian. Namun, XLOOKUP memungkinkan Anda mencari kolom apa pun di kedua arah. Misalnya, jika Anda ingin menemukan perwakilan penjualan yang menjual pesanan Binder pertama tahun ini, Anda akan menggunakan fungsi XLOOKUP berikut:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: Menunjuk ke sel pencarian Item
- D2:D44: Ini adalah kolom Item, yang merupakan array pencarian
- C2:C44: Ini adalah kolom Rep, yang merupakan array kembali di sebelah kiri array pencarian
- 0: Memilih kecocokan yang tepat
- 1: Memilih kecocokan pertama dalam hasil
-
Kali ini, hasilnya adalah nama perwakilan penjualan yang menjual pesanan binder pertama tahun ini.
Image
Lakukan Pencocokan Vertikal dan Horizontal dengan XLOOKUP
Kemampuan lain dari XLOOKUP yang tidak dapat dilakukan VLOOKUP adalah kemampuan untuk melakukan pencarian vertikal dan horizontal, yang berarti Anda dapat mencari item di bawah kolom, dan di sepanjang baris juga.
Fitur pencarian ganda ini merupakan pengganti yang efektif untuk fungsi Excel lainnya seperti INDEX, MATCH, atau HLOOKUP.
-
Dalam contoh spreadsheet berikut, penjualan untuk setiap perwakilan penjualan dibagi per kuartal. Jika Anda ingin melihat penjualan kuartal ketiga untuk perwakilan penjualan tertentu, tanpa fungsi XLOOKUP, pencarian seperti ini akan sulit.
Image -
Dengan fungsi XLOOKUP, pencarian seperti ini menjadi mudah. Menggunakan fungsi XLOOKUP berikut, Anda dapat mencari penjualan kuartal ketiga untuk perwakilan penjualan tertentu:
=XLOOKUP(J2, B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: Menunjuk ke sel pencarian Rep
- B2:B42: Ini adalah kolom Item, yang merupakan array pencarian kolom
- K2: Menunjuk ke sel pencarian Quarter
- C1:H1: Ini adalah array pencarian baris
- C2:H42: Ini adalah array pencarian untuk jumlah dolar di setiap kuartal
Fungsi XLOOKUP bersarang ini pertama-tama mengidentifikasi perwakilan penjualan, dan fungsi XLOOKUP berikutnya mengidentifikasi kuartal yang diinginkan. Nilai yang dikembalikan akan adalah sel tempat keduanya mencegat.
-
Hasil dari rumus ini adalah pendapatan kuartal satu untuk perwakilan dengan nama Thompson.
Image
Menggunakan Fungsi XLOOKUP
Fungsi XLOOKUP hanya tersedia untuk pelanggan Office Insider, tetapi akan segera diluncurkan ke semua pelanggan Microsoft 365.
Jika Anda ingin menguji fungsinya sendiri, Anda bisa menjadi Office Insider. Pilih File > Account, lalu pilih drop-down Office Insider untuk berlangganan.
Setelah Anda bergabung dengan program Office Insider, versi Excel yang Anda instal akan menerima semua pembaruan terbaru, dan Anda dapat mulai menggunakan fungsi XLOOKUP.
