XLOOKUP: Daha Esnek ve Güçlü Arama

Bu derste ne yapacağız?

Bu derste XLOOKUP işlevini öğreneceksiniz. Türkçe Excel'de adı genellikle ÇAPRAZARA olarak görünür. Üç şeyi uygulayacağız: işlevin söz dizimini yazmak, aranan değerin solundaki sütundan sonuç döndürmek ve değer bulunamadığında hata yerine kendi mesajınızı göstermek. Ayrıca DÜŞEYARA ile karşılaştıracak ve kendi Excel'inizde işlevin bulunup bulunmadığını kontrol edeceksiniz.

Ön koşul: Hücre başvurularını (örneğin A2:A11) bilmeniz ve önceki derste gördüğümüz DÜŞEYARA mantığına aşina olmanız yeterli. Bir önceki derste, bir tabloda ilk sütuna göre arama yapmayı ve bunun sınırlarını gördünüz. Bugün o sınırların çoğunu ortadan kaldıran yeni işleve geçiyoruz. Bir sonraki derste ise verinin kendisini renklerle ve simgelerle konuşturan koşullu biçimlendirmeye geçeceğiz.

Not: Excel'in arayüzü ve işlev adları sürüme ve dil ayarına göre değişebilir. Aşağıdaki menü adları genel bir yol gösterir; sizde küçük farklar olabilir.

Kavram

Bir apartmanın zil panosunu düşünün. DÜŞEYARA, yalnızca daire numarasını görüp yanındaki isimleri okuyabilen, ama isimden numaraya geri dönemeyen bir kişi gibidir. Kişi sadece soldan sağa bakar. XLOOKUP ise panonun her yerine bakabilen kişidir: Nerede arayacağını ve nereden cevap alacağını ayrı ayrı söylersiniz. İkisi birbirinden bağımsız olduğu için cevap solda da olsa sağda da olsa fark etmez.

İşlevin söz dizimi şöyledir:

=ÇAPRAZARA(aranan_değer; arama_dizisi; dönüş_dizisi; [bulunamazsa]; [eşleştirme_modu]; [arama_modu])

Türkçe Excel'de bağımsız değişkenler noktalı virgülle (;) ayrılır. İngilizce ayarlı Excel'de ise virgül (,) kullanılır ve işlev adı XLOOKUP'tır. Bağımsız değişkenleri tek tek açıklayalım:

• aranan_değer: Aradığınız şey. Örneğin bir ürün kodu.
• arama_dizisi: Aranan değerin bulunacağı tek sütun ya da tek satır.
• dönüş_dizisi: Sonucun alınacağı sütun ya da satır. Arama dizisiyle aynı sayıda satır içermelidir.
• bulunamazsa (isteğe bağlı): Değer bulunamazsa gösterilecek metin ya da değer. Böylece #YOK hatasıyla uğraşmazsınız.
• eşleştirme_modu (isteğe bağlı): Varsayılan 0'dır, yani tam eşleşme. -1 tam eşleşme yoksa bir küçüğünü, 1 bir büyüğünü getirir. 2 ise joker karakterli (* ve ?) eşleşmedir.
• arama_modu (isteğe bağlı): Varsayılan 1'dir, yani baştan sona arar. -1 sondan başa arar; bu, bir değer birden çok kez geçiyorsa sonuncusunu bulmak için işe yarar.

Dikkat edin: DÜŞEYARA'daki sütun indeks numarası (2, 3, 4...) yoktur. Bu yüzden tabloya yeni sütun eklediğinizde formül kaymaz ve bozulmaz. Ayrıca DÜŞEYARA'nın sonuna yazmayı unutup hataya yol açtığımız "YANLIŞ" (tam eşleşme) bağımsız değişkenine de gerek kalmaz; XLOOKUP varsayılan olarak tam eşleşme yapar.

Adım adım uygulama

Aşağıdaki örnek tabloyu yeni bir çalışma sayfasına yazın. Ürün kodu bilerek en sağdaki sütunda; DÜŞEYARA ile bu aramayı yapamazdık.

• Adım 1 – Verileri girin: Boş bir sayfada A1 hücresine tıklayın ve yukarıdaki tabloyu başlıklarıyla birlikte yazın. Veriler A1:C6 aralığında olmalı.
• Adım 2 – Arama hücresini hazırlayın: E1 hücresine Aranan kod, E2 hücresine Ürün adı, E3 hücresine Fiyat yazın. F1 hücresine tıklayıp D-202 yazın ve Enter'a basın.
• Adım 3 – Sola doğru arama yapın: F2 hücresine tıklayın ve formül çubuğuna şunu yazın: =ÇAPRAZARA(F1;C2:C6;A2:A6). Enter'a basın. Sonuç olarak Defter görmelisiniz. Burada F1 aranan koddur, C2:C6 arama yapılan sütundur, A2:A6 ise sonucun alındığı sütundur. Dönüş sütunu arama sütununun solunda; yine de formül sorunsuz çalışır.
• Adım 4 – Fiyatı getirin: F3 hücresine =ÇAPRAZARA(F1;C2:C6;B2:B6) yazın. Sonuç 40 olmalı. Yalnızca dönüş sütununu değiştirdiğinize dikkat edin.
• Adım 5 – Bulunamayan değeri yönetin: F1 hücresine kayıtlı olmayan bir kod, örneğin X-999 yazın. F2 hücresi artık #YOK hatası gösterecektir. F2'ye tekrar tıklayıp formülü şu hale getirin: =ÇAPRAZARA(F1;C2:C6;A2:A6;"Kayıt bulunamadı"). Enter'a bastığınızda hata yerine Kayıt bulunamadı yazısını görürsünüz. Dördüncü bağımsız değişken tam olarak bunu yapar. Metni tırnak içine almayı unutmayın.
• Adım 6 – Aynı anda birden çok sütun döndürün: F1'e tekrar K-101 yazın. G5 hücresine tıklayıp =ÇAPRAZARA(F1;C2:C6;A2:B6) yazın. Dönüş dizisi iki sütunu kapsadığı için sonuç yana doğru taşar: hem ürün adı hem fiyat görünür. Bu özellik, sürümünüzün dinamik dizileri desteklemesine bağlıdır. Taşma olmazsa bu adımı atlayıp Adım 3 ve 4'teki tek sütunlu yöntemi kullanın.
• Adım 7 – Sürümünüzü kontrol edin: XLOOKUP, Microsoft 365'te, Excel 2021 ve sonrasında ve web sürümünde bulunur; Excel 2019 ve daha eski sürümlerde yoktur. Kontrol etmek için boş bir hücreye =ÇAPR yazmaya başlayın. Açılan öneri listesinde ÇAPRAZARA görünüyorsa işlev sizde vardır. Görünmüyorsa aynı işi DÜŞEYARA veya İNDİS ile KAÇINCI birleşimiyle yapmanız gerekir. Ayrıca dosyayı XLOOKUP'a sahip olmayan biriyle paylaşacaksanız, o kişinin dosyada #AD? hatası görebileceğini unutmayın.

DÜŞEYARA ile kısa karşılaştırma

Sık karşılaşılan sorunlar

Mini görev

Yukarıdaki tabloya iki yeni sütun ekleyin: Stok ve Tedarikçi. Sonra arama alanınızı geliştirin. F1'e yazılan ürün koduna göre stok ve tedarikçi bilgisini getiren iki ayrı formül yazın. Kayıt yoksa Böyle bir ürün yok mesajını gösterin. Bunu yaparken sütun numarası kullanmadığınıza dikkat edin.

• Veri tablosunda en az beş ürün var mı?
• Arama kodu tablodaki sütunun solunda değil, sağında bir yerde mi? (Sola aramayı denemek için kod sütununu bilerek sağda bırakın.)
• Formüller ÇAPRAZARA işlevini kullanıyor mu?
• Olmayan bir kod yazınca #YOK yerine kendi mesajınız görünüyor mu?
• Tabloya yeni bir sütun ekleyince formüller hâlâ doğru çalışıyor mu?

Kendini sına

• Soru 1: XLOOKUP'ın Türkçe Excel'deki karşılığı nedir?
• Soru 2: XLOOKUP'ta arama dizisi ile dönüş dizisi neden ayrı ayrı belirtilir ve bunun DÜŞEYARA'ya göre avantajı nedir?
• Soru 3: Aranan değer bulunamadığında #YOK yerine özel bir mesaj göstermek için hangi bağımsız değişken kullanılır?
• Soru 4: Bir değer listede birden çok kez geçiyorsa sonuncusunu bulmak için hangi bağımsız değişken değiştirilir ve hangi değer verilir?
• Soru 5: Dosyayı Excel 2019 kullanan bir arkadaşınıza göndereceksiniz. XLOOKUP formülleri orada neden sorun çıkarabilir?

Cevaplar

• Cevap 1: ÇAPRAZARA. İngilizce ayarlı Excel'de XLOOKUP olarak görünür.
• Cevap 2: İki dizi ayrı olduğu için dönüş sütunu aramanın solunda da sağında da olabilir. Ayrıca sütun numarası gerekmediğinden tabloya sütun eklemek formülü bozmaz.
• Cevap 3: Dördüncü bağımsız değişken olan bulunamazsa kullanılır; örneğin "Kayıt bulunamadı" yazılır.
• Cevap 4: Altıncı bağımsız değişken olan arama_modu değiştirilir ve -1 verilir; böylece arama sondan başa doğru yapılır.
• Cevap 5: Excel 2019 ve daha eski sürümlerde bu işlev bulunmaz; formül tanınmaz ve #AD? hatası görülebilir. Bu durumda DÜŞEYARA ya da İNDİS ile KAÇINCI tercih edilmelidir.

Bu derste esnek aramanın temelini attınız. Bir sonraki derste, elinizdeki veriyi renklerle, simgelerle ve kurallarla görünür kılan koşullu biçimlendirmeye geçeceğiz. Böylece az önce bulduğunuz sonuçları da bir bakışta yorumlayabileceksiniz.