Formula umum
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Ringkasan
Untuk menguji sel untuk salah satu dari beberapa string, dan mengembalikan hasil kustom untuk kecocokan pertama yang ditemukan, Anda bisa menggunakan rumus INDEX / MATCH berdasarkan fungsi SEARCH. Pada contoh yang ditunjukkan, rumus di C5 adalah:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
di mana benda-benda (E5: E8) dan hasil (F5: F8) diberi nama rentang.
Ini adalah rumus array dan harus dimasukkan dengan Control + Shift + Enter.
Penjelasan
Rumus ini menggunakan dua rentang bernama: benda , dan hasil . Jika Anda memasukkan rumus ini secara langsung, pastikan untuk menggunakan rentang bernama dengan nama yang sama (ditentukan berdasarkan data Anda). Jika Anda tidak ingin menggunakan rentang bernama, gunakan referensi absolut sebagai gantinya.
Inti dari rumus ini adalah cuplikan ini:
ISNUMBER(SEARCH(things,B5)
Ini didasarkan pada rumus lain (dijelaskan secara terperinci di sini) yang memeriksa sel untuk satu substring. Jika sel berisi substring, rumus mengembalikan TRUE. Jika tidak, rumus mengembalikan FALSE.
Karena kita memberikan fungsi SEARCH lebih dari satu hal untuk dicari, dalam range bernama things , ini akan memberi kita lebih dari satu hasil, dalam sebuah array yang terlihat seperti ini:
(#VALUE!;9;#VALUE!;#VALUE!)
Angka mewakili kecocokan dalam berbagai hal , kesalahan mewakili item yang tidak ditemukan.
Untuk menyederhanakan larik, kami menggunakan fungsi ISNUMBER untuk mengonversi semua item dalam larik menjadi TRUE atau FALSE. Setiap nomor yang valid menjadi BENAR, dan setiap kesalahan (yaitu sesuatu yang tidak ditemukan) menjadi SALAH. Hasilnya adalah array seperti ini:
(FALSE;TRUE;FALSE;FALSE)
yang masuk ke fungsi MATCH sebagai argumen lookup_array, dengan lookup_value TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH kemudian mengembalikan posisi TRUE pertama yang ditemukan, 2 dalam kasus ini.
Akhirnya, kami menggunakan fungsi INDEX untuk mengambil hasil dari hasil rentang bernama pada posisi yang sama:
=INDEX(results,2) // returns "found red"
Anda dapat menyesuaikan rentang hasil dengan nilai apa pun yang masuk akal dalam kasus penggunaan Anda.
Mencegah kecocokan palsu
Satu masalah dengan pendekatan ini dengan pendekatan ISNUMBER + SEARCH adalah Anda mungkin mendapatkan kecocokan palsu dari kecocokan parsial dalam kata-kata yang lebih panjang. Misalnya, jika Anda mencoba mencocokkan "dr", Anda juga dapat menemukan "Andrea", "drank", "drip", dll. Karena "dr" muncul di dalam kata ini. Ini terjadi karena SEARCH secara otomatis melakukan pencocokan "jenis-isi".
Untuk perbaikan cepat, Anda dapat membungkus kata pencarian dalam karakter spasi (yaitu "dr", atau "dr") untuk menghindari menemukan "dr" dalam kata lain. Tapi ini akan gagal jika "dr" muncul pertama atau terakhir dalam sel.
Jika Anda membutuhkan solusi yang lebih kuat, salah satu opsinya adalah menormalkan teks terlebih dahulu di kolom pembantu, dan menambahkan spasi di depan dan di belakang. Kemudian gunakan rumus di halaman ini pada teks di kolom pembantu, bukan teks asli.
