Excel’de VLOOKUP ile çoklu eşleşen değerleri nasıl arayıp birleştirebilirim?
Excel'de VLOOKUP işlevi, belirli bir arama ölçütüne göre bulduğu ilk eşleşen değeri döndürür. Ancak bir dersteki tüm öğrencileri listelemek veya belirli bir kategoriye ait tüm ürünleri toplamak gibi yaygın senaryolarda, aynı anahtarla ilişkili tüm eşleşen değerleri alıp tek bir hücrede birleştirmeniz gerekebilir. Standart VLOOKUP bu tür durumlarda yetersiz kalır; peki hem arama yapmayı hem de çoklu sonuçları tek bir hücrede birleştirmeyi nasıl başarabilirsiniz? Aşağıda, farklı Excel sürümlerine ve kullanıcı tercihlerine uygun olarak bu görevi gerçekleştirmenin birkaç pratik ve etkili yöntemini inceleyeceğiz.
Excel'de VLOOKUP ile Çoklu Karşılık Gelen Değerleri Arayıp Birleştirme
TEXTJOIN ve FILTER İşlevleri ile Vlookup ve Çoklu Karşılık Gelen Değerleri Birleştirme
Excel 365 veya Excel 2021 kullanıyorsanız, TEXTJOIN ve FILTER işlevlerinin birleşimi, eşleşen tüm değerleri arayıp birleştirmek için verimli ve formül temelli güçlü bir çözüm sunar. Bu yaklaşım özellikle dinamik ve sürekli güncellenen veri kümeleri için idealdir; çünkü kaynak veri değiştiğinde sonuçlar otomatik olarak yenilenir. Çözüm, Excel sürümünüz FILTER işlevini desteklediğinde en iyi şekilde çalışır—bu işlev yalnızca en son Office sürümlerinde yer alır.
Hedef hücreye aşağıdaki formülü girin ve diğer satırlara da uygulamak istiyorsanız, formülü aşağı doğru sürükleyin. Böylece tüm eşleşen karşılık gelen değerler çıkarılır ve tek bir hücrede birleştirilir. Ekran görüntüsüne göz atın:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))
- FILTER($B$2:$B$16, $A$2:$A$16=D2, "")Formülün bu kısmı, $A$2:$A$16 aralığındaki her bir değeri D2 hücresindeki değerle karşılaştırır; eşleşme olması durumunda, $B$2:$B$16 aralığındaki ilgili değer sonuç dizisine dahil edilir.
- $B$2:$B$16: Eşleşen değerlerin alınacağı aralık.
- $A$2:$A$16=D2Değerlerin seçildiği koşul: Yalnızca $A$2:$A$16 aralığında D2 hücresindeki içerikle eşleşen satırlar işlenecektir.
- TEXTJOIN(", ", TRUE, …): Bu işlev, FILTER işlevinin çıktısını (eşleşmelerden oluşan bir dizi) alır ve belirtilen ayırıcıya (virgül ve boşluk) göre tek bir metin dizesinde birleştirir; aynı zamanda boş girişleri otomatik olarak yok sayar.
- ",": Ayırıcı olarak virgül ve boşluk kullanılır; gerektiğinde bu sembolü, örneğin noktalı virgül veya satır sonu gibi başka ayırıcılara değiştirebilirsiniz.
- TRUE: Birleştirme işlemi sırasında boş hücreleri yok sayar ve böylece düzenli, biçimlendirilmiş bir çıktı elde edersiniz.
Özel not: Bu yöntem yalnızca Excel 365 veya Excel 2021 ile uyumludur ve eski sürümlerde (örneğin Excel 2019, 2016 veya daha önceki sürümler) çalışmaz. Uygulamadan önce mutlaka Excel sürümünüzü kontrol edin.
İpucu: Arama değeriniz (örneğin D2) değişirse ya da Veri Aralığı listesine yeni eşleşen öğeler eklenirse, sonuç ek adımlara gerek kalmadan otomatik olarak güncellenir.
Olası sınırlamalar: Çok büyük veri kümelerinde formül hesaplama süresi uzayabilir. Ayrıca, arama veya sonuç aralıklarının birleştirilmemiş olduğundan emin olun; aksi takdirde formül hatalarına neden olabilir.
Kutools for Excel ile Vlookup ve Çoklu Karşılık Gelen Değerleri Birleştirme
Yerleşik formül yöntemleri sizce karmaşıksa ya da Excel sürümünüz TEXTJOIN ve FILTER gibi gelişmiş işlevleri desteklemiyorsa, Kutools for Excel kullanıcı dostu bir grafiksel çözüm sunar. Kutools’un Çoklu Arama özelliği, birkaç basit adımda eşleşen tüm sonuçları aramanıza ve tek bir hücrede birleştirmenize olanak tanır; böylece hem yeni başlayanlar hem de deneyimli kullanıcılar için ideal bir seçenektir. Kutools ile ne karmaşık formüller ne de kod yazmanız gerekir; özellikle büyük veya değişken veri kümelerinde tekrarlayan arama ve toplama işlemleri yaparken son derece kullanışlıdır.
Kutools for Excel'ı yükledikten sonra aşağıdaki adımları izleyin:
Kutools > Süper ARA > Birçoklu arama (birden fazla sonuç döndürme) seçeneğine tıklayarak kurulum iletişim kutusunu açın. Bu iletişim kutusunda aşağıdaki adımları kullanarak arama ve çıktı ayarlarınızı hızlıca yapılandırabilirsiniz:
- Birleştirilmiş sonuçların görüntüleneceği hedef hücreleri ve aramak istediğiniz değerleri içeren hücreleri seçin;
- Arama anahtarını ve sonuç sütunlarını içeren tablo aralığını belirtin;
- Arama anahtarlarının (Anahtar Sütun) bulunduğu sütunu ve birleştirilecek değerlerin (Dönüş sütunu) bulunduğu sütunu belirtin;
- Ayarlarınızı onaylamak ve verileri işlemek için **Tamam** düğmesine tıklayın.
Sonuç: Kutools, seçtiğiniz çıktı hücresinde tüm eşleşen ve birleştirilmiş değerleri şimdi gösterecektir. Ekran görüntüsüne göz atın:
Bu yöntem, karmaşık formüller veya kodlar olmadan Excel arayüzünden çalışmayı tercih edenler için şiddetle önerilir. Ayrıca formül hataları olasılığını azaltır ve tekrarlayan arama ve birleştirme görevlerini yürütmede üretkenliği artırır.
Kullanıcı Tanımlı İşlev ile Vlookup ve Çoklu Karşılık Gelen Değerleri Birleştirme
VBA (Visual Basic for Applications) konusunda yetkin olan kullanıcılar veya dinamik dizi ile FILTER işlevi desteğine sahip olmayan eski Excel sürümlerini kullananlar, çoklu sonuçları esnek bir şekilde birleştiren özel bir Kullanıcı Tanımlı İşlev (UDF) oluşturabilir. Bu yöntem tüm Excel sürümleriyle uyumludur ve belirli ayırıcı karakterler veya koşullar temelinde özelleştirilebilir.
1. ALT + F11 tuşlarına basarak 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: Bir hücrede Vlookup ile Çoklu Eşleşen Değerleri Arayıp 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. VBA düzenleyicisini kaydedip kapatın. Çalışma sayfanıza dönün ve sonucunuzu almak istediğiniz boş bir hücreye şu formülü girerek bu UDF’yi kullanın: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16). Formülü diğer hücrelere kopyalamak için doldurma tutamacını aşağı doğru sürükleyin. Belirli bir arama değerine göre tüm eşleşen sonuçlar, virgül ve boşlukla ayrılmış şekilde tek bir hücrede birleştirilip döndürülecektir. Ekran görüntüsüne bakın:
- D2: Veri kümenizde eşleştirilecek arama değeri (LookupValue).
- A2:A16: İşlevin arama değerini (LookupRange) aradığı aralık.
- B2:B16: Arama değeriyle eşleşen hücreler için birleştirilecek değerleri içeren aralık (ReturnRange).
VBA Kodu ile Vlookup ve Çoklu Karşılık Gelen Değerleri Birleştirme
Tekrarlayan kullanım senaryolarında veya çalışma sayfası hücrelerinde özel işlevlerden kaçınmak isteyenler için, sonuçları doğrudan birleştiren hazır bir VBA makrosundan yararlanabilirsiniz. Bu yöntem, aynı sürüm ya da eklentilere sahip olmayan kullanıcıların yer aldığı paylaşımlı ortamlarda sorunsuz çalışır.
1. VBA düzenleyicisini açmak için Geliştirici Araçları > Visual Basic seçeneğine tıklayın.
2. VBA penceresinde Ekle > Modül seçeneğine tıklayın ve ardından bu kodu modüle yapıştırın:
Sub VLookupAndConcatenate()
Dim ws As Worksheet
Dim dataRange As Range, lookupRange As Range, resultRange As Range
Dim dict As Object
Dim i As Long, lastRow As Long
Dim lookupValue As Variant, result As String
Dim delimiter As String
delimiter = ", "
Set dict = CreateObject("Scripting.Dictionary")
Set ws = ActiveSheet
On Error Resume Next
Set dataRange = Application.InputBox( _
Prompt:="Please select the data range (contains lookup column and result column)", _
Title:="Select Data Range", _
Type:=8)
On Error GoTo 0
If dataRange Is Nothing Then Exit Sub
On Error Resume Next
Set lookupRange = Application.InputBox( _
Prompt:="Please select the lookup range (single column)", _
Title:="Select Lookup Range", _
Type:=8)
On Error GoTo 0
If lookupRange Is Nothing Then Exit Sub
On Error Resume Next
Set resultRange = Application.InputBox( _
Prompt:="Please select the starting cell for results output", _
Title:="Select Output Location", _
Type:=8)
On Error GoTo 0
If resultRange Is Nothing Then Exit Sub
resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
For i = 1 To dataRange.Rows.Count
lookupValue = dataRange.Cells(i, 1).Value
If Not dict.Exists(lookupValue) Then
dict.Add lookupValue, dataRange.Cells(i, 2).Value
Else
dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
End If
Next i
For i = 1 To lookupRange.Rows.Count
lookupValue = lookupRange.Cells(i, 1).Value
If dict.Exists(lookupValue) Then
resultRange.Cells(i, 1).Value = dict(lookupValue)
Else
resultRange.Cells(i, 1).Value = "Not Found"
End If
Next i
MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub
3. Makroyu çalıştırmak için 
düğmesine tıklayın. Giriş kutuları, Veri Aralığı, arama aralığı ve sonuç aralığını seçmenizi isteyecektir. Birleştirilmiş sonuçlar, seçtiğiniz çıktı hücrelerinde doğrudan görüntülenecektir.
Bu makro yaklaşımı, Farklı değer ile sık sık çoklu birleştirme aramaları gerçekleştiriyorsanız özellikle kullanışlıdır; çünkü çalışma sayfasını UDF çağrılarıyla doldurmaktan kaçınmanızı sağlar.
Ayırıcıyı kodda kolayca ayarlayabilir ve makroyu iş akışınızın gereksinimlerine göre sonuçları bir hücreye ya da dosyaya aktaracak şekilde genişletebilirsiniz.
Excel'de çoklu karşılık gelen değerleri birleştirmek, durumunuza göre çeşitli yaklaşımlarla mümkündür ve her biri belirli avantajlar sunar. Dinamik dizi formüllerini, Kutools for Excel gibi eklentileri veya VBA tabanlı yöntemleri kullanmak, gruplanmış verileri analiz etme ve görüntüleme yeteneğinizi artırır. Veri kümenizin boyutuna ve karmaşıklığına bağlı olarak, sizin veya ekibiniz için en iyi performansı ve bakım kolaylığını sağlayacak yöntemi değerlendirin. Günlük işlemlerinizde veri tutarlılığını kontrol edin, birleştirme hatalarını önleyin ve en iyi sonuçlar için başvuru aralıklarını doğrulayın. Formül hesaplamalarında hata oluşursa, aralıklarınızın veriyle eşleştiğini ve Excel sürümünüz için doğru formül giriş yöntemini kullandığınızı tekrar kontrol edin.
Daha gelişmiş Excel teknikleri ve çok çeşitli pratik nasıl yapılır kılavuzları için kapsamlı eğitim kütüphanemizi ziyaret edin.
En İyi Office Üretkenlik Araçları
Excel becerilerinizi Kutools for Excel ile geliştirin ve verimlilikte hiç yaşamadığınız bir deneyim yaşayın.Kutools for Excel, üretkenliği artırmak ve Zamanı Kaydet için 300’den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen edinmek için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli bir arayüz kazandırarak işlerinizi çok daha kolay hale getirir.
- Word, Excel, PowerPoint, Publisher, Access, Visio ve Project'te sekmeli düzenleme ve okuma özelliğini etkinleştirin.
- Belgeleri yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açıp oluşturun.
- Günlük üretkenliğinizi %50 artırır ve her gün yüzlerce fare tıklamasından tasarruf etmenizi sağlar!
Tüm Kutools eklentileri — tek bir kurulum dosyasında!
Kutools for Office paketi, Excel, Word, Outlook ve PowerPoint eklentilerini ve ayrıca Office Tab Pro’yu içeren kapsamlı bir çözümdür; Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans— birkaç dakikada hazır (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı üretkenlik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— ayrı ayrı eklenti satın almaya göre tasarruf sağlar