Ringkasan
Untuk mengekstrak beberapa kecocokan untuk memisahkan sel, dalam baris terpisah, Anda bisa menggunakan rumus array berdasarkan INDEX dan SMALL. Dalam contoh yang ditampilkan, rumus dalam E5 adalah:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($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 C4: C11, dan "grup" merujuk ke B4: B11. Nama-nama ini juga ditentukan dalam tangkapan layar di atas.
Inti dari rumus ini adalah ini: kita menggunakan fungsi SMALL untuk mendapatkan 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=E$4,ROW(names)-MIN(ROW(names))+1)
Cuplikan ini menguji "grup" rentang bernama untuk nilai di E4. Jika ditemukan, ini mengembalikan nomor baris "dinormalisasi" dari larik nomor baris yang dibuat dengan bagian rumus ini:
ROW(names)-MIN(ROW(names))+1
Hasilnya adalah array yang berisi nomor baris di mana ada kecocokan, dan FALSE jika tidak. Array terlihat seperti ini:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Array ini masuk ke SMALL. Nilai k untuk SMALL (nth) berasal dari rentang yang meluas:
ROWS($E$5:E5)
Ketika disalin ke bawah tabel hasil, kisaran mengembang, menyebabkan k (n) meningkat. Fungsi SMALL mengembalikan setiap nomor baris yang cocok, yang diberikan ke fungsi INDEX sebagai row_num, dengan rentang bernama "nama" sebagai larik.
Penanganan kesalahan
Saat ROWS mengembalikan nilai untuk k yang tidak ada, SMALL melontarkan kesalahan #NUM. Ini terjadi setelah semua kecocokan terjadi. Untuk menekan kesalahan, kami menggunakan IFERROR untuk menangkap kesalahan dan mengembalikan string kosong ("").
