Excel'de tek bir hücrede birden fazla değeri döndürmek için vlookup nasıl kullanılır?

VLOOKUP, Excel'de güçlü bir işlevdir, ancak varsayılan olarak yalnızca ilk eşleşen değeri döndürür. Eğer tüm eşleşen değerleri almanız ve bunları tek bir hücrede birleştirmeniz gerekiyorsa ne yaparsınız? Bu, veri setlerini analiz ederken veya bilgileri özetlerken sık karşılaşılan bir gereksinimdir. Bu kılavuzda, hem formüller hem de yararlı özellikler kullanarak tek bir hücreye birden fazla değer döndürme yöntemlerini adım adım anlatacağız.

TEXTJOIN işleviyle birden fazla değeri tek bir hücreye döndürme (Excel 2019 ve Office 365)

• Tüm eşleşen değerleri tek bir hücreye döndürme
• Tek bir hücreye yinelenenler olmadan tüm eşleşen değerleri döndürme

Kutools ile birden fazla değeri tek bir hücreye döndürme

Kullanıcı Tanımlı İşlev ile birden fazla değeri tek bir hücreye döndürme

• Tüm eşleşen değerleri tek bir hücreye döndürme
• Tek bir hücreye yinelenenler olmadan tüm eşleşen değerleri döndürme

TEXTJOIN işleviyle birden fazla değeri tek bir hücreye döndürme (Excel 2019 ve Office 365)

Eğer Excel 2019 veya Office 365 gibi daha yüksek bir Excel sürümünüz varsa, yeni bir işlev olan TEXTJOIN mevcuttur. Bu güçlü işlev sayesinde, hızlı bir şekilde vlookup yapabilir ve tüm eşleşen değerleri tek bir hücreye döndürebilirsiniz.

  Tüm eşleşen değerleri tek bir hücreye döndürme

Lütfen sonuçları yerleştirmek istediğiniz boş bir hücreye aşağıdaki formülü uygulayın, ardından ilk sonucu almak için Ctrl + Shift + Enter tuşlarına birlikte basın ve bu formülü kullanmak istediğiniz hücreye kadar doldurma tutamacını sürükleyin; aşağıdaki ekran görüntüsünde gösterildiği gibi tüm karşılık gelen değerleri elde edeceksiniz:

  Tek bir hücreye yinelenenler olmadan tüm eşleşen değerleri döndürme

Eğer arama verilerine göre yinelenenler olmadan tüm eşleşen değerleri döndürmek istiyorsanız, aşağıdaki formül size yardımcı olabilir.

Lütfen aşağıdaki formülü kopyalayıp boş bir hücreye yapıştırın, ardından ilk sonucu almak için Ctrl + Shift + Enter tuşlarına birlikte basın ve bu formülü diğer hücrelere kopyalayın; aşağıdaki ekran görüntüsünde gösterildiği gibi yinelenen olmayan tüm karşılık gelen değerleri elde edeceksiniz:

Kutools ile birden fazla değeri tek bir hücreye döndürme

Kutools for Excel'in "Gelişmiş Satırları Birleştir" özelliğini kullanarak, karmaşık formüllere gerek kalmadan birden fazla eşleşen değeri tek bir hücreye kolayca alabilirsiniz! Elle yapılan geçici çözümlere veda edin ve Excel'deki arama görevlerinizi daha etkili bir şekilde ele almanın yolunu keşfedin. Kutools for Excel'in bunu nasıl mümkün kıldığını inceleyelim!

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

1. Başka bir sütuna dayalı olarak bir sütun verisini birleştirmek istediğiniz veri aralığını seçin.

2. "Kutools" > "Birleştir ve Ayır" > "Gelişmiş Satırları Birleştir" seçeneğine tıklayın, aşağıdaki ekran görüntüsüne bakın:

3. Açılan "Gelişmiş Satırları Birleştir" iletişim kutusunda:

• Birleştirilecek anahtar sütun adına tıklayın ve ardından "Anahtar Anahtarı"na tıklayın.
• Ardından, anahtar sütuna göre verilerini birleştirmek istediğiniz başka bir sütuna tıklayın ve "Hesaplama" alanından açılır listeyi seçerek birleştirilmiş verileri ayırmak için bir ayırıcı seçin.
• Sonra, Tamam düğmesine tıklayın.

Aynı değere dayalı olarak başka bir sütundan gelen tüm karşılık gelen değerler, tek bir hücrede birleştirilmiştir. Ekran görüntüleri:

İpuçları: Hücreleri birleştirirken yinelenen içerikleri kaldırmak istiyorsanız, iletişim kutusundaki "Yinelenen Değerleri Sil" seçeneğini işaretleyin. Bu, yalnızca benzersiz girişlerin tek bir hücrede birleştirilmesini sağlar, böylece verileriniz herhangi bir fazladan çaba harcamadan daha temiz ve düzenli hale gelir. Ekran görüntüleri:

Kutools for Excel'i şimdi indirin ve ücretsiz deneyin!

Kullanıcı Tanımlı İşlev ile birden fazla değeri tek bir hücreye döndürme

Yukarıdaki TEXTJOIN işlevi yalnızca Excel 2019 ve Office 365 için kullanılabilir, eğer daha düşük Excel sürümlerin varsa, bu görevi tamamlamak için bazı kodlar kullanmalısınız.

  Tüm eşleşen değerleri tek bir hücreye döndürme

1. "ALT + F11" tuşlarını basılı tutun ve bu işlem "Microsoft Visual Basic for Applications" penceresini açar.

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

VBA kodu: Birden fazla değeri tek bir hücreye döndürmek için Vlookup

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. Ardından bu kodu kaydedip kapatın, çalışma sayfasına geri dönün ve şu formülü girin: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") Sonuçları yerleştirmek istediğiniz belirli boş bir hücreye yazın, ardından doldurma tutamacını aşağı çekerek tek bir hücrede istediğiniz tüm karşılık gelen değerleri alın, aşağıdaki ekran görüntüsüne bakın:

  Tek bir hücreye yinelenenler olmadan tüm eşleşen değerleri döndürme

Dönen eşleşen değerlerdeki yinelenenleri yok saymak için lütfen aşağıdaki kodu kullanın.

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

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

VBA kodu: Yinelenen eşleşen değerleri tek bir hücreye döndürmek için Vlookup

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. Kodu ekledikten sonra, açılan "Microsoft Visual Basic for Applications" penceresinde "Araçlar" > "Referanslar" seçeneğine tıklayın ve ardından açılan "Referanslar – VBAProject" iletişim kutusunda "Kullanılabilir Referanslar" listesi kutusundan "Microsoft Scripting Runtime" seçeneğini işaretleyin, aşağıdaki ekran görüntülerine bakın:

4. Ardından iletişim kutusunu kapatmak için Tamam'a tıklayın, kod penceresini kaydedip kapatın, çalışma sayfasına dönün ve şu formülü girin: =MultipleLookupNoRept(E2,$A$2:$C$11,3) Sonucu çıkarmak istediğiniz boş bir hücreye yazın ve ardından doldurma tutamacını aşağı çekerek tüm eşleşen değerleri alın, aşağıdaki ekran görüntüsüne bakın:

TEXTJOIN gibi formülleri dizi fonksiyonlarıyla birleştirenlerden, Kutools for Excel veya Kullanıcı Tanımlı Fonksiyon gibi araçlardan yararlanana kadar tüm yaklaşımlar karmaşık arama görevlerini basitleştirir. Size en uygun yöntemi seçin. Eğer daha fazla Excel ipucu ve püf noktası keşfetmeye ilgi duyuyorsanız, web sitemiz binlerce öğretici sunmaktadır.

Daha fazla ilgili makale: