Formula Excel: Dapatkan perlawanan ke-9 dengan INDEX / MATCH -

Formula generik

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

Ringkasan

Untuk mendapatkan beberapa nilai pencocokan dari sekumpulan data dengan formula, anda dapat menggunakan fungsi JIKA dan KECIL untuk mengetahui bilangan baris setiap pertandingan dan memberi nilai yang kembali ke INDEX. Dalam contoh yang ditunjukkan, formula dalam I7 adalah:

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

Di mana julat yang dinamakan adalah amts (D4: D11), id (I3), dan id (C4: C11).

Perhatikan bahawa ini adalah formula larik dan mesti dimasukkan dengan Control + Shift + Enter.

Penjelasan

Pada intinya, formula ini hanyalah formula INDEX yang mengambil nilai dalam array pada kedudukan tertentu. Nilai untuk n disediakan di lajur H, dan semua kerja "berat" yang dilakukan rumus adalah untuk mengetahui baris dari mana untuk mendapatkan nilai, di mana baris sesuai dengan pertandingan "nth".

Fungsi IF berfungsi untuk mengetahui baris mana yang mengandungi padanan, dan fungsi KECIL mengembalikan nilai n dari senarai itu. Di dalam IF, ujian logik adalah:

ids=id

yang menghasilkan tatasusunan ini:

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

Perhatikan id pelanggan sesuai dengan kedudukan 1 dan 4, yang muncul sebagai BENAR. Argumen "nilai jika benar" dalam IF menghasilkan senarai nombor baris relatif dengan ungkapan ini:

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

yang menghasilkan tatasusunan ini:

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

Susunan ini kemudian "disaring" oleh hasil ujian logik, dan fungsi IF mengembalikan hasil array berikut:

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

Perhatikan bahawa kami mempunyai nombor baris yang sah untuk baris 1 dan baris 2.

Susunan ini kemudian diproses oleh KECIL, yang dikonfigurasi untuk menggunakan nilai di lajur H untuk mengembalikan nilai "nth". Fungsi KECIL secara automatik mengabaikan nilai logik BENAR dan SALAH dalam array. Pada akhirnya, formula dikurangkan menjadi:

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

Kesalahan pengendalian

Setelah tidak ada lagi padanan untuk id tertentu, fungsi KECIL akan mengembalikan kesalahan #NUM. Anda boleh mengatasi ralat ini dengan fungsi IFERROR, atau dengan menambahkan logik untuk menghitung padanan dan membatalkan pemprosesan apabila nombor di lajur H lebih besar daripada jumlah padanan. Contoh di sini menunjukkan satu pendekatan.