Ringkasan
Untuk mengekstrak beberapa kecocokan untuk memisahkan sel, di kolom terpisah, Anda bisa menggunakan rumus array berdasarkan INDEX dan SMALL. Dalam contoh yang ditunjukkan, rumus di F5 adalah:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),""))
Ini adalah rumus array dan harus dimasukkan dengan Control + Shift + Enter.
Setelah Anda memasukkan rumus di sel pertama, seret ke bawah dan ke seberang untuk mengisi sel lainnya.
Penjelasan
Catatan: rumus ini menggunakan dua rentang bernama: "nama" merujuk ke C5: C11, dan "grup" merujuk ke B5: B11. Nama-nama ini juga ditentukan dalam tangkapan layar di atas.
Inti dari rumus ini adalah ini: kami menggunakan fungsi SMALL untuk menghasilkan nomor baris yang sesuai dengan "pertandingan ke-n". Setelah kami memiliki nomor baris, kami cukup meneruskannya ke fungsi fungsi INDEX, yang mengembalikan nilai pada baris itu.
Triknya adalah SMALL bekerja dengan larik yang dibangun secara dinamis oleh IF dalam bit ini:
IF(groups=$E5,ROW(names)-MIN(ROW(names))+1)
Potongan ini menguji rentang bernama "grup" untuk nilai dalam E5. Jika ditemukan, ini mengembalikan nomor baris dari larik nomor baris relatif yang dibuat dengan:
ROW(names)-MIN(ROW(names))+1
Hasil akhirnya adalah larik yang berisi angka di mana ada kecocokan, dan SALAH jika tidak:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Array ini masuk ke SMALL. Nilai k untuk SMALL (nth) berasal dari rentang yang meluas:
COLUMNS($E$5:E5)
Saat disalin ke seluruh tabel hasil, rentangnya meluas, menyebabkan k (n) bertambah. Fungsi SMALL mengembalikan setiap nomor baris yang cocok, yang diberikan ke fungsi INDEX sebagai row_num, dengan rentang bernama "nama" sebagai larik.
Penanganan kesalahan
Ketika COLUMNS mengembalikan nilai untuk k yang tidak ada, SMALL melontarkan kesalahan #NUM. Ini terjadi setelah semua kecocokan terjadi. Untuk menekan kesalahan, kami membungkus rumus dalam fungsi IFERROR untuk menangkap kesalahan dan mengembalikan string kosong ("").
