Excel’in XLOOKUP işlevi, genellikle tek boyutlu veri aramalarında kullanılır ancak doğru şekilde yapılandırıldığında iki boyutlu yani hem satır hem sütun bazlı aramalar için de güçlü bir çözüm sunar. Excel 2021 ve sonrası sürümlerde ya da Microsoft 365 üzerinden bu özellik rahatlıkla kullanılabilir. Peki, Excel’de XLOOKUP ile iki yönlü arama işlemi nasıl yapılır?
XLOOKUP Fonksiyonunun Temel Yapısı
XLOOKUP, bir değeri arayıp buna karşılık gelen değeri döndürmek için kullanılır. Fonksiyonun temel söz dizimi ise şu şekilde:
=XLOOKUP(arama_değeri, arama_dizisi, dönüş_dizisi, [bulunamazsa], [eşleşme_modu], [arama_yönü])
XLOOKUP işlevi, Excel’de veri aramayı ve eşleştirmeyi kolaylaştıran güçlü bir araç. Bu işlevin altı ana argümanı bulunur:
- a (Gerekli): Arama Değeri: Aranacak olan değer.
- b (Gerekli): Arama Dizisi: Arama değerinin bulunacağı aralık.
- c (Gerekli): Dönüş Dizisi: Arama değeri bulunduğunda döndürülecek olan değeri içeren aralık.
- d (İsteğe Bağlı): Bulunamayan Metin: Arama değeri (a), arama dizisinde (b) bulunamazsa döndürülecek özel bir metin belirleyebilirsiniz. Eğer bu argüman atlanırsa Excel eşleşme bulunamadığında sıfır döndürür.
- e (İsteğe Bağlı): Eşleşme Modu: Eşleşme tipini belirler:
- 0: Tam eşleşme (varsayılan).
- -1: Tam eşleşme veya sonraki daha küçük öge.
- 1: Tam eşleşme veya sonraki daha büyük öge.
- 2: Joker karakter eşleşmesi.
- f (İsteğe Bağlı): Arama Modu: Arama yönünü ve yöntemini belirler:
- 1: Yukarıdan aşağıya veya soldan sağa arama (varsayılan).
- -1: Aşağıdan yukarıya veya sağdan sola arama.
- 2: Arama dizisi (b) artan düzende iken ikili arama.
- -2: Arama dizisi (b) azalan düzende iken ikili arama.
Eğer isteğe bağlı argümanlar olan e ve f boş bırakılırsa e argümanı için tam eşleşme; f argümanı için ise yukarıdan aşağıya veya soldan sağa arama varsayılan olarak uygulanır.
💡Örnek kullanım:
=XLOOKUP(F2,A2:A100,D2:D100,"Geçersiz ID",0,1)
G2 hücresine bu formülü yazdığınızda, sonuç olarak 48 puanını elde edersiniz:
📝 Formül şu şekilde işler:
- F2 hücresindeki kimlik değerini (a argümanı) alır.
- Bu kimliği A2’den A100’e kadar olan hücrelerde (b argümanı) arar.
- Eşleşen kimliğin karşılığı olan değeri D2’den D100’e kadar olan hücrelerden (c argümanı) döndürür.
- Eğer F2 hücresindeki kimlik, A2’den A100’e kadar olan listede yoksa “Geçersiz Kimlik” (d argümanı) ifadesini döndürür.
- Beşinci argüman olan 0, Excel’e sadece tam eşleşmeleri döndürmesini (e argümanı) söyler.
- Altıncı argüman olan 1 ise aramanın belirtilen aralığın en üstünden en altına doğru yapılmasını (f argümanı) sağlar.
Bu formül, F2 hücresindeki ID’yi A2:A100 aralığında arar ve eşleşen satırdaki D sütunundaki değeri döndürür. Eğer hücre içerisinde değer bulunamazsa “Geçersiz ID” ifadesi gösterilir.
XLOOKUP İle İki Yönlü Arama Formülü
Tek yönlü aramalar, belirli bir satır veya sütun verisine ulaşmayı sağlar. Ancak bazı durumlarda hem satır hem sütun değerine göre bir kesişim noktasındaki veriye ulaşmak gerekebilir. Bu tür aramalar için iç içe XLOOKUP kullanılır:
İki yönlü arama yapmak için bir XLOOKUP işlevini başka bir XLOOKUP işlevinin içine yerleştirmeniz gerekir:
=XLOOKUP(a¹, b¹, XLOOKUP(a², b², c))
📝 Bu formülde:
- a¹ ve b¹, ilk değişken için arama değerini ve arama dizisini temsil eder.
- a² ve b², ikinci değişken için arama değerini ve arama dizisini ifade eder.
- c ise sonuç olarak döndürülecek olan veri dizisi.
Her XLOOKUP işlevi için isteğe bağlı olan d, e ve f argümanlarını tek yönlü bir arama yaparken olduğu gibi ekleyebilirsiniz. Ancak anlatımı basitleştirmek adına bu örneklerde ve söz diziminde ilgili argümanların çıkarıldığını belirtmekte fayda var.
💡Örnek Kullanım:
=XLOOKUP(F2,A2:A100,XLOOKUP(G1,B1:D1,B2:D100))
G2 hücresine aşağıdaki formülü yazdığınızda, sonuç olarak 48 puanını elde edersiniz:
📝 Formül şu şekilde çalışır:
Excel, ilk olarak belirtilen kimliği (a¹ argümanı) A2’den A100’e kadar olan hücrelerde (b¹ argümanı) arar. Ardından belirtilen değişken (a² argümanı), B1’den D1’e kadar olan hücrelerde (b² argümanı) aranır. Son olarak bu iki aramanın kesişim noktasında B2’den D100’e kadar olan hücrelerdeki (c argümanı) ilgili değer döndürülür.
Bunun bir sonucu olarak G1 hücresindeki değişkeni “Yaş” olarak değiştirdiğinizde formül, 41 sonucunu verir.
Benzer şekilde G1 hücresindeki değeri, “Cinsiyet” olarak değiştirdiğinizde ise sonuç olarak “M” değerini alırsınız.
İki boyutlu XLOOKUP formüllerinde XLOOKUP işlevlerinin hangi sırada iç içe yerleştirildiğinin bir önemi yok. Bu örnekte kimlik araması, formülde ilk sırada yer alırken sütun başlığı araması ise iç içe kullanılmış. Bu sıralamanın değiştirilmesi durumunda dahi aynı sonuca ulaşılır.
Excel’de Veri Doğrulama Listesi Ekleyerek Kullanımı Kolaylaştırmak
İki yönlü arama işlemini daha kullanıcı dostu hâle getirmek için açılır listelerle arama hücrelerini dinamik hâle getirebilirsiniz. Bunun için:
✅ G1 hücresini seçin.
✅ Veri sekmesine tıklayın ve “Veri Doğrulama” seçeneğine girin.
✅ Açılan pencerede “İzin Ver” alanından “Liste” seçeneğini işaretleyin.
✅ Kaynak olarak sütun başlıklarını; yani B1:D1 aralığını seçin.”Tamam” butonuna tıkladığınızda G1 hücresinde bir açılır düğme belirecek. Bu düğmeye tıkladığınızda, değişken seçenekleri otomatik olarak listelenecek.
✅ Aynı işlemi diğer arama hücresi olan F2 için de tekrarlayın. Kaynak alanında gelecekteki veri eklemelerini hesaba katmak için mevcut veri kümenizin boyutunu aşan bir aralık belirtmeniz gerekir (örneğin veri kümeniz 100 satır içeriyorsa kaynağı A1000 hücresine kadar genişletebilirsiniz). İki nokta üst üste işaretinden sonraki nokta (bu bağlamda “trim referans operatörü” olarak da bilinir), Excel’e kaynak aralığının sonundaki boş hücreleri otomatik olarak kesmesini emreder.
Alternatif olarak, A sütunundaki değerleri içeren hücre sayısını sayan ve bu sayıda seçeneği açılır menüde döndüren bir formül de kullanabilirsiniz. Bu adımların ardından artık F2 hücresindeki açılır listeden kolayca bir kimlik seçebileceksiniz.
Eski Excel Sürümleri için Alternatif: INDEX ve MATCH
Excel 2019 veya daha eski sürümlerde XLOOKUP kullanılamaz. Bu durumda iki yönlü arama işlemleri INDEX ve MATCH fonksiyonlarıyla gerçekleştirilebilir. Ancak bu yöntemler daha karmaşık formüller gerektirdiğinden XLOOKUP daha pratik bir çözüm olarak öne çıkar.
Kaynak: www.howtogeek.com
