Excel'de Birden Fazla Kriterle INDEX MATCH Nasıl Yapılır

Birkaç yıl önce, satış performansını birden çok bölge arasında karşılaştırmam gereken bir pazarlama kampanyası analizi üzerinde çalıştım. Veriler birden fazla Excel sayfasına dağılmıştı ve belirli ürün satış sayılarını tek bir özet rapora çekmem gerekiyordu. İlk başta verileri elle arayıp kopyalamayı denedim, ancak düşündüğüm kadar kolay olmadı. Yanlış olan tek bir satır bile tüm raporu bozabilirdi.

İşte o zaman INDEX MATCH()'i keşfettim. Formülü doğru yazmak için birkaç deneme yaptım, ama ihtiyacım olan tam sayıları ne kadar kolay bulup çekebildiğini görünce rutinimin bir parçası haline geldi. Yalnızca iki işleve dayanarak, veriler sayfalar arasında ne kadar dağınık olursa olsun, tam olarak ihtiyacım olan veriyi çekebiliyordum.

Bu yazıda, aynı şeyi INDEX() ve MATCH() işlevlerini kullanarak nasıl yapabileceğinizi açıklayacağım. Excel’de her zaman öğrenilecek daha çok şey var. Yeni başlıyorsanız Introduction to Excel kursumuzu şiddetle öneririm. Daha deneyimliyseniz, Advanced Excel Functions kursumuzu deneyin.

INDEX MATCH’e Kısa Bir Hatırlatma

INDEX MATCH, birlikte çalışarak gelişmiş aramalar yapan iki Excel işlevinin birleşiminden söz etmek için kullanılan kısaltılmış bir ifadedir. Bu fikre INDEX(MATCH()) de diyebiliriz, ancak bu yazıda INDEX MATCH ifadesini tercih edeceğim. Şimdi bunların her birine sırayla bakalım:

INDEX() işlevi, belirtilen bir aralıktaki konumuna göre bir hücrenin değerini getirir. Söz dizimi şöyledir:

=INDEX(array, row_num, [column_num])

Burada:

• array, değeri almak istediğiniz hücre aralığıdır.

• row_num, değerin döndürüleceği dizideki satır numarasıdır.

• column_num (isteğe bağlı), değerin döndürüleceği dizideki sütun numarasıdır.

MATCH() işlevi, bir aralıktaki bir değerin göreli konumunu belirler. Söz dizimi şöyledir:

=MATCH(lookup_value, lookup_array, [match_type])

Burada:

• lookup_value, bulmak istediğiniz değerdir.

• lookup_array, işlevin değeri aradığı aralıktır.

match_type isteğe bağlıdır. 1 (varsayılan) lookup_value değerine küçük veya ona eşit olan değeri bulur (dizi artan sırada sıralanmış olmalıdır). 0 tam eşleşme bulur (dizinin sıralanması gerekmez). -1 lookup_value değerine büyük veya ona eşit olan en küçük değeri bulur (dizi azalan sırada sıralanmış olmalıdır).

INDEX() ile MATCH() nasıl birleştirilir

MATCH()'i INDEX() içine gömerek dinamik bir arama oluşturabiliriz. Bunu bir örnekle anlayalım: Diyelim ki veri kümesinde David Wilson'ın konumunu bulmak istiyorsunuz. INDEX() içinde satır numarasını elle girmek yerine, bunu belirlemek için MATCH()'i kullanın:

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))

Yukarıdaki formülde MATCH("David Wilson", A2:A6, 0) 4 döndürür; bu satır konumudur. Ve INDEX(C2:C6, 4) C2:C6 aralığının 4. satırındaki değeri getirir; bu da Seattle’dır.

INDEX() ile MATCH()’i birleştirin. Görsel: Yazar.

Bunu daha da dinamik hale getirmek için, sabit yazılan David Wilson ifadesini bir hücre başvurusuyla değiştirebilirsiniz. Böylece formül, D4 hücresindeki değere göre otomatik olarak uyarlanır:

=INDEX(C2:C6,MATCH(D4,A2:A6,0))

INDEX MATCH’te sabit değeri değiştirin. Görsel: Yazar.

INDEX MATCH vs. VLOOKUP()

Artık INDEX() ve MATCH()’in ayrı ayrı nasıl çalıştığını ve birleştirildiklerinde aramaları nasıl daha dinamik hale getirdiğini bildiğinize göre, INDEX MATCH’in neden VLOOKUP()’tan daha iyi bir seçenek olduğuna bakalım.

• VLOOKUP() sol tarafta arama sütunu olmasını gerektirirken, INDEX MATCH konumdan bağımsız olarak herhangi bir sütundan veri getirmenize olanak tanır.

• INDEX MATCH, tüm tabloları tarayan VLOOKUP()’a kıyasla yalnızca gerekli hücre aralığını işler. 

• VLOOKUP() kullanan formüller, statik sütun indislerine dayandıkları için sütunlar eklendiğinde veya silindiğinde bozulabilir. Buna karşılık, INDEX MATCH formüllerinizin veri yapısındaki değişikliklere rağmen sağlam kalmasını sağlamak için dinamik aralıklara başvurur.

• INDEX MATCH ile sütun numaralarını manuel saymamıza gerek yok. Arama sütununu ve döndürme sütununu belirtmeniz yeterlidir. 

Birden Fazla Kriterle INDEX MATCH

Sıklıkla yinelenen kayıtlar içeren veri kümeleriyle çalışmam gerekiyor ve bu tiplerde değer bulmak oldukça zordur. Ancak artık INDEX MATCH kullanıyorum çünkü standart diğer arama formüllerinin aksine bu senaryoları çok kolay yönetiyor. Bunu adım adım nasıl kullandığımı göstereyim.

Birden fazla kriter için verileri hazırlayın

Önce veri kümenizi oluşturun ve her sütun için net başlıkların bulunduğu bir tablo halinde düzenli olduğundan emin olun. Her satır benzersiz bir kaydı temsil etmeli ve her sütun belirli bir veri niteliği içermelidir.

Örneğin, işte örnek bir veri kümesi:

Birden fazla kriter için INDEX MATCH veri kümesi. Görsel: Yazar.

Birden fazla kriter için formülü yazın

Verileriniz düzgün şekilde düzenlendikten sonra formülü yazma zamanı. INDEX MATCH formülü, birden fazla koşulu sağlayan satırı bularak başka bir sütundan bir değer getirir. Bu, MATCH() işlevi içinde mantıksal sınamaları birleştirip bunu INDEX() işlevinin içine gömerek yapılır.

Temel söz dizimi şöyledir: 

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Burada: 

• Return_range, değerin döndürüleceği aralıktır.

• Criteria1, Criteria2, … sağlanması gereken koşullardır.

• Range1, Range2, … kriterlere karşılık gelen aralıklardır.

Artık bir veri düzenimiz olduğuna göre, sorumuzun yanıtına daha yakından bakalım: INDEX MATCH birden fazla kriterle nasıl kullanılır.

Karmaşık kriterler için yardımcı sütunlar kullanın

Veri kümenizde birden fazla koşul varsa, süreci basitleştirmek için yardımcı sütunlar kullanın. Tüm koşulları tek bir sütunda birleştirerek aramaları kolaylaştırır. Örneğin, aynı veri kümesini kullanarak Ad ve Maaş sütunlarını birleştirerek bir yardımcı sütun oluşturuyorum:

=A2&B2

Yardımcı sütun oluşturun. Görsel: Yazar.

Bu yardımcı sütun, INDEX MATCH formülümü basitleştiriyor. Birden fazla koşul içeren karmaşık bir dizi formülü yazmak yerine, daha basit bir yaklaşım için formülde yardımcı sütuna başvurdum:

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))

Yardımcı sütunla INDEX MATCH. Görsel: Yazar.

Birden fazla kriteri dizi formülüyle birleştirin

Yardımcı sütunları tercih etmiyorsanız, aynı sonucu elde etmek için dizi formülleri kullanabilirsiniz. Bu sayede birden fazla kriteri doğrudan MATCH() işlevi içinde değerlendirebilirsiniz. Örneğin, Alice’in Maaşını İK departmanında şöyle buluyorum:

Adım 1: Mantıksal koşullarla MATCH() işlevini yazın:

 MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0)

Bu formülde 1, MATCH() işlevinin tüm koşulların doğru olduğu satırları aramasını sağlar. (F4=A2:A11), F4 hücresindeki değerin A2:A11 aralığındaki herhangi bir değerle eşleşip eşleşmediğini kontrol eder. (F5=B2:B11) ise F5 hücresindeki değerin B2:B11 aralığındaki herhangi bir değerle eşleşip eşleşmediğini kontrol eder. * operatörü bir VE (AND) mantığı gibi davranır ve tüm koşulların sağlanmasını garanti eder.

Adım 2: Bu MATCH() işlevini INDEX() işlevinin içine alın:

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))

Adım 3: Formülü tamamlayın. Excel’in eski sürümünü kullanıyorsanız, bir dizi formülü yapmak için Ctrl+Shift+Enter tuşlarına basın. Yeni sürümlerde Enter’a basmanız yeterlidir.

Birden fazla kriterle dizi INDEX MATCH. Görsel: Yazar.

Birden Fazla Kriterle INDEX MATCH’in İleri Kullanımları

INDEX MATCH ile çok daha fazlasını yapabilirsiniz. Nasıl olduğuna bakalım: 

Adlandırılmış ve dinamik aralıklarla INDEX MATCH kullanın

Excel’de adlandırılmış aralıkları standart A1:A10 gibi başvurular yerine results veya totalSales gibi anlamlı adlar vermek için kullanıyorum. Böylece farklı sayfalar arasında formülleri yönetmek kolaylaşıyor. 

Bir hücre aralığını adlandırmak için hücreleri seçin ve Ctrl + F3 (Windows) veya Cmd + F3 (Mac) tuşlarına basarak Ad Yöneticisini açın. Ardından Yeni’ye tıklayın, bir ad girin ve Tamam’a tıklayın.

Aralığa ad verin. Görsel: Yazar.

Adlandırılmış aralık ile dinamik aralık arasındaki tek fark, adlandırılmış aralığın sabit bir hücre grubuna atıfta bulunması, dinamik aralığın ise veriler eklendiğinde veya kaldırıldığında otomatik olarak ayarlanmasıdır.

Dinamik bir aralık ayarlamak için hücreleri seçin. Formüller sekmesinde Ad Yöneticisi’ne tıklayın veya Ctrl + F3 tuşlarına basarak Excel Ad Yöneticisi’ni açın ve Yeni’ye tıklayın. Yeni Ad ileti kutusu açılacaktır. Şimdi Ad alanına istediğiniz adı girin. Ardından Başvurulan yer alanına dinamik aralığın formülünü yazın. 

Dinamik aralık ayarlayın. Görsel: Yazar. 

Şimdi bir örneğe bakalım: İki dinamik ve bir statik aralık tanımladım:

• total_amount: =$F$2:INDEX($F:$F, COUNTA($F:$F))

• items_list: =$A$2:INDEX($A:$A, COUNTA($A:$A))

• lookup_value: =$I$3

Şimdi bu aralıkları INDEX MATCH formülünde kullanıyorum:

=INDEX(total_amount,MATCH(lookup_value,items_list,0))

Ve gördüğünüz gibi, açık adlarla formül çok daha anlaşılır hale geliyor.

INDEX MATCH ile dinamik ve adlandırılmış aralıkları kullanın. Görsel: Yazar.

Karmaşık aramalar için iç içe INDEX MATCH

Temel çalışmaların yanı sıra, karmaşık aramaları yönetmek için iç içe INDEX MATCH işlevlerini de kullanabilirsiniz. Örneğin, farklı bölgelerdeki ürün kategorilerine göre satışları gösteren bir veri kümem var. 

Ham veri kümesi. Görsel: Yazar.

Doğudaki East bölgesindeki mobilya satışlarını bulmak istiyorum. Ancak bunu yapmak için hem ürün kategorisini (satır) hem de bölgeyi (sütun) eşleştirmem gerekiyor; bunu temel bir INDEX MATCH yapamaz. Bu yüzden burada aşağıdaki iç içe INDEX MATCH formülünü kullanıyorum:

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))

Nasıl çalıştığı şöyle: INDEX() B2:D4 aralığından bir değer çeker, ancak tam olarak nerede araması gerektiğini söylemek için bir satır numarasına ve bir sütun numarasına ihtiyaç duyar. İlk MATCH(D6, A2:A4, 0) satır numarasını bulur. Eğer D6 hücresi Furniture içeriyorsa, A2:A4 sütununda arar ve bunu ikinci satırda bulur.

Sonra MATCH(D7, B1:D1, 0) sütun numarasını belirler. Eğer D7 hücresi East diyorsa, B1:D1 boyunca bakar ve bunu ikinci sütunda bulur.

INDEX() satır ve sütunu öğrendikten sonra çıktı değerini gösterir. Bizim örneğimizde Furniture için East bölgesindeki satışlar 450’dir.

İç içe INDEX MATCH kullanın. Görsel: Yazar.

Satırlar ve sütunlar arasında elle arama yapmak yerine bu formülü kullanıyorum çünkü her şeyi tam olarak ve hatasız şekilde hallediyor. 

Yaygın Zorluklar ve Sorun Giderme İpuçları

INDEX MATCH kullanmaya başladığımda birkaç zorlukla karşılaştım ve sizin aynı hayal kırıklıklarını yaşamanızı istemem. Bu yüzden en yaygın sorunları ve bunların nasıl aşılacağını anlatacağım.

INDEX MATCH formüllerinde hataları yönetin

#N/A ve #VALUE! gibi hatalar ilk bakışta can sıkıcı görünebilir, ancak düzeltmesi oldukça kolaydır. Soruna neyin yol açtığını nasıl tespit edeceğinizi ve bunu çözmenin basit adımlarını görelim.

#N/A hatası, MATCH() işlevi bir değer bulamadığında ortaya çıkar. Bunun nedeni arama değerinin arama dizisinde bulunmaması veya verilerde gizli boşluklar olmasıdır. Örneğin, bir keresinde Çalışan İsimlerini çekerken yanlış sütuna başvurmuştum:

=INDEX(B2:B6,MATCH(E3,C2:C6,0))

INDEX MATCH’te #N/A hatası. Görsel: Yazar.

Bu tür sorunları düzeltmek için arama değerinin dizide bulunduğunu doğrulayın ve boşlukları temizlemek için TRIM() işlevini kullanın:

=TRIM(INDEX(B2:B6,MATCH(E3,A2:A6,0)))

INDEX MATCH’te #N/A hatası düzeltildi. Görsel: Yazar.

#VALUE! hatası, formül dizi formülü olarak ayarlanmadığında görünür. Örneğin, MATCH() işlevini kullanıp birden fazla aralık dahil edersem, Excel bunu bir dizi formülü olarak görür. Ancak doğru şekilde ayarlanmamışsa Excel bir #VALUE! hatası verir.

=INDEX(C2:C6,MATCH(D4&E4,A2:A6&B2:B6,0))

INDEX MATCH’te #VALUE hatası. Görsel: Yazar.

Bunu çözmek için, formülü girdikten sonra Ctrl + Shift + Enter tuşlarına basın. Böylece Excel formülü, artık bir dizi formülü olduğunu belirten süslü parantezlerle {} sarmalar. Ancak bu parantezleri elle yazmayın; formül bozulur.

INDEX MATCH’te #VALUE hatası düzeltildi. Görsel: Yazar.

Büyük veri kümelerinde performansı optimize edin

Daha büyük veri kümelerinde formüllerim zaman zaman yavaşlıyordu ve bu yüzden hesaplamaların güncellenmesini beklemek zorunda kalıyordum. Siz de benzer sorunlarla boğuşuyorsanız, şu ipuçlarını deneyin: 

• Arama aralığını sınırlayın: Aralıkları yalnızca gerekli olanla sınırlandırın. Örneğin A:A yerine, hesaplama süresini azaltmak için A1:A100 kullanın.

• Yardımcı sütunlar kullanın: Karmaşık kriterleri yardımcı sütunlarla önceden hesaplayın. Bu, dizi formüllerinin hesaplama yükünü azaltır.

• El ile hesaplama modunu etkinleştirin: Excel’i sürekli yeniden hesaplamalardan kaçınmak için el ile hesaplama moduna alın. Değişikliklerden sonra formülleri manuel güncellemek için F9’a basın.

• Değişken (volatile) işlevlerden kaçının: NOW(), RAND() ve TODAY() gibi değişken işlevleri INDEX MATCH ile birlikte kullanmayı en aza indirin. Bu işlevler, çalışma kitabı her güncellendiğinde yeniden hesaplama tetikler.

Son Düşünceler 

INDEX MATCH teknikleri zaman kazandırır ve karmaşık veri analizini basitleştirir. Büyük veri kümeleriyle çalışıyorsanız denemeye değer. Ancak kalıcı bir anlayış için en iyi yol pratiktir. Bu yüzden birkaç veri kümesini ele alıp öğrendiklerinizi denemenizi öneririm. Ben de becerilerimi böyle geliştirdim. 

Bilginizi derinleştirmek için, daha geniş bir yelpazede güçlü araçları ustalıkla kullanmak üzere Advanced Excel Functions kursumuza göz atın. Ama Excel’de veri analizi konusunda kapsamlı bir uzmanlık oluşturmak istiyorsanız, Data Analysis in Excel kursumuzu öneririm. Veri hazırlamadan görselleştirmeye kadar her şeyi kapsar.