Formula umum
(=INDEX(rng1,MATCH(MAX(LEN(rng1)*(rng2=criteria)),LEN(rng1)*(rng2=criteria),0)))
Ringkasan
Untuk menemukan string terpanjang dalam rentang dengan kriteria, Anda bisa menggunakan rumus array berdasarkan INDEX, MATCH, LEN dan MAX. Dalam contoh yang ditampilkan, rumus di F6 adalah:
(=INDEX(names,MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)))
Di mana "nama" adalah rentang bernama C5: C14, dan "kelas" adalah rentang bernama B5: B14.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Penjelasan
Inti dari rumus ini adalah fungsi MATCH, yang menempatkan posisi string terpanjang menggunakan kriteria yang disediakan:
MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)
Catatan MATCH diatur untuk melakukan pencocokan tepat dengan menyediakan nol untuk jenis pencocokan. Untuk nilai pencarian, kami memiliki:
LEN(names)*(class=F5)
Fungsi LEN mengembalikan larik hasil (panjang), satu untuk setiap nama dalam daftar di mana class = "A" dari sel F5:
(5;6;8;6;6;0;0;0;0;0)
Ini secara efektif menyaring semua Kelas B, dan fungsi MAX kemudian mengembalikan nilai terbesar, 8.
Untuk membuat array pencarian, kami menggunakan pendekatan yang sama:
LEN(names)*(class=F5)
Dan dapatkan hasil yang sama:
(5;6;8;6;6;0;0;0;0;0)
Setelah LEN dan MAX dijalankan, kami memiliki rumus MATCH dengan nilai-nilai ini:
MATCH(8,(5;6;8;6;6;0;0;0;0;0),0))
MATCH kemudian mengembalikan posisi 8 dalam daftar, 3, yang dimasukkan ke dalam INDEX seperti ini:
=INDEX(names,3)
Akhirnya, INDEX dengan patuh mengembalikan nilai di posisi ke-3 nama , yaitu "Jonathan".
