Excel Mudah DAFTAR TRAINING
Articles

Mencari Data yang Hilang dengan XLOOKUP: Membandingkan Dua Sumber Data di Excel

04 Oktober 2026 - Syamsul Arham
Mencari Data yang Hilang dengan XLOOKUP: Membandingkan Dua Sumber Data di Excel

Mencari Data yang Hilang dengan XLOOKUP

Pernahkah Anda memiliki dua sumber data yang seharusnya identik, tetapi setelah dibandingkan ternyata jumlah datanya berbeda?

Misalnya:

  • Source 1 memiliki 5.000 data
  • Source 2 seharusnya memiliki 5.000 data
  • Tetapi ternyata Source 2 hanya memiliki 4.000 data

Pertanyaannya:

Bagaimana cara menemukan 1.000 data yang tidak ada di Source 2?

Jika datanya hanya 10 atau 20 baris, kita masih mungkin melakukan pemeriksaan secara manual.

Tetapi bagaimana jika datanya 5.000, 50.000, atau bahkan 500.000 baris?

Di sinilah XLOOKUP dapat membantu.

Memahami Masalah

Kita memiliki dua sumber data yang seharusnya berisi informasi yang sama.

Source 1

Source 1 adalah sumber data utama yang menjadi acuan.

ID Nama
EMP001Andi
EMP002Budi
EMP003Citra
EMP004Dedi
EMP005Eko
EMP006Fajar
EMP007Gita
EMP008Hadi
EMP009Indra
EMP010Joko
EMP011Kiki
EMP012Lala
EMP013Maman
EMP014Nia
EMP015Oki

Dalam contoh sederhana ini terdapat 15 data.

Source 2

Source 2 seharusnya memiliki data yang sama.

Namun ternyata beberapa data tidak terdapat di Source 2.

ID Nama
EMP001Andi
EMP002Budi
EMP003Citra
EMP005Eko
EMP006Fajar
EMP008Hadi
EMP009Indra
EMP011Kiki
EMP012Lala
EMP014Nia
EMP015Oki

Source 2 hanya memiliki 11 data.

Berarti ada:

15 − 11 = 4 data

yang tidak ditemukan di Source 2.

Tetapi kita belum tahu data mana yang hilang.

Untuk menemukannya, kita perlu membandingkan kedua sumber data.

Struktur Data Sebelum Membuat Formula

Sebelum membuat formula, jangan langsung berpikir:

"Formula XLOOKUP-nya apa?"

Pertama-tama kita perlu memahami struktur masalahnya.

Kita akan menyusun kedua sumber data dalam satu worksheet agar proses perbandingan mudah dilakukan.

ID Source 1 Nama Status ID Source 2 Nama
EMP001AndiEMP001Andi
EMP002BudiEMP002Budi
EMP003CitraEMP003Citra
EMP004DediEMP005Eko
EMP005EkoEMP006Fajar
EMP006FajarEMP008Hadi
EMP007GitaEMP009Indra
EMP008HadiEMP011Kiki
EMP009IndraEMP012Lala
EMP010JokoEMP014Nia
EMP011KikiEMP015Oki
EMP012Lala
EMP013Maman
EMP014Nia
EMP015Oki

Source 1

Source 1 berada di:

Kolom A:B

Kolom Status

Kolom Status berada di:

Kolom C

Kolom ini digunakan untuk menunjukkan apakah data dari Source 1 ditemukan di Source 2.

Source 2

Source 2 berada di:

Kolom D:E

Untuk proses pencarian, kita akan menggunakan ID sebagai dasar pencocokan.

Dengan demikian, prosesnya adalah:

Source 1
   │
   │ ID
   ▼
XLOOKUP
   │
   │ mencari ke
   ▼
Source 2
   │
   ├── ditemukan → ADA
   │
   └── tidak ditemukan → TIDAK ADA

Menentukan Key untuk Perbandingan

Sekarang kita perlu menentukan:

Data apa yang digunakan sebagai dasar pencocokan?

Dalam contoh ini kita menggunakan:

ID Employee

Contohnya:

EMP001
EMP002
EMP003
...

ID merupakan pilihan yang baik karena setiap employee memiliki ID yang unik.

Dalam kasus nyata, key bisa berupa:

  • Nomor Invoice
  • Nomor Transaksi
  • Kode Produk
  • Nomor PO
  • Employee ID
  • Nomor Pelanggan
  • Nomor Faktur

Bahkan dalam beberapa kasus, key dapat berupa gabungan beberapa kolom.

Jadi sebelum membuat formula, pastikan terlebih dahulu bahwa kita sudah menentukan key yang tepat.

Membuat Formula XLOOKUP

Sekarang kita masuk ke formulanya.

Pada sel C2, kita ingin mengetahui apakah ID pada A2 terdapat di Source 2.

Gunakan formula:

=XLOOKUP(A2,$D$2:$D$12,"ADA","TIDAK ADA")

Kemudian tekan Enter.

Hasilnya:

ADA

Karena EMP001 memang terdapat di Source 2.

Selanjutnya copy formula tersebut ke bawah sampai baris terakhir.

Memahami Formula

Formula:

=XLOOKUP(A2,$D$2:$D$12,"ADA","TIDAK ADA")

terdiri dari beberapa bagian.

A2

Ini adalah nilai yang ingin dicari.

Pada baris pertama:

EMP001

Jadi XLOOKUP akan mencari EMP001.

$D$2:$D$12

Ini adalah lookup array atau area tempat pencarian dilakukan.

Dalam contoh ini, semua ID Source 2 berada pada range tersebut:

EMP001
EMP002
EMP003
EMP005
EMP006
EMP008
EMP009
EMP011
EMP012
EMP014
EMP015

Tanda $ digunakan agar range Source 2 tetap ketika formula dicopy ke bawah.

"ADA"

Ini adalah nilai yang dikembalikan apabila ID ditemukan.

"TIDAK ADA"

Ini adalah nilai yang dikembalikan apabila ID tidak ditemukan.

Dengan demikian, formula tersebut dapat dibaca:

"Cari ID pada A2 di daftar ID Source 2. Jika ditemukan, tampilkan ADA. Jika tidak ditemukan, tampilkan TIDAK ADA."

Hasil Perbandingan

Setelah formula dicopy ke seluruh data Source 1, hasilnya menjadi:

ID Source 1 Nama Status ID Source 2 Nama
EMP001AndiADAEMP001Andi
EMP002BudiADAEMP002Budi
EMP003CitraADAEMP003Citra
EMP004DediTIDAK ADAEMP005Eko
EMP005EkoADAEMP006Fajar
EMP006FajarADAEMP008Hadi
EMP007GitaTIDAK ADAEMP009Indra
EMP008HadiADAEMP011Kiki
EMP009IndraADAEMP012Lala
EMP010JokoTIDAK ADAEMP014Nia
EMP011KikiADAEMP015Oki
EMP012LalaADA
EMP013MamanTIDAK ADA
EMP014NiaADA
EMP015OkiADA

Sekarang kita langsung dapat melihat empat data yang tidak ditemukan.

ID Nama
EMP004Dedi
EMP007Gita
EMP010Joko
EMP013Maman

Menampilkan Hanya Data yang Hilang

Jika jumlah datanya sedikit, kita bisa langsung melihat hasilnya.

Namun jika datanya 5.000 baris, akan lebih praktis jika menggunakan Filter.

Pada kolom Status, aktifkan Filter dan pilih:

TIDAK ADA

Maka Excel hanya akan menampilkan data yang tidak ditemukan di Source 2.

Dengan demikian kita tidak perlu memeriksa 5.000 baris satu per satu.

Menghitung Jumlah Data yang Hilang

Setelah mengetahui data mana yang tidak ditemukan, kita juga dapat menghitung jumlahnya.

Gunakan:

=COUNTIF(C2:C16,"TIDAK ADA")

Hasilnya:

4

Artinya terdapat 4 data Source 1 yang tidak ditemukan di Source 2.

Pada kasus sebenarnya, misalnya:

  • Source 1 = 5.000 data
  • Source 2 = 4.000 data

hasil COUNTIF mungkin menunjukkan:

1000

Artinya terdapat 1.000 data Source 1 yang tidak ditemukan di Source 2.

Bagaimana Jika Datanya 5.000 vs 4.000?

Tidak ada perubahan konsep.

Misalnya:

Source 1

A2:A5001

Source 2

D2:D4001

Maka formula pada C2 dapat menggunakan:

=XLOOKUP(A2,$D$2:$D$4001,"ADA","TIDAK ADA")

Kemudian copy formula sampai C5001.

Excel akan memeriksa seluruh 5.000 ID Source 1 terhadap 4.000 ID Source 2.

Hasil akhirnya akan menunjukkan data mana yang:

ADA

dan mana yang:

TIDAK ADA

Jangan Hanya Membandingkan Jumlah Baris

Ada satu hal penting yang perlu diperhatikan.

Jangan hanya membandingkan jumlah data.

Misalnya:

Source 1 = 5.000 data
Source 2 = 5.000 data

Apakah berarti kedua sumber data pasti sama?

Belum tentu.

Bisa saja:

  • 100 data Source 1 tidak ada di Source 2
  • tetapi Source 2 memiliki 100 data lain yang tidak ada di Source 1

Jumlah akhirnya tetap:

Source 1 = 5.000
Source 2 = 5.000

Padahal isi datanya berbeda.

Karena itu, jumlah baris hanya merupakan indikator awal.

Untuk memastikan apakah kedua sumber data benar-benar sama, kita perlu melakukan matching berdasarkan key.

XLOOKUP untuk Data Reconciliation

Dari contoh sederhana ini kita dapat melihat bahwa XLOOKUP bukan hanya digunakan untuk:

Mencari sebuah nilai.

XLOOKUP juga dapat digunakan untuk melakukan:

Data reconciliation

yaitu proses membandingkan dua sumber data untuk menemukan perbedaan.

Konsepnya:

SOURCE 1
   │
   │ ID
   ▼
XLOOKUP
   │
   │ mencari ID
   ▼
SOURCE 2
   │
   ├── Ditemukan
   │      ↓
   │     ADA
   │
   └── Tidak ditemukan
          ↓
       TIDAK ADA

Dengan pendekatan ini, proses yang sebelumnya dilakukan secara manual dapat dilakukan secara otomatis.

Semakin besar datanya, semakin terasa manfaatnya.

Kesimpulan

Ketika kita memiliki dua sumber data yang seharusnya identik, jangan langsung membandingkannya satu per satu.

Gunakan langkah berikut:

1. Tentukan sumber data utama

Tentukan data mana yang menjadi acuan.

2. Tentukan key

Pilih kolom yang digunakan untuk mencocokkan data.

3. Susun kedua sumber data

Letakkan data yang akan dibandingkan pada worksheet dengan struktur yang jelas.

4. Gunakan XLOOKUP

Cari setiap key dari Source 1 ke Source 2.

5. Berikan status

Tampilkan:

ADA

atau:

TIDAK ADA

6. Filter hasil

Tampilkan hanya data dengan status TIDAK ADA.

7. Hitung jumlahnya

Gunakan COUNTIF untuk mengetahui jumlah data yang hilang.

Formula Utama

Untuk mengetahui apakah ID Source 1 terdapat di Source 2:

=XLOOKUP(A2,$D$2:$D$12,"ADA","TIDAK ADA")

Untuk menghitung jumlah data yang tidak ditemukan:

=COUNTIF(C2:C16,"TIDAK ADA")

Dari 15 Data Menjadi 5.000 Data

Pada contoh kita:

15 data Source 1
↓
11 data Source 2
↓
4 data yang tidak ditemukan

Prinsip yang sama dapat diterapkan pada data yang jauh lebih besar:

5.000 data Source 1
↓
4.000 data Source 2
↓
1.000 data yang perlu ditemukan

Jadi, kekuatan XLOOKUP bukan hanya pada kemampuannya untuk mengambil data.

XLOOKUP juga dapat membantu kita menemukan perbedaan, melakukan validasi, dan melakukan reconciliation terhadap data.

Excel bukan sekadar mencari data. Dengan formula yang tepat, Excel dapat membantu kita menemukan masalah di dalam data.