Excel'de Birden Fazla Kritere Göre Arama Nasıl Yapılır?

DÜŞEYARA neden iki koşulla arayamaz?

DÜŞEYARA tek bir aranan değer alır ve onu tablonun yalnız ilk sütununda arar. Fiyat listenizde aynı ürün birden fazla bedenle geçiyorsa "Tişört" araması ilk bulduğu satırı, yani S bedeni getirir. M bedenin fiyatını almak için ürün ve beden koşulunu birlikte vermeniz gerekir.

Bunun dört yolu var: yardımcı sütun, ÇAPRAZARA, İNDİS ile KAÇINCI ve sonuç sayıysa ÇOKETOPLA. Hangisini seçeceğiniz Excel sürümünüze ve tablonun yapısına bağlıdır.

Yardımcı sütunla DÜŞEYARA'yı kullanmaya devam etmek

En eski sürümlerde bile çalışan yol, iki ölçütü tek bir sütunda birleştirmektir. Tablonun soluna yeni bir sütun ekleyip =B2&"|"&C2 yazarsınız ve "Tişört|M" gibi anahtarlar elde edersiniz. Arama da =DÜŞEYARA("Tişört|M";A2:D7;4;YANLIŞ) olur.

Araya konan dikey çizgi gereksiz görünebilir ama önemlidir. Ayraç olmadan "AB" ile "C" ve "A" ile "BC" aynı anahtarı, "ABC"yi üretir. Yardımcı sütunu sonradan gizleyebilirsiniz.

ÇAPRAZARA ile tek formülde iki koşul

Microsoft 365 ve Excel 2021 kullanıyorsanız yardımcı sütuna gerek yoktur. ÇAPRAZARA, aranan diziyi formül içinde birleştirmenize izin verir. A2:A7&B2:B7 ifadesi her satır için "TişörtS", "TişörtM" gibi bir değer üretir.

=ÇAPRAZARA("Tişört"&"M";A2:A7&B2:B7;C2:C7)

=XLOOKUP("Tişört"&"M",A2:A7&B2:B7,C2:C7)

Bu yöntemde sonuç sütununu ayrıca seçtiğiniz için fiyatın aranan sütunun sağında olması gerekmez. Koşul sayısını artırmak için & ile üçüncü bir sütunu da ekleyebilirsiniz. Renk sütunu da olan bir tabloda aranan değer "Tişört"&"M"&"Lacivert", aranan dizi de A2:A7&B2:B7&C2:C7 olur.

ÇAPRAZARA'nın bir başka yazımı birleştirme yerine koşulları çarpar: =ÇAPRAZARA(1;(A2:A7="Tişört")*(B2:B7="M");C2:C7). Bu biçim, ayraç sorununu tamamen ortadan kaldırır. Koşullardan biri "büyüktür" gibi bir karşılaştırma olduğunda da çalışır; örneğin fiyatı 500 TL'yi geçen ilk gömleği bulmak için ikinci koşulu (C2:C7>500) yaparsınız.

İNDİS ve KAÇINCI ile sürümden bağımsız arama

ÇAPRAZARA olmayan sürümlerde İNDİS ile KAÇINCI aynı sonucu verir. Buradaki fikir şudur: her koşul DOĞRU ya da YANLIŞ üretir, bunları çarptığınızda yalnız iki koşulu da sağlayan satır 1 olur. KAÇINCI bu 1'in yerini bulur, İNDİS de o satırdaki fiyatı getirir.

=İNDİS(C2:C7;KAÇINCI(1;(A2:A7="Gömlek")*(B2:B7="L");0))

=INDEX(C2:C7,MATCH(1,(A2:A7="Gömlek")*(B2:B7="L"),0))

Excel 2019 ve öncesinde bu formülü Enter yerine Ctrl+Shift+Enter ile girmeniz gerekir. Formül çubuğunda kıvrık parantezleri görürseniz dizi formülü olarak girilmiş demektir. Microsoft 365'te normal Enter yeterlidir.

Sonuç sayıysa ÇOKETOPLA yeter

Aradığınız değer fiyat, stok ya da tutar gibi bir sayıysa ve her kombinasyon tabloda bir kez geçiyorsa ÇOKETOPLA en sade çözümdür. Koşulları sütun sütun yazarsınız ve formül tek eşleşen satırın değerini döndürür.

=ÇOKETOPLA(C2:C7;A2:A7;"Gömlek";B2:B7;"M")

=SUMIFS(C2:C7,A2:A7,"Gömlek",B2:B7,"M")

Bu yolun bir tuzağı vardır. Aynı ürün ve beden tabloda iki kez yazılmışsa ÇOKETOPLA iki fiyatı toplar ve hata vermeden yanlış sonuç döndürür. Kombinasyon bulunamazsa da #YOK yerine 0 verir; sıfır fiyat gerçek bir değer gibi görünebilir.

Ölçütleri hücreye bağlamak

Formüle "Tişört" ve "M" gibi sabit metinler yazmak yerine ölçütleri hücrelerden okumak raporu yeniden kullanılabilir yapar. E2'ye ürünü, F2'ye bedeni yazdığınızda formül =ÇAPRAZARA(E2&F2;A2:A100&B2:B100;C2:C100;"Bulunamadı") olur. Dördüncü argüman, eşleşme olmadığında #YOK yerine gösterilecek metindir.

E2 ve F2'ye veri doğrulamayla açılır liste eklerseniz kullanıcı yalnız tabloda geçen ürün ve bedenleri seçebilir. Bu, yazım hatasından doğan boş sonuçları büyük ölçüde ortadan kaldırır.

Birleştirme yönteminde aralık seçimi de önemlidir. A:A&B:B gibi tam sütun başvurusu her hesaplamada bir milyondan fazla satırı birleştirir ve dosyayı belirgin biçimde yavaşlatır. Aralığı gerçek veri boyutuna yakın tutun ya da tabloyu Excel tablosuna dönüştürüp sütun adlarıyla başvurun.

Hangi yöntemi seçmelisiniz?

Microsoft 365 ya da Excel 2021 kullanıyorsanız ÇAPRAZARA'lı formül hem kısa hem okunaklıdır. Dosyayı eski sürüm kullanan kişilerle paylaşıyorsanız İNDİS ile KAÇINCI ya da yardımcı sütun daha güvenlidir. Eşleşen birden çok satırın hepsini görmek istiyorsanız arama yerine FİLTRE fonksiyonuna geçmelisiniz.

Sıradaki adım olarak kendi tablonuzda tekrarlayan kombinasyon olup olmadığını kontrol edin. =ÇOKEĞERSAY(A2:A7;A2;B2:B7;B2) sonucu 1'den büyük çıkan satırlar, aramanın hangi değeri getireceğini belirsizleştirir.