Genel formül
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Özet
Bir hücreyi birkaç şeyden biri için kontrol etmek ve listede bulunan ilk eşleşmeyi döndürmek için, bir eşleşmeyi bulmak için ARA veya BUL kullanan bir DİZİN / KAÇINCI formülü kullanabilirsiniz. Gösterilen örnekte, C5'teki formül şöyledir:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
burada "şeyler", adlandırılmış aralık E5: E9'dur.
Not: Bu bir dizi formülüdür ve Control + Shift + Enter ile girilmelidir.
Açıklama
Bu örnekte, "şeyler" (E5: E9) adı verilen adlandırılmış bir aralıktaki renklerin bir listesi var. Bu renklerden herhangi birini içerip içermediğini görmek için B sütunundaki metni kontrol etmek istiyoruz. Öyleyse, bulunan ilk rengin adını döndürmek istiyoruz.
Baştan sona çalışarak, bu formül ISNUMBER işlevini ve ARA işlevini kullanarak B5'teki metni aşağıdaki gibi "şeyler" de listelenen her renk için arar:
ISNUMBER(SEARCH(things,B5)
Bu ifade, bir hücreyi tek bir alt dize için kontrol eden bir formüle (burada ayrıntılı olarak açıklanmıştır) dayanmaktadır. Hücre alt dizeyi içeriyorsa, ifade TRUE değerini döndürür. Değilse, ifade FALSE değerini döndürür.
Bu ARAMA'yı verdiğimizde (tek bir şey yerine) bir şeyler listesi bize bir dizi sonuç verecektir. Bulunan her renk sayısal bir konum oluşturur ve bulunmayan renkler bir hata oluşturur:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
ISNUMBER işlevi daha sonra sonuçları DOĞRU / YANLIŞ değerlerine dönüştürür. Herhangi bir sayı DOĞRU olur ve herhangi bir hata (bulunamadı) YANLIŞ olur. Sonuç şuna benzer bir dizidir:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Bu dizi, dizi argümanı olarak MATCH işlevine döndürülür. Arama değeri DOĞRU'dur ve tam eşlemeyi zorlamak için eşleme türü sıfır olarak ayarlanmıştır. Eşleşen bir renk olduğunda, KAÇINCI bulunan ilk DOĞRU konumu döndürür. Bu değer INDEX işlevine satır numarası olarak beslenir ve dizi olarak sağlanan "şeyler" adlı aralıkla. En az bir eşleşme olduğunda, INDEX bu konumdaki rengi döndürür. Eşleşme bulunamadığında, bu formül #YOK hatasını verir.
Sabit kodlanmış değerlerle
Bu örnekte "şeyler" gibi harici bir adlandırılmış aralık ayarlamak istemiyorsanız, değerleri formüle aşağıdaki gibi "dizi sabitleri" olarak sabit kodlayabilirsiniz:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Hücrede ilk eşleşmeyi yap
Buradaki dil oldukça kafa karıştırıcı, ancak yukarıdaki formül, aranacak şeyler listesinde bulunan ilk eşleşmeyi döndürecektir. Bunun yerine test edilmekte olan hücrede bulunan ilk eşleşmeyi döndürmek istiyorsanız, aşağıdaki gibi bir formül deneyebilirsiniz:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
Formülün bu sürümünde, MATCH işlevi şu kod parçacığının sonucunu aramak için ayarlanmıştır:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
Bu, SEARCH tarafından döndürülen sonuçlarda minimum değeri almak için AGGREGATE işlevini kullanır. Burada AGGREGATE'e ihtiyacımız var, çünkü gelen dizi büyük olasılıkla hatalar içerecektir (bir şeyler bulunamadığında SEARCH tarafından döndürülür) ve bu hataları görmezden gelecek ve yine de bize minimum sayısal değeri verecek bir işleve ihtiyacımız var.
AGGREGATE işlevinin sonucu, ARAMA ile döndürülen aynı dizi ile birlikte arama değeri olarak doğrudan MATCH'a döndürülür. Nihai sonuç, nesneler listesinde bulunan ilk eşleşme değil, hücrede bulunan ilk eşleşmedir.
