Hücreden ilk liste değerini alın

Belirli bir hücrede, bir aralıkta listelenen ilk anahtar kelimeyi almak için (hücre birden fazla değerden birini içeriyorsa) oldukça karmaşık bir dizi formüle ihtiyacınız vardır: INDEX, MATCH, ISNUMBER ve SEARCH işlevlerini birlikte kullanmalısınız.

Hücredeki ilk liste değerini nasıl alabilirim?

Yukarıda gösterildiği gibi, anahtar kelimeler sütununa karşı hücrelerde bulunan ilk eşleşen anahtar kelimeyi almak için tam eşleşme değil, içerme eşleşmesi yapmamız gerekir. Bunu gerçekleştirmek üzere ISNUMBER ve SEARCH işlevlerini birleştirerek TRUE/FALSE değerlerinden oluşan bir dizi elde edebilirsiniz. Ardından MATCH işleviyle bu dizideki ilk TRUE değerinin konumunu belirleyip, o konum numarasını INDEX işlevine göndererek ilgili değeri çekebilirsiniz.

Genel söz dizimi

=INDEX(keyword_rng,MATCH(TRUE,ISNUMBER(SEARCH(keyword_rng,lookup_cell)),0))

√ Not: Bu bir dizi formülüdür ve girilirken Ctrl+Shift+Entertuşlarına basmanız gerekir.

B5 hücresinden Anahtar Kelime listesinde bulunan ilk eşleşmeyi almak için, aşağıdaki formülü C5 hücresine kopyalayın veya girin ve sonucu almak üzere Ctrl+Shift+Entertuşlarına basın:

=INDEX()($E$5:$E$7),MATCH(TRUE,ISNUMBER(SEARCH()))($E$5:$E$7),B5)),0))

√ Not: Yukarıdaki dolar işaretleri ($) mutlak başvuruları gösterir; yani formüldeki keyword_rngbaşka hücrelere taşıdığınızda veya kopyaladığınızda değişmez. Ancak lookup_celldinamik olması için dolar işareti almamıştır. Formülü girdikten sonra, formülü alttaki hücrelere uygulamak için doldurma tutamacını aşağı doğru sürükleyin.

Formül açıklaması

=INDEX(($E$5:$E$7,MATCH(TRUE,ISNUMBER(SEARCH($E$5:$E$7,B5)),0))

• SEARCH($E$5:$E$7,B5): SEARCH işlevi, $E$5:$E$7 aralığındaki anahtar kelimelerin konumlarını sayısal değerler olarak döndürür, bulunamazsa #VALUE! hatası verir. Sonuç şu şekilde bir dizi olur: {15,11,#VALUE!}.
• ISNUMBER()SEARCH($E$5:$E$7,B5))=ISNUMBER(){15;11;#VALUE!}): ISNUMBER işlevi, dizideki sayıları TRUE’a, #VALUE! hatalarını ise FALSE’a dönüştürür. Örneğin: {TRUE;TRUE;FALSE}.
• MATCH(TRUE,)ISNUMBER()SEARCH($E$5:$E$7,B5)),0)=MATCH(TRUE,){TRUE;TRUE;FALSE},0):match_type 0 parametresi, MATCH işlevinin tam eşleşme yapmasını ve {TRUE;TRUE;FALSE} dizisindeki ilk TRUE’un konumunu döndürmesini sağlar. Böylece işlev {TRUE;TRUE;FALSE} sonucunu verir. Yani işlev şunu döndürür: 1.
• INDEX(($E$5:$E$7,))MATCH(TRUE,)ISNUMBER()SEARCH($E$5:$E$7,B5)),0)) = INDEX(($E$5:$E$7,)1):Ardından INDEX işlevi, 1 numaralı değeri $E$5:$E$7 aralığından getirir; bu da AAA’dır.

Not

• Bir hücre iki veya daha fazla anahtar kelime içeriyorsa, formül anahtar kelime aralığında listelenen ilk kelimeyi döndürür.
• Formül büyük/küçük harf duyarlı değildir. Büyük/küçük harfe duyarlı bir eşleşme yapmak istiyorsanız, SEARCH işlevini FIND ile değiştirmeniz yeterlidir.
• Anahtar kelimeler listesini yalnızca belirli bir metnin hücrede olup olmadığını kontrol etmek istiyorsanız, bunu sabit bir diziyle (örn. {«AAA»}) değiştirebilirsiniz.

İlgili işlevler

İlgili Formüller