Excel'de XLOOKUP mu DÜŞEYARA mı Kullanmalısınız?

XLOOKUP mu DÜŞEYARA mı sorusu genelde bir satış tablosunda ürün kodunu yazınca fiyatın otomatik gelmesini istediğiniz anda çıkar karşınıza. Elin refleksle DÜŞEYARA'ya gitmesi doğal, formül yıllardır böyle öğretiliyor. Ama DÜŞEYARA'nın üç can sıkıcı huyu var ve XLOOKUP tam olarak bu huyları düzeltmek için tasarlandı.

Aşağıda ikisini gerçek formül örnekleriyle, tuzaklarıyla ve hangi durumda hangisini seçmeniz gerektiğiyle karşılaştırıyorum. Kullandığınız Excel sürümü, tabloyu kimlerle paylaşacağınız ve veri boyutunuz, bu seçimi doğrudan etkiliyor.

DÜŞEYARA gerçekte nasıl çalışır ve nerede tıkanır

DÜŞEYARA dört parametre ister: aranan değer, arama yapılacak tablo, döndürülecek sütunun numarası ve eşleşme tipi. Mantığı basit: soldaki sütunda bir değer arar, eşleşmeyi bulduğu satırdan N. sütundaki veriyi getirir.

=DÜŞEYARA(A2;Tablo1;3;YANLIŞ)

Bu formülde A2 hücresindeki değer Tablo1'in ilk sütununda aranıyor, eşleşme bulunursa üçüncü sütundaki veri dönüyor. Sondaki YANLIŞ parametresi tam eşleşme istendiğini belirtiyor; bu parametreyi boş bırakmak, DÜŞEYARA'yla ilgili hataların büyük bölümünü açıklıyor.

DÜŞEYARA yalnızca soldan sağa arar; aradığınız değer arama yapılan sütunun solundaysa formül o veriye ulaşamaz, tabloyu yeniden düzenlemek gerekir. Sütun numarası da sabit kodludur: tabloya yeni bir sütun eklediğinizde formül eski numarayı okumaya devam eder, hata vermez ama sessizce yanlış veriyi döndürür. Eşleşme tipini belirtmeyi atlarsanız da DÜŞEYARA varsayılan olarak yaklaşık eşleşmeye kayar ve aradığınız tam değer yerine ona en yakın olanı getirir.

Bu üç sorun tek başına küçük görünür ama yüzlerce satırlık bir raporda üst üste binince ciddi bir güven kaybına yol açar. Bir muhasebe tablosunda yanlış sütuna işaret eden tek bir DÜŞEYARA, fark edilmeden haftalarca hatalı rakam üretebilir.

XLOOKUP bu sorunları nasıl çözüyor

XLOOKUP, sütun numarası yerine iki ayrı aralık ister: arama yapılacak dizi ve döndürülecek dizi.

=XLOOKUP(A2;B:B;C:C;"Bulunamadı")

Bu formülde A2 değeri B sütununda aranıyor, eşleşme bulunursa C sütunundaki karşılık dönüyor. Tabloya sütun eklense de silinse de formül bozulmuyor; çünkü sabit bir sütun numarasına değil, doğrudan aralık referansına bağlı çalışıyor.

Dördüncü parametre de kritik: "Bulunamadı" metni, eşleşme yoksa #YOK hatası yerine doğrudan hücrede görünür. DÜŞEYARA'da aynı sonucu almak için formülü EĞERHATA içine sarmanız gerekir:

=EĞERHATA(DÜŞEYARA(A2;Tablo1;3;YANLIŞ);"Bulunamadı")

İki formülü yan yana koyunca fark netleşiyor: XLOOKUP aynı işi tek katmanda yapıyor, DÜŞEYARA ise iki fonksiyonu iç içe geçirmeyi gerektiriyor. XLOOKUP varsayılan olarak tam eşleşme yapıyor; eşleşme parametresini girmeyi atlasanız bile yaklaşık eşleşme tuzağına düşmüyorsunuz. INDEX ve KAÇINCI ikilisi de var; XLOOKUP'tan önce soldan sağa arama sınırını aşmanın standart yolu buydu ve bazı çok kriterli arama senaryolarında hâlâ tercih ediliyor:

=INDEX(C:C;KAÇINCI(A2;B:B;0))

Bu üç formülü aynı tabloda deneyip sonuçları karşılaştırmak, aradaki farkı ezberlemekten çok daha kalıcı bir öğrenme yolu.

Performans, yatay arama ve çift yönlü eşleştirme

Binlerce satırlık bir veri kümesinde DÜŞEYARA her hesaplamada arama aralığının tamamını tarıyor. XLOOKUP, arama ve döndürme dizilerini ayrı ele aldığı için bazı senaryolarda daha az iş yapıyor; döndürülecek sütun arama sütunundan uzaksa bu fark daha belirgin hissediliyor. Asıl performans kazancı ise formül seçiminden bağımsız bir alışkanlıktan geliyor: aralığı tüm sütun yerine (B:B yerine B2:B5000 gibi) yalnızca veri içeren kısımla sınırlamak.

• Arama aralığını veri sınırıyla daraltın, tüm sütunu taratmayın.
• Aynı aramayı tabloda tekrar tekrar yapmak yerine sonucu bir yardımcı sütunda tutun ve oradan referans verin.
• Çok sayıda XLOOKUP veya DÜŞEYARA içeren dosyalarda otomatik hesaplamayı elle hesaplamaya alıp F9 ile tetiklemek, işlemi hızlandırır.

DÜŞEYARA yalnızca dikey arama yapar; satır bazlı bir arama gerektiğinde ayrı bir fonksiyon olan YATAYARA devreye girer. XLOOKUP bu ayrımı ortadan kaldırıyor: aynı fonksiyon, arama dizisinin yönüne göre hem dikey hem yatay çalışıyor. İki yönlü arama, yani hem satır hem sütun başlığına göre bir hücreyi bulmak, iki XLOOKUP'ı iç içe geçirerek daha okunabilir biçimde yazılabilir:

=XLOOKUP(A2;B1:F1;XLOOKUP(A3;A2:A10;B2:F10))

Burada dıştaki XLOOKUP sütun başlığını eşleştiriyor, içteki XLOOKUP satır etiketini buluyor. İlk bakışta karmaşık görünse de tek satırda okunabilmesi, ayrı yardımcı sütunlar açmaya kıyasla büyük kolaylık sağlıyor.

Hangi Excel sürümünde hangisi çalışıyor

XLOOKUP, Microsoft 365 ve Excel 2021 sonrası sürümlerde mevcut; daha eski bir sürümde fonksiyon adı tanınmıyor ve hücrede #AD? hatası çıkıyor. Dosyayı eski sürümdeki biriyle paylaşacaksanız bu ciddi bir risk taşır; herkesin aynı sürümü kullanmadığı kurumsal ortamlarda DÜŞEYARA hâlâ daha güvenli bir varsayılan. Yeni kurduğunuz ve yalnızca kendi güncel sürümünüzde açacağınız bir çalışma kitabında ise XLOOKUP'ı tercih etmemek için pratik bir sebep yok.

Bu tablo bir geçiş kararı vermeyi de kolaylaştırıyor: satırların çoğunda XLOOKUP öne çıksa da, paylaşılan dosyalarda son sözü sürüm gereksinimi satırı söylüyor.

Joker karakter, yaklaşık eşleşme ve formül hataları

Bir müşteri adının yalnızca bir kısmını bildiğinizde joker karakterler işe yarıyor; ikisi de yıldız (*) ve soru işareti (?) destekliyor. Ama eşleşme modu parametresiyle XLOOKUP'ta kontrol daha ince: en yakın küçük değeri mi yoksa en yakın büyük değeri mi arayacağınızı ayrıca belirtebilirsiniz.

=XLOOKUP(D2;E:E;F:F;"Bulunamadı";-1)

Buradaki -1, tam eşleşme yoksa bir alt değeri getir anlamına geliyor; bu davranış vergi dilimi ya da komisyon aralığı gibi sayısal kademelerde işe yarıyor. DÜŞEYARA'da aynı sonuç için eşleşme tipini YANLIŞ yerine DOĞRU yapmak yeterliydi ama hangi yönde yuvarlanacağını seçemiyordunuz.

#YOK hatası, arama değeri tabloda bulunamadığında çıkıyor; en sık sebep, iki tablodaki verinin biri metin biri sayı formatında tutulması. Görünmeyen boşluk karakterleri de aynı şekilde eşleşmeyi engelliyor, özellikle dışarıdan içe aktarılan verilerde. Bu durumda KIRP fonksiyonuyla veriyi temizlemek çözüm oluyor:

=XLOOKUP(KIRP(A2);KIRP(B:B);C:C;"Bulunamadı")

Formülü önce küçük bir örnek tabloda test edip sonucu gözle doğrulamak, binlerce satırlık tabloya uygulamadan önce hatayı yakalamanın en pratik yolu. Yeni bir tabloda çalışıyorsanız ve herkes güncel Microsoft 365 kullanıyorsa XLOOKUP'a geçmek işinizi gerçekten kolaylaştırır; eski dosyalarla ya da farklı sürümlerle uğraşıyorsanız DÜŞEYARA'yı bir kenara bırakmayın. Fonksiyon referanslarının tam parametre listesi ve güncellemeler için Microsoft'un resmi Excel işlev dokümantasyonuna bakabilirsiniz.

Yazan Celil Uyanikoglu

Bilgisayar mühendisiyim; 25 yılı aşkın süredir bilgi işlem sektörünün içindeyim. Bu blogu 2020'de, işimde her gün karşılaştığım sorunların çözümlerini bir yere yazmak için açtım: Linux, güvenlik, tarayıcılar, yapay zeka araçları. Yazdığım her rehberi önce kendi bilgisayarımda ya da sunucumda deniyorum; çalıştığını görmediğim adımı yayınlamam. Hatalı ya da eskimiş bir şey görürsen iletişim sayfasından yaz — düzeltirim.