INDEX-MATCH ile Birden Çok Ölçütlü Arama

Bazen tek bir sütun bir satırı benzersiz biçimde tanımlamaz. Belirli bir bedendeki ürünün fiyatına veya belirli bir departmandaki çalışanın maaşına ihtiyaç duyabilirsiniz.

Bu durumda tam olarak bir satırı bulmak için iki ya da daha fazla sütunda aynı anda eşleştirme yapan çok ölçütlü arama gerekir.

INDEX-MATCH, ek yardımcı sütunlara gerek kalmadan koşulları tek bir eşleştirme testinde birleştirerek bunu zarif bir şekilde gerçekleştirir.

En basit zihinsel model, anahtar sütunlarınızı tek bir sütunda birleştirmektir. Ürün ile bedeni birbirine ekleyen bir yardımcı sütun oluşturun ve ardından bu sütunda sıradan bir arama yapın.

Örneğin bir yardımcı hücrede =A2&"|"&B2 bulunabilir ve bu hücre "Shirt|Large" değerini üretebilir. Ardından MATCH ile "Shirt|Large" ifadesini bu birleştirilmiş sütunda ararsınız.

Bu yöntem çalışır; ancak sayfanızı gereksiz yere karmaşıklaştırır. Sonraki bölümlerde yardımcı sütunu tamamen nasıl kaldıracağınızı göreceksiniz.

Temel hile, iki koşul testini MATCH içinde çarpmaktır.

(A2:A10=G1), ilk ölçüt için TRUE/FALSE dizisi oluşturur. (B2:B10=G2) de ikinci ölçüt için aynısını yapar. Bunları (A2:A10=G1)*(B2:B10=G2) şeklinde çarpmak, yalnızca her ikisinin de TRUE olduğu yerlerde 1, diğer yerlerde 0 üretir.

Ardından MATCH, her iki koşulu da karşılayan satırı bulmak için 1 değerini arar.

Hesap tablolarında TRUE, 1; FALSE ise 0 gibi davranır. Bu iki değeri çarpmak mantıksal bir AND işlemini taklit eder:

• 1 çarpı 1 = 1 (her iki koşul da karşılanır)
• 1 çarpı 0 = 0
• 0 çarpı 1 = 0
• 0 çarpı 0 = 0

Dolayısıyla yalnızca her iki ölçütün de karşılandığı satırlar 1 üretir. Diğer tüm satırlar 0 olur. Bu tek 1, istediğimiz satırı işaretler.

Şimdi çarpılmış diziyi MATCH içine alarak tam olarak 1 değerini arayın.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0), her iki koşulun da TRUE olduğu ilk satırın konumunu döndürür.

Eşleşen birleşim dördüncü veri satırındaysa MATCH, 4 değerini döndürür. INDEX'in yanıtı getirmek için ihtiyaç duyduğu konum budur.

Bu MATCH sonucunu, örneğin C2:C10 aralığındaki fiyat gibi, gerçekten istediğiniz sütundaki INDEX işlevine verin.

Tam formül şu anlama gelir: C2:C10 aralığından, ürünün G1'e ve bedenin G2'ye eşit olduğu satırdaki değeri döndür.

Bu, yardımcı sütun gerektirmeyen ve verilerinizi yeniden düzenlemenizi istemeyen gerçek bir çok ölçütlü aramadır.

Bu formül, koşul dizilerini değerlendirir. Güncel Excel ve Google Sheets sürümlerinde Enter tuşuna basmanız yeterlidir.

Daha eski Excel sürümlerinde (dinamik dizilerden önce) formülü Ctrl+Shift+Enter ile dizi formülü olarak onaylamanız gerekir; bu işlem küme parantezleri ekler. Eski Excel'de sonuç yanlışsa veya bir hata görünüyorsa genellikle eksik olan adım bu onaydır.

Üç ölçüte mi ihtiyacınız var? Bir test daha çarpmanız yeterlidir. Örneğin D sütunundaki bir rengi G3 girdisiyle de eşleştirmek istediğinizi varsayalım.

Her ek (range=criterion) çarpanı sonucu daha da daraltır. Yalnızca tüm koşulların TRUE olduğu satırlarda çarpım 1 olarak kalır; herhangi bir FALSE değeri tüm çarpımı 0'a dönüştürür.

Bu düzen, ihtiyaç duyduğunuz kadar sütuna uygulanabilir.

Veriler: A = ürün, B = beden, C = fiyat. "Large" bedenindeki bir "Shirt" ürününün fiyatını bulmak istiyorsunuz.

• G1 = "Shirt", G2 = "Large".
• Koşul dizileri yalnızca Shirt+Large satırında, örneğin 4. satırda, 1 üretir.
• MATCH(1, ..., 0), 4 değerini döndürür.
• INDEX(C2:C10, 4), o satırın fiyatını döndürür.

Girdilerden birini değiştirirseniz formül doğru satırı anında yeniden bulur.

Şu noktaları aklınızda tutun:

• Eşit aralıklar: Her koşul aralığı ve INDEX sütunu aynı yüksekliğe sahip olmalıdır.
• Eşleşme olmaması: Hiçbir satır tüm ölçütleri karşılamazsa MATCH, #N/A döndürür. Tüm ifadeyi IFERROR içine alın.
• Yinelenenler: Birden fazla satır eşleşirse MATCH yalnızca ilkini döndürür. Ölçütlerinizi sonucu benzersiz kılacak kadar belirgin seçin.

Birden fazla satır eşleşebiliyor ve yalnızca birini getirmek yerine değerlerini toplamak istiyorsanız, SUMPRODUCT dizi olarak girilen INDEX-MATCH formülüne temiz bir alternatiftir.

Koşul dizilerini değer sütunuyla çarpar ve sonuçları toplar; böylece yalnızca her iki ölçütü de karşılayan satırlar sonuca katkıda bulunur. SUMPRODUCT dizileri yerel olarak işlediği için Ctrl+Shift+Enter kullanmanız gerekmez.

Tek bir eşleşen değeri getirmek için INDEX-MATCH, tüm eşleşmeler üzerinde toplama yapmak için SUMPRODUCT kullanın.

Birden çok ölçütlü arama bilginizi sınayın.

INDEX-MATCH ile birden çok ölçütlü aramalar için:

• Koşul dizilerini birbiriyle çarpın: (A=G1)*(B=G2) yalnızca tüm koşulların sağlandığı yerde 1 verir (mantıksal AND).
• MATCH(1, ..., 0) bu satırın konumunu bulur.
• INDEX(returnCol, position) değeri döndürür.

Ek koşullar için daha fazla *(range=criterion) çarpanı ekleyin, aralıkların yüksekliğini eşit tutun, eski Excel sürümlerinde Ctrl+Shift+Enter ile onaylayın ve IFERROR ile hataları yönetin.