Formula umum
=MATCH(1,EXACT(LEFT(A1,255),LEFT(rng,255))*EXACT(MID(A1,256,255),MID(rng,256,255)),0)
Ringkasan
Untuk mencocokkan teks yang lebih panjang dari 255 karakter dengan fungsi MATCH, Anda dapat menggunakan fungsi LEFT, MID, dan EXACT untuk mengurai dan membandingkan teks, seperti dijelaskan di bawah ini. Pada contoh yang ditunjukkan, rumus di G5 adalah:
=MATCH(1,EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255)),0)
dimana datanya adalah rentang bernama B5: B15.
Catatan: rumus ini melakukan perbandingan peka huruf besar kecil.
Penjelasan
Fungsi MATCH memiliki batas 255 karakter untuk nilai pencarian. Jika Anda mencoba menggunakan teks yang lebih panjang, MATCH akan mengembalikan kesalahan #VALUE.
Untuk mengatasi batas ini, Anda dapat menggunakan logika boolean dan fungsi LEFT, MID, dan EXACT untuk mengurai dan membandingkan teks.
String yang kami uji di sel E5 adalah 373 karakter sebagai berikut:
Lorem ipsum dolor amet menaruh burung di atasnya dana perwalian listicle, hak sepeda vaporware unicorn Anda mungkin belum pernah mendengar tentang mereka kumis. Pakan helvetica semiotik berkerak sebenarnya pusaka. Tumblr poutine unicorn godard berusaha keras sebelum mereka menjual rompi fixie twee kitsch meditasi narwhal secara harfiah hoodie retro. Tas kurir pengrajin jus hijau berkerak.
Pada intinya, ini hanyalah rumus MATCH, yang disiapkan untuk mencari 1 dalam mode pencocokan tepat:
=MATCH(1,array,0)
Larik dalam rumus di atas hanya berisi 1 dan 0, dan 1 mewakili teks yang cocok. Array ini dibangun oleh ekspresi berikut:
EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255))
Ekspresi ini sendiri memiliki dua bagian. Di sebelah kiri kami memiliki:
EXACT(LEFT(E5,255),LEFT(data,255)) // compare first 255 chars
Di sini, fungsi LEFT mengekstrak 255 karakter pertama dari E5, dan dari semua sel dalam data rentang bernama (B5: B15). Karena data berisi 11 string teks, LEFT akan menghasilkan 11 hasil.
Fungsi EXACT kemudian membandingkan satu string dari E5 terhadap semua 11 string yang dikembalikan oleh LEFT. EXACT mengembalikan 11 hasil dalam larik seperti ini:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Di sebelah kanan, kami memiliki ekspresi lain:
EXACT(MID(E5,256,255),MID(data,256,255) // compare next 255 chars
Ini sama persis dengan pendekatan yang digunakan dengan LEFT, tetapi di sini kami menggunakan fungsi MID untuk mengekstrak 255 karakter teks berikutnya. Fungsi EXACT kembali menampilkan 11 hasil:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Saat dua larik di atas dikalikan satu sama lain, operasi matematika memaksa nilai TRUE FALSE menjadi 1 dan 0. Mengikuti aturan aritmatika boolean, hasilnya berupa larik seperti ini:
(0;0;0;0;0;0;0;0;0;1;0)
yang dikembalikan langsung ke MATCH sebagai array pencarian. Rumusnya sekarang dapat diselesaikan menjadi:
=MATCH(1,(0;0;0;0;0;0;0;0;0;1;0),0)
Fungsi MATCH melakukan pencocokan tepat, dan mengembalikan hasil akhir dari 10, yang mewakili string teks kesepuluh di B5: B15.
Catatan: panjang teks yang ditunjukkan pada contoh dihitung dengan fungsi LEN. Ini muncul untuk referensi saja.
Opsi tidak peka huruf besar / kecil
Fungsi EXACT peka huruf besar / kecil, jadi rumus di atas akan menggunakan huruf besar / kecil.
Untuk melakukan pencocokan tidak peka huruf besar / kecil dengan teks panjang, Anda menggunakan fungsi ISNUMBER dan SEARCH sebagai berikut:
=MATCH(1,ISNUMBER(SEARCH(LEFT(E5,255),LEFT(data,255)))*ISNUMBER(SEARCH(MID(E5,256,255),MID(data,256,255))),0)
Struktur keseluruhan rumus ini identik dengan contoh di atas, tetapi fungsi SEARCH digunakan sebagai pengganti TEPAT untuk membandingkan teks (dijelaskan secara detail di sini).
Tidak seperti EXACT, fungsi SEARCH juga mendukung karakter pengganti.
Dengan XMATCH
Fungsi XMATCH tidak memiliki batas 255 karakter yang sama dengan MATCH. Untuk melakukan pencocokan serupa pada teks panjang dengan XMATCH, Anda dapat menggunakan rumus yang lebih sederhana di bawah ini:
=XMATCH(E5,data)
Catatan: XMATCH mendukung karakter pengganti, tetapi tidak peka huruf besar / kecil.
