Excel'de birden fazla ilgili değeri aramak ve birleştirmek nasıl yapılır?

Excel'de VLOOKUP kullandığınızda, genellikle veri setinden ilk eşleşen değeri döndürür. Ancak, bazen birden fazla ilgili değeri döndürmeniz ve birleştirmeniz gereken senaryolar olabilir (örneğin, belirli bir sınıfla ilişkili tüm isimler). VLOOKUP tek başına bunu başaramayacağı için, istenen sonucu elde etmek için gelişmiş teknikleri inceleyeceğiz.

Excel'de birden fazla ilgili değeri arama ve birleştirme

• TEXTJOIN ve FILTER Fonksiyonlarını Kullanma (Excel 365/2021)
• Kutools for Excel Kullanımı
• Kullanıcı Tanımlı Fonksiyon Kullanımı

TEXTJOIN ve FILTER Fonksiyonları ile birden fazla ilgili değeri arama ve birleştirme

Excel 365 veya Excel 2021 kullanıyorsanız, TEXTJOIN ve FILTER fonksiyonlarının kombinasyonu, birden fazla ilgili değeri birleştirmek için güçlü ve verimli bir yöntem sunar.

Hedef hücreye aşağıdaki formülü girin, ardından formülü diğer hücrelere sürükleyerek hücreleri doldurun. Tüm eşleşen değerler çıkarılır ve tek bir hücrede birleştirilir. Ekran görüntüsüne bakın:

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

Kutools for Excel ile birden fazla ilgili değeri arama ve birleştirme

Birden fazla ilgili değerle çalışırken Excel'in VLOOKUP sınırlamalarından bıktınız mı? Ancak Kutools for Excel'in Birçoklu Arama özelliğiyle, birkaç tıklamayla birden fazla ilgili değeri arayabilir ve birleştirebilirsiniz!

Kutools for Excel'i yükledikten sonra lütfen şu adımları izleyin:

"Kutools" > "Süper ARA" > "Birçoklu Arama (birden fazla sonuç döndürür)" seçerek iletişim kutusunu açın. İletişim kutusunda, aşağıdaki işlemleri belirtin:

1. Metin kutularında "Liste Yerleştirme Alanı" ve "Aranacak Değerler" alanlarını ayrı ayrı seçin;
2. Kullanmak istediğiniz tablo aralığını seçin;
3. "Anahtar Sütun" ve "Dönüş Sütunu" açılır menülerinden anahtar sütununu ve dönüş sütununu ayrı ayrı belirtin;
4. Son olarak, Tamam düğmesine tıklayın.
   

Sonuç: Artık tüm eşleşen değerler tek bir hücrede birleştirilmiştir. Ekran görüntüsüne bakın:

Kullanıcı Tanımlı Fonksiyon ile birden fazla ilgili değeri arama ve birleştirme

Eğer VBA (Visual Basic for Applications) konusunda rahat hissediyorsanız, birden fazla ilgili değeri birleştirmek için bir Kullanıcı Tanımlı Fonksiyon (UDF) oluşturabilirsiniz. Bu yöntem tüm Excel sürümlerinde çalışır.

1. "ALT + F11" tuşlarına basılı tutarak "Microsoft Visual Basic for Applications" penceresini açın.

2. "Ekle" > "Modül" seçin ve aşağıdaki kodu Modül Penceresine yapıştırın.

VBA Kodu: Bir hücredeki birden fazla eşleşen değeri arama ve birleştirme

Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
    Dim Cell As Range
    Dim Result As String
    Result = ""
    For Each Cell In LookupRange
        If Cell.Value = LookupValue Then
            Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
        End If
    Next Cell
    If Result <> "" Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    ConcatenateMatches = Result
End Function

3. Ardından bu kodu kaydedip kapatın, çalışma sayfasına geri dönün ve bu formülü girin: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) , sonuçları koymak istediğiniz boş bir hücreye yazın, ardından formülü diğer hücrelere doldurmak için doldurma sapmasını aşağı çekin. Belirli bir veriye göre tüm ilgili değerler virgül ve boşluk ayırıcılarıyla tek bir hücreye döndürülmüştür, ekran görüntüsüne bakın:

Excel'de birden fazla ilgili değeri birleştirme, her birinin kendi avantajları olan çeşitli yöntemlerle gerçekleştirilebilir. Metin birleştirme için yerleşik fonksiyonlar (TEXTJOIN ve FILTER), Kutools gibi üçüncü parti araçlar veya özel VBA çözümleri tercih edebilirsiniz. Anahtar, Excel sürümünüz, beceri seviyeniz ve özel gereksinimlerinize en uygun yöntemi seçmektir. Eğer daha fazla Excel ipucu ve hilesini keşfetmeye ilgi duyuyorsanız, web sitemiz binlerce öğretici sunmaktadır.