Formula umum
=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))
Ringkasan
Untuk menemukan teks yang paling sering muncul dalam rentang, berdasarkan kriteria yang Anda berikan, Anda bisa menggunakan rumus array berdasarkan beberapa fungsi Excel INDEX, MATCH, MODE, dan IF. Pada contoh yang ditunjukkan, rumus di G5 adalah:
=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))
di mana "pemasok" adalah rentang bernama C5: C15, dan "klien" adalah rentang bernama B5: B15.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Penjelasan
Bekerja dari dalam ke luar, kami menggunakan fungsi MATCH untuk mencocokkan rentang teks itu sendiri, dengan memberikan MATCH rentang yang sama untuk nilai pencarian dan array pencarian, dengan nol untuk jenis pencocokan:
MATCH(supplier,supplier,0)
Karena nilai pencarian adalah larik dengan 10 nilai, MATCH mengembalikan larik 10 hasil:
(1;1;3;3;5;1;7;3;1;5;5)
Setiap item dalam larik ini mewakili posisi pertama di mana nama pemasok muncul dalam data. Larik ini dimasukkan ke dalam fungsi IF, yang digunakan untuk memfilter hasil hanya untuk Klien A:
IF(client=F5,(1;1;3;3;5;1;7;3;1;5;5))
IF mengembalikan array yang difilter ke fungsi MODE:
(1;FALSE;3;FALSE;5;1;FALSE;FALSE;1;5;FALSE)
Perhatikan hanya posisi yang terkait dengan Klien A yang tersisa dalam larik. MODE mengabaikan nilai FALSE dan mengembalikan angka yang paling sering muncul ke fungsi INDEX sebagai nomor baris:
=INDEX(supplier,1)
Akhirnya, dengan rentang bernama "pemasok" sebagai larik, INDEX mengembalikan "Brown", pemasok yang paling sering muncul untuk Klien A.
