Bir listedeki hücreye karşı ilk eşleşen değeri al

Diyelim ki, bir anahtar kelime listesi var ve belirli bir hücrede görünen ilk anahtar kelimeyi almak istiyorsunuz, ancak hücre birkaç farklı değer içeriyor. Bu durumda INDEX ve MATCH formüllerini AGGREGATE ve SEARCH fonksiyonlarının yardımıyla kullanmanız gerekecek.

Bir listedeki hücreye karşı ilk eşleşen değeri nasıl alırız?

Yukarıdaki tabloda gösterilen Anahtar Kelimeler listesine göre bir hücredeki ilk eşleşen anahtar kelimeyi almak için tam eşleşme yerine bir içerme eşleşmesi yapmamız gerekir. Bunu yapmak için, SEARCH fonksiyonunu kullanarak anahtar kelimelerin hücredeki konumlarını sayısal olarak AGGREGATE fonksiyonuna besleyebilirsiniz. Daha sonra AGGREGATE, function_num değerini 15 ve ref2 argümanını 1 olarak ayarlayarak en küçük sayıyı alır. Ardından MATCH'i ilk en küçük değeri bulmak için kullanın ve bu pozisyon numarasını INDEX'e besleyerek o pozisyondaki değeri alın.

Genel sözdizimi

=INDEX(anahtar_kelime_rng,MATCH(AGGREGATE(15,6,SEARCH(anahtar_kelime_rng,arama_hücresi),1),SEARCH(anahtar_kelime_rng,arama_hücresi),0))

√ Not: Bu bir dizi formülüdür ve Ctrl + Shift + Enter tuşlarına basarak girmeniz gerekmektedir.

Anahtar Kelimeler sütununa karşı B5 hücresinde görünen ilk eşleşen anahtar kelimeyi almak için lütfen aşağıdaki formülü C5 hücresine kopyalayın veya girin ve sonucu almak için Ctrl + Shift + Enter tuşlarına basın:

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

√ Not: Yukarıdaki dolar işaretleri ($) mutlak referansları gösterir, yani formülün keyword_rng'si formülü diğer hücrelere taşıdığınızda veya kopyaladığınızda değişmez. Ancak lookup_cell'e dolar işareti eklenmemiştir çünkü dinamik olmasını istersiniz. Formülü girdikten sonra, formülü aşağıya doğru sürükleyerek alttaki hücrelere uygulayabilirsiniz.

Formül açıklaması

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5): SEARCH fonksiyonu, $E$5:$E$7 aralığındaki her anahtar kelimenin sayısal olarak bulunduğu pozisyonu döndürür; bulunamazsa #DEĞER! hatası verir. Sonuç şu şekilde bir dizi olacaktır: {15;11;#DEĞER!}.
• AGGREGATE(15,6,AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),1) = AGGREGATE(15,6,AGGREGATE(15,6,{15;11;#DEĞER!},1),1): Function_num değeri 15 ve seçeneği 6 olan AGGREGATE fonksiyonu, ref2 argümanına göre dizideki en küçük değeri döndürürken hata değerlerini görmezden gelir. Yani bu kod parçası 11 değerini döndürür.
• MATCH(MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0),0) = MATCH(MATCH(11,,{15;11;#DEĞER!},0),0): Match_type değeri 0, MATCH fonksiyonunun tam eşleşme yapmasını sağlar ve 11'in {15;11;#DEĞER!} dizisindeki pozisyonunu döndürür. Böylece fonksiyon 2 değerini döndürür.
• INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0)) = INDEX($E$5:$E$7,2): INDEX fonksiyonu daha sonra $E$5:$E$7 aralığındaki 2. değeri döndürür, bu da bbb'dir.

Not

• Eğer bir hücrede anahtar kelime yoksa, bir #SAYI! hatası döndürülür.
• Formül büyük/küçük harfe duyarlı değildir. Büyük/küçük harfe duyarlı bir eşleşme yapmak için, SEARCH fonksiyonunu FIND ile değiştirebilirsiniz.

İlgili fonksiyonlar

İlgili Formüller