=XLOOKUP(F2,A2:A6,C2:C6) mencari nilai di F2 pada A2:A6 dan mengembalikan nilai dari baris yang sama di C2:C6. Pencocokannya persis sebagai default, kolom pencariannya bisa berada di mana saja, dan rumus ini butuh Excel 2021 atau Microsoft 365 (di Excel 2019 dan yang lebih lama pakai INDEX dan MATCH). Ketik produk lain di F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Bread | $2.40 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Klik G2: range pencarian dan range kembali ditandai secara terpisah. Ubah C2:C6 menjadi B2:B6 dan G2 mengembalikan kategorinya. Tidak ada nomor kolom yang perlu dihitung, jadi menyisipkan kolom di antara A dan C tidak merusak rumusnya: Excel memindahkan kedua range.
Sintaks XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argumen | Fungsinya | Default |
|---|---|---|
lookup_value | Nilai yang dicari. | wajib |
lookup_array | Kolom (atau baris) tempat mencari. | wajib |
return_array | Kolom, baris atau blok tempat hasil diambil. Tingginya sama dengan lookup_array. | wajib |
if_not_found | Yang ditampilkan jika tidak ada yang cocok. | #N/A |
match_mode | 0 persis, -1 persis atau lebih kecil berikutnya, 1 persis atau lebih besar berikutnya, 2 wildcard. | 0 |
search_mode | 1 dari pertama ke terakhir, -1 dari terakhir ke pertama, 2 dan -2 pencarian biner pada data terurut. | 1 |
Hanya tiga argumen pertama yang wajib. Untuk melewati sebuah argumen opsional dan mengisi argumen setelahnya, biarkan kosong di antara koma: =XLOOKUP(F2,A2:A6,C2:C6,,0,-1) mengisi search_mode dan membiarkan if_not_found memakai default-nya.
Mengembalikan beberapa kolom sekaligus
Berikan XLOOKUP range kembali yang lebarnya beberapa kolom dan seluruh barisnya akan dikembalikan. Hasilnya tumpah ke sel-sel di samping rumus.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Category | Price | Stock |
| 2 | Apple | Fruit | $1.20 | 40 |
| 3 | Pear | Fruit | $1.50 | 25 |
| 4 | Carrot | Vegetable | $0.80 | 60 |
| 5 | Bread | Bakery | $2.40 | 15 |
| 6 | Milk | Dairy | $1.10 | 30 |
| 7 | Look for | Carrot | ||
| 8 | Result | Vegetable | $0.80 | 60 |
Satu rumus di B8 mengisi B8:D8 dengan Vegetable, $0,80 dan 60. Ketik sesuatu di C8 dan B8 menampilkan #SPILL!, karena hasilnya tidak punya ruang; hapus isinya dan hasilnya kembali. Untuk mengembalikan kolom-kolom dalam urutan lain, bungkus range kembalinya dengan CHOOSECOLS: =XLOOKUP(B7,A2:A6,CHOOSECOLS(B2:D6,3,1)) memberi Stock, lalu Category.
XLOOKUP ke kiri, dan pesan jika tidak ada yang cocok
Kolom pencariannya tidak harus berada di depan. Di sini XLOOKUP mencari harga di kolom C dan mengembalikan nama produk dari kolom A, yang tidak bisa dilakukan VLOOKUP. Argumen keempat menentukan apa yang ditampilkan jika tidak ada produk dengan harga itu.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Price | Product | |
| 2 | Apple | Fruit | $1.20 | 40 | $2.40 | Bread | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
$2,40 mengembalikan Bread. Ubah F2 menjadi 3 dan G2 menampilkan "No product", bukan #N/A. "" sebagai argumen keempat menampilkan sel yang terlihat kosong. if_not_found hanya menangani kasus "tidak ditemukan": range kembali dengan tinggi yang salah tetap menghasilkan #VALUE!, dan memang itulah yang perlu kamu lihat.
Menemukan kecocokan terakhir
XLOOKUP mengembalikan kecocokan pertama dari atas. Isi search_mode, argumen keenam, dengan -1 dan pencariannya berjalan dari bawah, jadi yang dikembalikan adalah kecocokan terakhir: pesanan terbaru, harga paling baru, status terakhir.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | First | Last | |
| 2 | 2026-03-02 | Ben | 120 | Ben | 120 | 60 | |
| 3 | 2026-03-05 | Ana | 80 | ||||
| 4 | 2026-03-09 | Ben | 45 | ||||
| 5 | 2026-03-12 | Cara | 200 | ||||
| 6 | 2026-03-20 | Ben | 60 | ||||
| 7 | 2026-03-24 | Ana | 95 |
Pesanan pertama Ben bernilai 120 dan yang terakhir 60. Ubah E2 menjadi Ana: 80 dan 95. Cara ini mengandalkan baris-barisnya yang terurut menurut tanggal. Jika tidak, cari tanggal terbaru pelanggan itu: =XLOOKUP(1,(B2:B7=E2)*(A2:A7=MAXIFS(A2:A7,B2:B7,E2)),C2:C7).
Pencocokan perkiraan: lebih kecil atau lebih besar berikutnya
match_mode -1 mengembalikan kecocokan persis atau, jika tidak ada, nilai lebih kecil berikutnya. Itulah aturan untuk rentang bertingkat: tingkat komisi, golongan pajak, nilai huruf. Berbeda dengan VLOOKUP dengan TRUE, tabelnya tidak harus diurutkan. Rentang di bawah ini sengaja tidak diurutkan.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 5000 | 5% | Ana | 750 | 0% | |
| 3 | 0 | 0% | Ben | 4,200 | 3% | |
| 4 | 10000 | 8% | Cara | 5,000 | 5% | |
| 5 | 1000 | 3% | Dev | 12,500 | 8% |
Angka 4.200 milik Ben berada di antara 1.000 dan 5.000, jadi ia mendapat 3% dari tingkat 1.000. Angka 5.000 milik Cara cocok persis, 5%. match_mode 1 bekerja ke arah sebaliknya, persis atau lebih besar berikutnya, yang menjawab "kotak terkecil yang muat" atau "jadwal pengiriman berikutnya": =XLOOKUP(18,{5;12;25;50},{"S";"M";"L";"XL"},,1) mengembalikan L.
XLOOKUP dengan wildcard
match_mode 2 mengubah * (karakter apa pun) dan ? (satu karakter) menjadi wildcard. Tanpa itu, XLOOKUP mencari karakternya apa adanya, kebalikan dari VLOOKUP (yang pencocokan persisnya menerima wildcard), dan itulah alasan yang biasa kenapa XLOOKUP dengan wildcard menghasilkan #N/A atau teks if_not_found-nya:
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None") None: no product is named *coffee*
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None",2) 2.9, the price of Iced coffee
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
"coffee" lebih dulu menemukan Iced coffee, 8,50. Seperti semua pencarian di Excel, pencocokannya tidak membedakan huruf besar dan kecil. Untuk mencari tanda bintang atau tanda tanya yang sebenarnya dalam match_mode 2, taruh tilde di depannya: "~*".
XLOOKUP dua arah
XLOOKUP yang mengembalikan satu baris utuh bisa menjadi range kembali bagi XLOOKUP kedua. XLOOKUP bagian dalam memilih baris berdasarkan wilayah, XLOOKUP bagian luar memilih kolom bulan dari baris itu.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 4,200 | 3,900 | 4,800 |
| 3 | South | 3,100 | 3,600 | 3,300 |
| 4 | East | 5,200 | 4,700 | 5,600 |
| 5 | West | 2,800 | 3,000 | 3,400 |
| 6 | Region | South | ||
| 7 | Month | Feb | ||
| 8 | Sales | 3,600 |
XLOOKUP(B6,A2:A5,B2:D5) mengembalikan baris South, yaitu 3100, 3600 dan 3300. XLOOKUP bagian luar menemukan Feb di B1:D1 dan mengambil nilai yang sesuai dari baris itu: 3.600. Pilih wilayah dan bulan lain di B6 dan B7. Versi INDEX dan MATCH dari pencarian yang sama ada di halaman INDEX dan MATCH.
XLOOKUP di Excel versi lama dan Google Sheets
XLOOKUP ada di Excel 2021, Excel 2024, Microsoft 365, Excel for the web dan aplikasi mobile-nya. Jika kamu membuka file yang memakainya di Excel 2019 atau yang lebih lama, rumus-rumusnya menampilkan #NAME? begitu dihitung ulang. Jika file-nya harus bisa dipakai di mana saja, tulis pencariannya dengan INDEX dan MATCH, yang dipahami semua versi:
=XLOOKUP(F2, A2:A6, C2:C6, "Not found")
=IFNA(INDEX(C2:C6, MATCH(F2, A2:A6, 0)), "Not found")
Google Sheets sudah punya XLOOKUP sejak 2022, dengan argumen yang sama. Untuk perbandingan perbedaannya secara berdampingan, lihat VLOOKUP vs XLOOKUP. Untuk mencocokkan dua kolom sekaligus (produk dan ukuran, nama dan tanggal), pola =XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6) dijelaskan di halaman pencarian dengan banyak kriteria.
Latihan: sebuah harga, atau "Not found"
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | ||
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Giliranmu: Di G2, kembalikan harga produk di F2, atau teks Not found jika produknya tidak ada di daftar.
Latihan: diskon berdasarkan nilai pesanan
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order from | Discount | Order | Discount | |
| 2 | $0 | 0% | $320 | ||
| 3 | $100 | 5% | |||
| 4 | $250 | 10% | |||
| 5 | $500 | 15% |
Giliranmu: Setiap diskon berlaku mulai dari nilai pesanannya ke atas. Di E2, pakai XLOOKUP untuk mengembalikan diskon bagi nilai pesanan di D2.
Pertanyaan yang sering diajukan
Bagaimana cara memakai XLOOKUP di Excel?
Berikan tiga argumen: yang dicari, kolom tempat mencari, dan kolom yang dikembalikan. =XLOOKUP("Pear",A2:A6,C2:C6) mencari Pear di A2:A6 dan mengembalikan nilai dari baris yang sama di C2:C6. Pencocokannya persis kecuali kamu menentukan lain.
Versi Excel mana yang punya XLOOKUP?
Excel 2021, Excel 2024, Microsoft 365 dan Excel for the web. Di Excel 2019 dan yang lebih lama, rumusnya menampilkan #NAME?; di sana pakai =INDEX(C2:C6,MATCH(F2,A2:A6,0)). Google Sheets juga punya XLOOKUP.
Bagaimana cara membuat XLOOKUP menampilkan sel kosong atau teks, bukan #N/A?
Pakai argumen keempat, if_not_found: =XLOOKUP(F2,A2:A6,C2:C6,"Not found"), atau "" untuk sel yang terlihat kosong. Argumen ini hanya menggantikan kasus tidak ditemukan; error lainnya tetap tampil.
Bagaimana cara menemukan kecocokan terakhir dengan XLOOKUP?
Isi argumen keenam, search_mode, dengan -1 agar pencariannya berjalan dari bawah ke atas: =XLOOKUP("Ben",B2:B7,C2:C7,,0,-1) mengembalikan transaksi terakhir Ben, bukan yang pertama.
Apakah XLOOKUP bisa mengembalikan lebih dari satu kolom?
Bisa. Berikan range kembali yang lebarnya beberapa kolom, seperti =XLOOKUP(F2,A2:A6,B2:D6), dan hasilnya tumpah (spill) ke sel-sel di sampingnya. Sel tempat hasilnya tumpah harus kosong, kalau tidak Excel menampilkan #SPILL!.
