Genel formül
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Özet
Bir hücreyi birkaç dizeden biri için test etmek ve bulunan ilk eşleşme için özel bir sonuç döndürmek için, ARAMA işlevine dayalı bir DİZİN / KAÇINCI formülü kullanabilirsiniz. Gösterilen örnekte, C5'teki formül şöyledir:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
burada şeyler (E5: E8) ve sonuçlar (F5: F8) adlandırılmış aralıklardır.
Bu bir dizi formülüdür ve Control + Shift + Enter ile girilmelidir.
Açıklama
Bu formül iki adlandırılmış aralık kullanır: şeyler ve sonuçlar . Bu formülü doğrudan taşıyorsanız, aynı adlara sahip (verilerinize göre tanımlanan) adlandırılmış aralıklar kullandığınızdan emin olun. Adlandırılmış aralıklar kullanmak istemiyorsanız, bunun yerine mutlak referanslar kullanın.
Bu formülün özü şu pasajdır:
ISNUMBER(SEARCH(things,B5)
Bu, bir hücreyi tek bir alt dize için kontrol eden başka bir formüle (burada ayrıntılı olarak açıklanmıştır) dayanmaktadır. Hücre alt dizeyi içeriyorsa, formül DOĞRU değerini verir. Aksi takdirde formül YANLIŞ değerini döndürür.
SEARCH işlevine aranacak birden fazla şey verdiğimiz için, adlandırılmış aralıktaki şeyler , bize aşağıdaki gibi görünen bir dizide birden fazla sonuç verecektir:
(#VALUE!;9;#VALUE!;#VALUE!)
Sayılar içinde maçlar temsil şeyler , hatalar bulunmamıştır öğeleri temsil eder.
Diziyi basitleştirmek için, dizideki tüm öğeleri TRUE veya FALSE'a dönüştürmek için ISNUMBER işlevini kullanıyoruz. Herhangi bir geçerli sayı DOĞRU olur ve herhangi bir hata (yani bulunamayan bir şey) YANLIŞ olur. Sonuç şuna benzer bir dizidir:
(FALSE;TRUE;FALSE;FALSE)
arama_dizisi bağımsız değişkeni olarak MATCH işlevine girer ve lookup_value TRUE olur:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH daha sonra bulunan ilk DOĞRU, bu durumda 2 konumunu döndürür.
Son olarak, aynı konumdaki adlandırılmış aralık sonuçlarından bir sonuç almak için INDEX işlevini kullanırız :
=INDEX(results,2) // returns "found red"
Sonuç aralığını, kullanım durumunuzda anlamlı olan değerlerle özelleştirebilirsiniz .
Yanlış eşleşmeleri önleme
ISNUMBER + SEARCH yaklaşımındaki bu yaklaşımla ilgili bir sorun, daha uzun kelimelerdeki kısmi eşleşmelerden yanlış eşleşmeler elde edebilmenizdir. Örneğin, "dr" ile eşleşmeye çalışırsanız, bu kelimelerin içinde "dr" geçtiği için "Andrea", "içti", "damla", vb. De bulabilirsiniz. Bunun nedeni, SEARCH'in otomatik olarak "içeren tür" eşleşmesi yapmasıdır.
Hızlı bir düzeltme için, başka bir sözcükte "dr" bulmaktan kaçınmak için arama sözcüklerini boşluk karakterlerine (yani "dr" veya "dr") sarabilirsiniz. Ancak hücrede "dr" ilk veya son olarak görünürse bu başarısız olur.
Daha sağlam bir çözüme ihtiyacınız varsa, bir seçenek, metni önce bir yardımcı sütunda normalleştirmek ve baştaki ve sondaki boşlukları eklemektir. Ardından, bu sayfadaki formülü, orijinal metin yerine yardımcı sütundaki metinde kullanın.
