Excel'de bir hücrede birden çok değer döndürmek için vlookup nasıl yapılır?

Normalde Excel'de, DÜŞEYARA işlevini kullandığınızda, ölçütlerle eşleşen birden çok değer varsa, yalnızca ilkini alabilirsiniz. Ancak bazen, aşağıdaki ekran görüntüsü gibi kriterleri karşılayan tüm karşılık gelen değerleri tek bir hücreye döndürmek istersiniz, bunu nasıl çözebilirsiniz?

Vlookup, METİNBİRLEŞTİR işleviyle birden çok değeri tek bir hücreye döndürmek için (Excel 2019 ve Office 365)

• Tüm eşleşen değerleri tek bir hücreye döndürmek için Vlookup
• Vlookup, tüm eşleşen değerleri yinelenmeden tek bir hücreye döndürmek için

Vlookup, Kullanıcı Tanımlı İşlevle birden çok değeri tek bir hücreye döndürmek için

• Tüm eşleşen değerleri tek bir hücreye döndürmek için Vlookup
• Vlookup, tüm eşleşen değerleri yinelenmeden tek bir hücreye döndürmek için

Vlookup kullanışlı bir özellikle birden fazla değeri tek bir hücreye döndürmek için

Vlookup, METİNBİRLEŞTİR işleviyle birden çok değeri tek bir hücreye döndürmek için (Excel 2019 ve Office 365)

Excel 2019 ve Office 365 gibi Excel'in daha yüksek sürümüne sahipseniz, yeni bir işlev vardır - METİN BİRLEŞTİRME, bu güçlü işlevle, hızlı bir şekilde bakabilir ve eşleşen tüm değerleri tek bir hücreye döndürebilirsiniz.

  Tüm eşleşen değerleri tek bir hücreye döndürmek için Vlookup

Lütfen aşağıdaki formülü sonucu koymak istediğiniz boş bir hücreye uygulayın, ardından tuşuna basın Ctrl + Üst Karakter + Enter ilk sonucu elde etmek için anahtarları bir araya getirin ve ardından doldurma tutamacını bu formülü kullanmak istediğiniz hücreye sürükleyin ve aşağıdaki ekran görüntüsü gibi karşılık gelen tüm değerleri alacaksınız:

  Vlookup, tüm eşleşen değerleri yinelenmeden tek bir hücreye döndürmek için

Arama verilerine dayalı olarak eşleşen tüm değerleri yineleme olmadan 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 Ctrl + Üst Karakter + Enter ilk sonucu elde etmek için anahtarları bir araya getirin ve ardından bu formülü diğer hücreleri doldurmak için kopyalayın ve aşağıda gösterilen ekran görüntüsü gibi, karşılık gelen değerleri olmadan karşılık gelen tüm değerleri alacaksınız:

Vlookup, Kullanıcı Tanımlı İşlevle birden çok değeri tek bir hücreye döndürmek için

Yukarıdaki METİN BİRLEŞTİRME işlevi yalnızca Excel 2019 ve Office 365 için kullanılabilir; daha düşük Excel sürümlerine sahipseniz, bu görevi tamamlamak için bazı kodlar kullanmanız gerekir.

  Tüm eşleşen değerleri tek bir hücreye döndürmek için Vlookup

1. Basılı tutun ALT + F11 anahtarlar ve açılır Uygulamalar için Microsoft Visual Basic pencere.

2. tıklayın Ekle > modülve aşağıdaki kodu Modül Penceresi.

VBA kodu: Bir hücreye birden çok değer 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, ", ") Sonucu yerleştirmek istediğiniz belirli bir boş hücreye yerleştirin, ardından istediğiniz tüm ilgili değerleri tek bir hücrede almak için dolgu tutamacını aşağı sürükleyin, ekran görüntüsüne bakın:

  Vlookup, tüm eşleşen değerleri yinelenmeden tek bir hücreye döndürmek için

Döndürülen eşleşen değerlerde kopyaları yok saymak için lütfen aşağıdaki kodu uygulayın.

1. Basılı tutun Alt + F11 tuşlarını açmak için Uygulamalar için Microsoft Visual Basic pencere.

2. tıklayın Ekle > modülve aşağıdaki kodu Modül Penceresi.

VBA kodu: Vlookup ve tek bir hücreye birden fazla benzersiz eşleşen değer döndürür

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, ardından Tools > Referanslar açıldığında Uygulamalar için Microsoft Visual Basic pencerede ve ardından dışarıda Referanslar - VBAProject iletişim kutusu, işaretleyin Microsoft Komut Dosyası Çalışma Zamanı seçeneği Mevcut Referanslar liste kutusu, ekran görüntülerine bakın:

4. Sonra tıklayın OK iletişim kutusunu kapatmak, kod penceresini kaydedip kapatmak, çalışma sayfasına geri dönün ve şu formülü girin: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

Vlookup kullanışlı bir özellikle birden fazla değeri tek bir hücreye döndürmek için

 Eğer varsa Kutools for Excel, Onun ile Gelişmiş Kombine Satırları özelliği, satırları aynı değere göre hızlı bir şekilde birleştirebilir veya birleştirebilir ve ihtiyaç duyduğunuzda bazı hesaplamalar yapabilirsiniz.

Kurduktan sonra Kutools for Excellütfen aşağıdaki işlemleri yapın:

1. Bir sütun verilerini başka bir sütuna göre birleştirmek istediğiniz veri aralığını seçin.

2. tıklayın Kutools > Birleştir ve Böl > Gelişmiş Kombine Satırları, ekran görüntüsüne bakın:

3. Dışarı fırladı Gelişmiş Kombine Satırları iletişim kutusu:

• Temel olarak birleştirilecek anahtar sütun adını tıklayın ve ardından Birincil anahtar
• Ardından, verilerini anahtar sütununa göre birleştirmek istediğiniz başka bir sütunu tıklayın ve Birleştirmek Birleştirilmiş verileri ayırmak için bir ayırıcı seçmek için.

4. Sonra tıklayın OK düğmesi ve aşağıdaki sonuçları alacaksınız:

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

Daha ilgili makaleler: