Rumus Excel: Dapatkan kecocokan ke-n dengan INDEX / MATCH -

Formula umum

(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))

Ringkasan

Untuk mengambil beberapa nilai yang cocok dari sekumpulan data dengan rumus, Anda dapat menggunakan fungsi IF dan SMALL untuk mengetahui nomor baris dari setiap kecocokan dan memasukkan nilai itu kembali ke INDEX. Dalam contoh yang ditunjukkan, rumus di I7 adalah:

(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))

Di mana rentang bernama amts (D4: D11), id (I3), dan ids (C4: C11).

Perhatikan bahwa ini adalah rumus array dan harus dimasukkan dengan Control + Shift + Enter.

Penjelasan

Pada intinya, rumus ini hanyalah rumus INDEX yang mengambil nilai dalam larik pada posisi tertentu. Nilai untuk n disediakan di kolom H, dan semua pekerjaan "berat" yang dilakukan rumus adalah mencari tahu baris untuk mengambil nilai, di mana baris sesuai dengan kecocokan "n".

Fungsi IF melakukan pekerjaan mencari tahu baris mana yang berisi kecocokan, dan fungsi SMALL mengembalikan nilai ke-n dari daftar itu. Di dalam IF, tes logisnya adalah:

ids=id

yang menghasilkan larik ini:

(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)

Perhatikan kecocokan id pelanggan di posisi 1 dan 4, yang muncul sebagai BENAR. Argumen "nilai jika benar" di IF menghasilkan daftar nomor baris relatif dengan ekspresi ini:

ROW(ids)-ROW(INDEX(ids,1,1))+1

yang menghasilkan array ini:

(1;2;3;4;5;6;7)

Larik ini kemudian "difilter" oleh hasil uji logika, dan fungsi IF mengembalikan hasil larik berikut:

(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)

Perhatikan bahwa kami memiliki nomor baris yang valid untuk baris 1 dan baris 2.

Larik ini kemudian diproses oleh SMALL, yang dikonfigurasi untuk menggunakan nilai di kolom H untuk mengembalikan nilai "n". Fungsi SMALL secara otomatis mengabaikan nilai logika TRUE dan FALSE dalam array. Akhirnya, rumus-rumus tersebut akan berkurang menjadi:

=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125

Penanganan kesalahan

Setelah tidak ada lagi kecocokan untuk id yang diberikan, fungsi SMALL akan mengembalikan kesalahan #NUM. Anda dapat menangani kesalahan ini dengan fungsi IFERROR, atau dengan menambahkan logika untuk menghitung kecocokan dan membatalkan pemrosesan setelah angka di kolom H lebih besar dari jumlah kecocokan. Contoh di sini menunjukkan satu pendekatan.