Listeye karşı bir hücredeki ilk eşleşen değeri alma

Diyelim ki bir anahtar kelime listeniz var ve belirli bir hücrede geçen ilk anahtar kelimeyi almak istiyorsunuz (hücrede başka birkaç değer de olsa bile). Bunun için INDEX ve MATCH formüllerini, AGGREGATE ve SEARCH işlevlerinin yardımıyla kullanmanız gerekir.

Listedeki ilk eşleşen değeri bir hücrede nasıl alabilirim?

Yukarıdaki tabloda gösterildiği gibi, bir hücrede Anahtar Kelimeler listesine karşı eşleşen ilk anahtar kelimeyi almak, tam eşleşme değil içerik eşleşmesi yapılması gerektiği anlamına gelir. Bunu gerçekleştirmek için SEARCH işlevini kullanarak hücrede görünen anahtar kelimelerin konumlarını sayısal olarak AGGREGATE işlevine aktarabilirsiniz. Ardından AGGREGATE işlevi, function_num parametresini 15 ve ref2 bağımsız değişkenini 1 olarak ayarlayarak en küçük sayıyı elde eder. Daha sonra MATCH işleviyle en küçük ilk değerin konumu bulunur ve bu konum numarası INDEX işlevine aktarılarak o konumdaki değer alınır.

Genel sözdizimi

=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

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

B5 hücresinde görünen, Anahtar Kelimeler sütununa karşı ilk eşleşen anahtar kelimeyialmak için lütfen aşağıdaki formülü C5 hücresine kopyalayın veya girin ve sonucu almak için Ctrl+Shift+Entertuş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 başvuruları gösterir; bu da formüldeki keyword_rngaralığının formül başka hücrelere taşındığında veya kopyalandığında değişmeyeceği anlamına gelir. Ancak lookup_celldinamik olması istendiği için bu hücreye dolar işareti eklenmemiştir. Formülü girdikten sonra, formülü alttaki hücrelere uygulamak için doldurma tutamacını aşağı doğru sürükleyin.

Formülün açıklaması

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

• SEARCH($E$5:$E$7,B5): SEARCH işlevi, $E$5:$E$7 aralığındaki her anahtar kelimenin konumunu sayısal değer olarak döndürür; bulunamazsa #VALUE! hatası verir. Sonuç şu şekilde bir dizi olur: {15;11;#VALUE!}.
• AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1)=AGGREGATE(15,6,){15;11;#VALUE!},1): AGGREGATE işlevi, 15 işlev_num değerine ve 6 seçeneğine sahip olduğunda, hata değerlerini yoksayarak dizideki en küçük değeri ref2 argümanı 1 temel alarak döndürür. Böylece bu kod parçası 11 değerini verir.
• MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)=MATCH()11,{15;11;#VALUE!},0):match_type 0, MATCH işlevinin tam eşleşme yapmasını sağlar ve 11 değerinin {15;11;#VALUE!} dizisindeki konumunu döndürür. Böylece işlev 2 değerini verir.
• INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)) = INDEX($E$5:$E$7,2): INDEX işlevi daha sonra 2 numaralı değeri $E$5:$E$7 aralığından döndürür; bu değer bbb olur.

Not

• Bir hücrede hiçbir anahtar kelime bulunmuyorsa, #NUM! 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 işlevini FIND ile değiştirmeniz yeterlidir.

İlgili işlevler

İlgili Formüller