Ringkasan: XLOOKUP dan INDEX-MATCH dipakai untuk mengambil nilai dari satu kolom berdasarkan kunci yang dicocokkan di kolom lain, sehingga dua tabel bisa digabung tanpa menyalin manual. Bagi data analyst pemula, kedua rumus ini menjadi jembatan pertama antara tabel-tabel yang terpisah.
Tujuan belajar
- Menulis XLOOKUP dengan argumen wajib dan opsional, termasuk if_not_found.
- Menulis INDEX-MATCH dan menjelaskan mengapa rumus ini tetap berguna di versi Excel lama.
- Memilih salah satu rumus berdasarkan versi Excel dan kebutuhan file.
Konsep inti
Waktu pertama kali menggabungkan dua tabel, saya menyalin kolom harga dari satu sheet ke sheet lain secara manual. Hasilnya cepat tidak sinkron begitu data sumbernya berubah. Dari situ saya mulai belajar rumus pencarian, dan dua yang paling sering dibandingkan adalah XLOOKUP dan INDEX-MATCH.

XLOOKUP mencari lookup_value di dalam lookup_array, lalu mengembalikan nilai dari return_array pada baris yang sama. Sintaksnya: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Secara bawaan, fungsi ini mencari kecocokan persis dan mengembalikan item pertama yang cocok (Microsoft Support, n.d.-a). Yang paling terasa bagi saya, kolom hasil boleh berada di sisi mana pun dari kolom kunci. VLOOKUP hanya bisa mencari dari kiri ke kanan (Microsoft Support, n.d.-a).
Tiga argumen terakhir bersifat opsional:
- if_not_found: teks yang ditampilkan bila tidak ada kecocokan. Jika dihilangkan, hasilnya #N/A.
- match_mode: 0 untuk kecocokan persis (bawaan), -1 untuk persis atau nilai lebih kecil berikutnya, 1 untuk persis atau nilai lebih besar berikutnya, dan 2 untuk pencocokan wildcard.
- search_mode: 1 untuk mencari dari awal (bawaan) dan -1 untuk mencari dari akhir.
Satu catatan soal versi. Halaman Microsoft menyebut XLOOKUP tidak tersedia di Excel 2016 dan Excel 2019, meskipun daftar Applies To di halaman yang sama juga memuat Excel 2019. Saya mengikuti catatannya. Karena itu, sebelum membagikan file, periksa dulu versi Excel rekan yang akan membukanya (Microsoft Support, n.d.-a).
Sebelum XLOOKUP tersedia, banyak analis memakai INDEX bersama MATCH. INDEX mengembalikan nilai pada posisi tertentu di sebuah rentang (Microsoft Support, n.d.-d), sedangkan MATCH mengembalikan posisi dari nilai yang dicari. Keduanya digabung menjadi =INDEX(return_array, MATCH(lookup_value, lookup_array, 0)). Saya selalu menulis 0 secara eksplisit, karena nilai bawaan match_type bukan kecocokan persis. Jika memakai -1, daftar pencarian harus diurutkan menurun (Microsoft Support, n.d.-b).
Pada Microsoft 365 terbaru, rumus ini cukup ditekan Enter. Di versi lama, kombinasi INDEX-MATCH kadang harus dimasukkan sebagai array formula dengan Ctrl+Shift+Enter (Microsoft Support, n.d.-c). Keunggulannya dibanding VLOOKUP sama seperti XLOOKUP: ia bisa mencari ke kiri, dan tidak rusak ketika kolom disisipkan di tengah tabel (DataCamp, n.d.).
Diagram berikut merangkum alur satu pencarian, yang berlaku untuk XLOOKUP maupun INDEX-MATCH.
Contoh kode
Contoh berikut memakai data sintetis yang saya buat sendiri: tabel pelanggan dengan empat ID dan tabel pesanan dengan tiga baris. Padanan SQL untuk pencarian banyak baris adalah JOIN. Contoh formula Excel di atas memakai pemisah koma seperti dokumentasi Microsoft. Di Excel berbahasa Indonesia, pemisah argumen biasanya titik koma.
-- SQL (PostgreSQL): padanan untuk banyak baris, mirip XLOOKUP yang dijalankan per baris
SELECT p.id_pesanan, p.total, l.nama
FROM pesanan p
LEFT JOIN pelanggan l ON l.id_pelanggan = p.id_pelanggan;
# Python (pandas)
import pandas as pd
pelanggan = pd.DataFrame({
"id_pelanggan": [1041, 1042, 1043, 1044],
"nama": ["Andi", "Rina", "Budi", "Sari"],
})
pesanan = pd.DataFrame({
"id_pesanan": [1, 2, 3],
"id_pelanggan": [1042, 1044, 1042],
"total": [150000, 80000, 95000],
})
# Mirip INDEX-MATCH: cari posisi baris pertama yang cocok, lalu ambil nilai kolom nama
posisi = pelanggan.index[pelanggan["id_pelanggan"] == 1042][0]
print(pelanggan.loc[posisi, "nama"]) # Rina
# Mirip XLOOKUP untuk banyak baris: left join; validate memastikan kunci sisi kanan unik
hasil = pesanan.merge(pelanggan, on="id_pelanggan", how="left", validate="many_to_one")
print(hasil)
# R (tidyverse)
library(tidyverse)
pelanggan <- tibble(
id_pelanggan = c(1041, 1042, 1043, 1044),
nama = c("Andi", "Rina", "Budi", "Sari")
)
pesanan <- tibble(
id_pesanan = 1:3,
id_pelanggan = c(1042, 1044, 1042),
total = c(150000, 80000, 95000)
)
# Mirip INDEX-MATCH untuk satu nilai: ambil nama untuk ID 1042
pelanggan |>
filter(id_pelanggan == 1042) |>
pull(nama)
# Mirip XLOOKUP untuk banyak baris: left_join dengan relasi yang diperiksa
pesanan |>
left_join(pelanggan, by = "id_pelanggan", relationship = "many-to-one")
Contoh kecil
Contoh kecil. Dengan tabel A2:A5 berisi ID 1041 sampai 1044 dan B2:B5 berisi nama Andi, Rina, Budi, dan Sari, rumus =XLOOKUP(1042, A2:A5, B2:B5, “Tidak terdaftar”) menghasilkan Rina. Untuk ID 1050 yang tidak ada, hasilnya teks “Tidak terdaftar”, bukan #N/A. Teks itu memberi tahu pembaca laporan bahwa ID tersebut memang belum ada, bukan bahwa rumusnya rusak.
Kesalahan yang sering terjadi
Waspadai tiga hal ini:
- Membiarkan #N/A muncul tanpa if_not_found, lalu menutupinya dengan IFERROR tanpa memeriksa penyebabnya. Kadang kuncinya memang berbeda format, misalnya angka 1042 di satu tabel dan teks “1042” di tabel lain. Saya pernah mengira rumusnya salah, padahal kolom ID di satu tabel berisi teks.
- Mengira rumus akan menemukan duplikat. XLOOKUP dan MATCH berhenti pada kecocokan pertama, sehingga ID yang muncul dua kali di tabel referensi diam-diam hanya menampilkan satu hasil. Periksa duplikat sebelum mencari.
- Tidak mengunci rentang tabel referensi saat rumus disalin. Gunakan tanda dolar, misalnya $A$2:$A$5, seperti yang dibahas pada Hari 8.
Latihan
Gunakan data berikut: A2:A4 berisi 2001, 2002, dan 2003, sedangkan B2:B4 berisi Dedi, Ani, dan Yoga.
- Tulis rumus XLOOKUP untuk mencari nama dengan ID 2002. Jika tidak ditemukan, tampilkan “Tidak ada”.
- Tulis versi INDEX-MATCH untuk soal 1 dengan pencocokan persis.
- Jika ID yang dicari adalah 2009, apa hasil kedua rumus, dan mengapa hasilnya berbeda?
Kunci jawaban singkat: (1) =XLOOKUP(2002, A2:A4, B2:B4, “Tidak ada”) menghasilkan Ani. (2) =INDEX(B2:B4, MATCH(2002, A2:A4, 0)) juga menghasilkan Ani. (3) XLOOKUP menampilkan “Tidak ada” karena argumen if_not_found sudah diisi. INDEX-MATCH menampilkan #N/A karena tidak punya argumen itu, sehingga perlu dibungkus IFNA bila ingin teks yang sama.
Ringkasan
XLOOKUP adalah cara paling ringkas di Excel modern, sedangkan INDEX-MATCH tetap berguna untuk file yang harus dibuka di versi lama. Dalam kedua rumus, pastikan jenis data kunci sama di kedua tabel dan periksa duplikatnya. Besok, pada Hari 11, saya mulai SQL dasar: SELECT, WHERE, ORDER BY, dan LIMIT, yang dijadwalkan terbit 21 November 2026 pukul 09.00 WIB.
Sumber bacaan
DataCamp. (n.d.). Index Match Excel: A better way to look up data. https://www.datacamp.com/tutorial/index-match-excel
Microsoft Support. (n.d.-a). XLOOKUP function. https://support.microsoft.com/en-au/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
Microsoft Support. (n.d.-b). How to correct a #N/A error in INDEX/MATCH functions. https://support.microsoft.com/en-US/Excel/how-to-correct-a-n-a-error-in-index-match-functions
Microsoft Support. (n.d.-c). How to correct a #VALUE! error in INDEX/MATCH functions. https://support.microsoft.com/en-US/excel/how-to-correct-a-value-error-in-index-match-functions
Microsoft Support. (n.d.-d). INDEX function. https://support.microsoft.com/article/a5dcf0dd-996d-40a4-a822-b56b061328bd
Catatan data: seluruh contoh angka pada tulisan ini adalah data sintetis yang saya buat sendiri. Tidak ada data pasien atau data pribadi nyata di dalamnya.
Navigasi seri Belajar Analisis Data
- Berikutnya: Hari 11, SQL: SELECT, WHERE, ORDER BY, dan LIMIT (terbit 21 November 2026 pukul 09.00 WIB)
- Semua tulisan seri Belajar Analisis Data: https://legawa.com/tag/data-analyst/
- Sebelumnya: Hari 9, PivotTable dan Ringkasan Data



Tinggalkan Balasan