Formula umum
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Ringkasan
Untuk memeriksa sel untuk salah satu dari beberapa hal, dan mengembalikan kecocokan pertama yang ditemukan dalam daftar, Anda bisa menggunakan rumus INDEX / MATCH yang menggunakan SEARCH atau FIND untuk menemukan kecocokan. Pada contoh yang ditunjukkan, rumus di C5 adalah:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
di mana "benda" adalah rentang bernama E5: E9.
Catatan: ini adalah rumus array dan harus dimasukkan dengan Control + Shift + Enter.
Penjelasan
Dalam contoh ini, kami memiliki daftar warna dalam rentang bernama yang disebut "benda" (E5: E9). Kami ingin memeriksa teks di kolom B untuk melihat apakah itu berisi salah satu warna ini. Jika demikian, kami ingin mengembalikan nama warna pertama yang ditemukan.
Bekerja dari dalam ke luar, rumus ini menggunakan fungsi ISNUMBER dan fungsi SEARCH untuk mencari teks di B5 untuk setiap warna yang terdaftar dalam "benda" seperti ini:
ISNUMBER(SEARCH(things,B5)
Ekspresi ini didasarkan pada rumus (dijelaskan secara rinci di sini) yang memeriksa sel untuk satu substring. Jika sel berisi substring, ekspresi tersebut mengembalikan TRUE. Jika tidak, ekspresi mengembalikan FALSE.
Ketika kita memberikan PENCARIAN ini daftar hal-hal (bukan satu hal) akan memberi kita kembali serangkaian hasil. Setiap warna yang ditemukan akan menghasilkan posisi numerik, dan warna yang tidak ditemukan akan menghasilkan kesalahan:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Fungsi ISNUMBER kemudian mengonversi hasil menjadi nilai TRUE / FALSE. Setiap nomor menjadi BENAR, dan kesalahan apa pun (tidak ditemukan) menjadi SALAH. Hasilnya adalah array seperti ini:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Array ini dikembalikan ke fungsi MATCH sebagai argumen array. Nilai pencarian TRUE dan jenis pencocokan disetel ke nol untuk memaksa pencocokan tepat. Ketika ada warna yang cocok, MATCH mengembalikan posisi pertama kali BENAR ditemukan. Nilai ini dimasukkan ke dalam fungsi INDEX sebagai nomor baris, dengan rentang bernama "things" disediakan sebagai larik. Jika ada setidaknya satu kecocokan, INDEX mengembalikan warna pada posisi itu. Jika tidak ada kecocokan yang ditemukan, rumus ini mengembalikan kesalahan # N / A.
Dengan nilai hard-code
Jika Anda tidak ingin menyiapkan rentang bernama eksternal seperti "benda" dalam contoh ini, Anda dapat memasukkan nilai kode keras ke dalam rumus sebagai "konstanta larik" seperti ini:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Dapatkan pertandingan pertama di sel
Bahasa di sini cukup membingungkan, tetapi rumus di atas akan mengembalikan kecocokan pertama yang ditemukan dalam daftar hal yang harus dicari. Jika sebaliknya Anda ingin mengembalikan kecocokan pertama yang ditemukan di sel yang sedang diuji, Anda dapat mencoba rumus seperti ini:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
Dalam versi rumus ini, fungsi MATCH diatur untuk mencari hasil dari cuplikan ini:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
yang menggunakan fungsi AGREGATE untuk mendapatkan nilai minimum dalam hasil yang dikembalikan oleh SEARCH. Kita membutuhkan AGGREGATE di sini, karena array yang masuk kemungkinan besar akan mengandung kesalahan (dikembalikan oleh SEARCH ketika hal-hal tidak ditemukan), dan kita memerlukan fungsi yang akan mengabaikan kesalahan tersebut dan tetap memberi kita nilai numerik minimum.
Hasil dari AGGREGATE dikembalikan langsung ke MATCH sebagai nilai pencarian, bersama dengan array yang sama yang dikembalikan oleh SEARCH. Hasil akhirnya adalah kecocokan pertama yang ditemukan di dalam sel, bukan kecocokan pertama yang ditemukan dalam daftar benda.
