Formula umum
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Ringkasan
Untuk membandingkan dua daftar dan menarik nilai yang hilang dari satu daftar ke daftar lainnya, Anda bisa menggunakan rumus array yang didasarkan pada INDEX dan MATCH. Dalam contoh yang diperlihatkan, nilai terakhir dalam daftar B ada di sel D11. Rumus di D12, disalin, adalah:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
di mana "lengkap" adalah rentang bernama B5: B15.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Penjelasan
Bekerja dari dalam ke luar, inti dari rumus ini adalah ekspresi MATCH bagian dalam:
ISNA(MATCH(complete,$D$5:D11,0)
Di sini, fungsi MATCH digunakan untuk membandingkan semua nilai "lengkap" terhadap daftar parsial. Rentang bernama "complete" digunakan untuk nilai pencarian, dan daftar parsial digunakan sebagai array pencarian. Perhatikan, bagaimanapun, bahwa daftar parsial dimasukkan sebagai rentang perluasan yang mengakhiri "satu sel di atas" sel formula. Ini memungkinkan daftar parsial diperluas untuk menyertakan nilai baru seperti yang muncul di bawah daftar asli.
Hasil dari MATCH adalah deretan angka dan kesalahan # N / A, dimana angka merepresentasikan nilai dalam daftar lengkap yang ada pada daftar parsial; dan kesalahan menunjukkan nilai yang hilang:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Fungsi ISNA digunakan untuk mengubah hasil ini menjadi larik nilai TRUE dan FALSE. Dalam larik ini, TRUE terkait dengan nilai yang hilang dan FALSE terkait dengan nilai yang sudah ada:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Fungsi ISNA mengembalikan larik ini ke MATCH luar sebagai larik pencarian. Fungsi MATCH selalu mengembalikan kecocokan pertama yang ditemukan, jadi kecocokan akan mengembalikan posisi (baris) dari nilai pertama yang hilang yang ditemukan. Hasil ini dikembalikan ke INDEX sebagai nomor baris, dengan rentang bernama "complete" disediakan sebagai larik.
Di sel D12, nilai hilang pertama yang ditemukan adalah "kiwi" di baris 2, jadi kami memiliki:
=INDEX(complete,2) // returns "kiwi"
Di D13, "kiwi" sekarang disertakan dalam referensi perluasan, jadi nilai pertama yang hilang adalah "pear":
=INDEX(complete,5) // returns "pear"
Dan seterusnya. Setelah semua nilai yang hilang ditambahkan, rumus akan mengembalikan kesalahan # N / A.
