XLOOKUP(): Excel’in En Esnek Arama (Lookup) İşlevi

Eğer daha önce VLOOKUP() kullandıysanız ve sola doğru arama yapmanız gerektiğinde takıldıysanız, XLOOKUP() bu sorunu çözer. Bu, Excel’in daha yeni arama işlevidir. Tek bir basit formülle VLOOKUP(), HLOOKUP() ve hatta çoğu INDEX() ile MATCH() formülünün yerini alabilir.

XLOOKUP() her yönde arama yapabilir, aynı anda birden fazla sütunu döndürebilir ve bir değer eksik olduğunda özel bir mesaj gösterebilir. 

Bu rehberde önce sözdizimini ele alacağız. Ardından tek bir basit veri setini baştan sona kullanarak gerçek formülleri adım adım kuracağız. Ayrıca tam ve yaklaşık eşleşmeleri, birden fazla sütun döndürmeyi, hataları nasıl ele alacağınızı ve XLOOKUP()’ın VLOOKUP() ve INDEX()/MATCH() ile karşılaştırmasını da kapsayacağız. 

Excel’de XLOOKUP() Nasıl Kullanılır

Sıfırdan gerçek bir XLOOKUP() formülü oluşturalım. 

Veri setimizde beş ürün var; her birinin bir ID’si, adı, kategorisi ve fiyatı bulunuyor. A’dan D’ye sütunlarında, 1. satırda başlıklarla yer alıyorlar.

XLOOKUP() için veri seti. Görsel: Yazar.

Diyelim ki “P104”ün fiyatını bulmak istiyoruz. Bunu adım adım nasıl yapacağımıza bakalım.

Tek bir değeri arayın

Sonucun görünmesini istediğiniz boş bir hücreye tıklayın. Ben F2’yi kullanıyorum. Ve şu formülü yazın:

=XLOOKUP("P104", A2:A6, D2:D6)

Bu formülde: 

• "P104" lookup_value’dır, yani aradığımız öğe

• A2:A6 lookup_array’dır, Excel’in arama yaptığı aralık

• D2:D6 return_array’dır, Excel’in yanıtı çektiği aralık

Excel, A sütunu “Product ID”de “P104”ü arar, 5. satırda bulur ve D sütunundaki karşılık gelen değeri döndürür: $45.

"P104"ü doğrudan yazmak yerine bir hücreye başvurabilirsiniz; böylece formülün kendisini düzenlemeden aranan değeri değiştirebilirsiniz. Bizim örneğimizde A5 hücresi "P104" içeriyor, dolayısıyla formül şu hale gelir:

=XLOOKUP(A5, A2:A6, D2:D6)

Excel’de XLOOKUP() kullanın. Görsel: Yazar.

Artık A5’e herhangi bir ürün ID’si yazabilirsiniz; fiyat otomatik olarak güncellenir.

Başka bir sütundan değer döndürün

Bu adım, XLOOKUP() ile VLOOKUP() arasındaki asıl farkı gösterir. Arama sütununuzun sağındaki bir sütundan değer döndürmekle sınırlı değilsiniz.

Diyelim ki fiyat yerine “P102” için kategori istiyorsunuz. Aynı kurulum; sadece return_array’i C sütununa, “Category”ye yönlendirin:

=XLOOKUP(A3, A2:A6, C2:C6)

Burada A3, Home değerini döndürür. A5’e değiştirirseniz Electronics olarak güncellenir. Benzer şekilde, return_array’i tekrar değiştirerek ürün adını, fiyatı veya başka bir sütunu çekebilirsiniz; hepsi için aynı lookup_value ve lookup_array’i kullanırsınız.

XLOOKUP() başka bir sütundan değer döndürür. Görsel: Yazar.

VLOOKUP() ile, arama sütununuzdan itibaren sütunları sayar ve farklı bir sonuç istediğiniz her seferinde sütun dizin numarasını ayarlarsınız. XLOOKUP() ile, doğrudan istediğiniz sütunu işaret edersiniz. 

Formülü birden çok satıra kopyalayın

Şimdiye kadar bir defada bir değer aradık. Ancak çoğu gerçek çalışma sayfası aynı anda birçok değeri aramaya ihtiyaç duyar; örneğin her biri eşleşen bir fiyata ihtiyaç duyan sipariş ID’lerinin tam listesi gibi.

Bunu şöyle kurarız: A sütunu, A2’den başlayarak Ürün ID’lerimizin listesini zaten içeriyor. Her birinin yanına, F sütununa eşleşen fiyatların gelmesini istiyoruz.

Bunun için F2’ye tıklayın ve formülü yazın; aranan değer için A2’ye referans verin:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Şimdi tekrar F2’ye tıklayın ve doldurma tutamacını (hücrenin sağ alt köşesindeki küçük kare) satırlarınızın geri kalanına doğru aşağı sürükleyin.

Excel formülü aşağı kopyalar ve her satır artık kendi Ürün ID’si için doğru fiyatı döndürür. A2’nin ID’si fiyatını F2’de alır, A3’ünki F3’te ve bu şekilde devam eder.

Formülü diğer hücrelere aşağı kopyalayın. Görsel: Yazar.

A2:A6 ve D2:D6 etrafındaki dolar işaretlerine dikkat edin. Buna mutlak başvuru denir. Aralığı sabitleyerek formülü aşağı kopyaladığınızda kaymasını engeller. 

Bu burada önemlidir; çünkü her satır aynı ürün listesini aramalı, sadece her seferinde farklı bir aranan değerle.

XLOOKUP()’ta Tam Eşleşme ve Yaklaşık Eşleşme Nasıl Kullanılır

Varsayılan olarak XLOOKUP() tam eşleşme arar. Bir tane bulamazsa, daha önce ele aldığımız if_not_found bağımsız değişkenini kurmadıysanız hata döndürür. 

Ancak XLOOKUP() match_mode bağımsız değişkenini kullanarak en yakın eşleşmeyi de bulabilir. 

Tam eşleşme (Varsayılan)

Bunun için ekstra bir şey yazmanıza gerek yok. Şimdiye kadar kurduğumuz tüm formüller zaten tam eşleşme kullanıyor; çünkü bu XLOOKUP()’ın varsayılan davranışıdır.

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel A2’deki tam değeri arar ve yalnızca arama aralığında o birebir eşleşmeyi bulursa sonuç döndürür.

Bunu açıkça yazmak isterseniz, dördüncü bağımsız değişken olarak sıfır ekleyin:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() tam eşleşme döndürür. Görsel: Yazar.

0’dan önceki boşluğa dikkat ettiniz mi? Bu, if_not_found’ı atlar (çünkü burada boş bırakıyoruz) ve doğrudan match_mode’a geçer. Çoğu zaman bu bağımsız değişkeni tamamen boş bırakabilir ve Excel’in kendiliğinden tam eşleşmeyi varsayılan yapmasına izin verebilirsiniz.

Yaklaşık eşleşme

Şimdi, verileriniz her olası arama için bir değere sahip değil diyelim. Toplam satışlarının hangi aralığa düştüğüne bağlı olarak satış temsilcilerinin farklı oranlar kazandığı bir komisyon yapısını düşünün. Her dolar tutarı için bir satırınız olmayacak, sadece her aralığın başlangıç noktası olacak.

İşte yaklaşık eşleşmenin faydalı olduğu yer burasıdır. 

match_mode’u -1 yapın; XLOOKUP() aranan değerden küçük veya ona eşit en büyük değeri bulur.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Diyelim ki arama aralığınız $0, $5.000 ve $10.000 satış eşiklerini tutuyor ve her biri farklı bir komisyon oranına bağlı. 

$7.500 satış yapan bir temsilci tam olarak hiçbir satırla eşleşmez. match_mode -1 olduğunda, XLOOKUP() $7.500’ü aşmayan en büyük eşik olan $5.000’i bulur ve o satırın komisyon oranını döndürür.

match_mode’u bunun yerine 1 yaparsanız XLOOKUP() mantığı tersine çevirir. Aranan değerden büyük veya ona eşit en küçük değeri bulur. Bu, aşağı değil yukarı yuvarladığınız kargo ağırlık dilimleri gibi durumlarda faydalıdır.

XLOOKUP() yaklaşık eşleşme döndürür. Görsel: Yazar.

Not: Yaklaşık eşleşme, arama aralığınızın sıralı olduğunu varsayar; -1 için artan, 1 için azalan. Doğru sıralanmamışsa, XLOOKUP() hata vermeden yanlış sonuç döndürebilir; bu da daha kötü bir durumdur. Bu yüzden buna güvenmeden önce sıralama düzeninizi mutlaka iki kez kontrol edin.

XLOOKUP() ile Sola Doğru Nasıl Arama Yapılır

VLOOKUP() aralığınızın yalnızca en soldaki sütununda arama yapar ve sağındaki bir sütundan değer döndürür; tersi yönde gidemez. Üçüncü bağımsız değişkeni bir aralık değil; sol kenardan sayılan bir sütun indeksidir. 

Ona “bir sütun sola git” demenin yolu yoktur; bu nedenle ihtiyacınız olan değer arama sütununuzun solundaysa, aramayı çalıştırmak için verilerinizi yeniden düzenlemek veya yardımcı bir sütun eklemek zorunda kalırsınız.

XLOOKUP() bu sınırlamaya sahip değildir; çünkü arama ve döndürme için ayrı aralıklara işaret edersiniz.

Ürün listemizi ele alalım: Ürün ID’si A sütununda, Ürün Adı B sütununda. 

Elinizde bir ürün adı olduğunu ve onun ID’sini bulmak istediğinizi varsayalım; bu, VLOOKUP()’ın tersidir.

C9’a Bluetooth Speaker (listede var olan bir ad) girin. E2’ye şunu yazın:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() B sütununda “Bluetooth Speaker”ı arar, 5. satırda bulur ve A sütunundan “P104”ü döndürür; A sütunu B’nin solunda olmasına rağmen. 

XLOOKUP(), VLOOKUP()’ın aksine sola doğru değerleri arar. Görsel: Yazar.

XLOOKUP() ile Birden Fazla Sütunu Nasıl Döndürürsünüz

XLOOKUP(), dinamik dizi özelliğini kullanarak tek bir formülle aynı anda birkaç sütunu döndürebilir. Bunu yapmak için return_array’i tek bir sütun yerine bir aralığa işaret ettirin; XLOOKUP() eşleşen tüm satırı gereken kadar hücreye “döker” (spill). 

Bunu ürün listemizle deneyelim. 

C9’a bir Ürün ID’si yazıyorum; örneğin “P104”. E2’ye şunu giriyorum:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Burada return_array B2:D6’dır; üç sütun genişliğinde olup Ürün Adı, Kategori ve Fiyatı kapsar. Enter’a bastığınızda XLOOKUP() A sütununda “P104”ü bulur; ardından Bluetooth Speaker, Electronics ve $45 değerlerini aynı anda döndürerek E9, F9 ve G9’a taşır.

XLOOKUP() tek formülle birden fazla sütun döndürür. Görsel: Yazar.

Spill aralığı (döküm aralığı) denen şey budur. Asıl formül E9’dadır; F9 ve G9 kendi formülleri olmadan kendiliğinden dolar. 

F9’a tıklayın; formül çubuğunda doğrudan yazılmış bir şey yerine orijinal formülün soluk (gri) bir sürümünü görürsünüz. E9’un dökümü aktifken F9’a yazmayı denereniz, Excel bir spill (döküm) hatası verir. 

Bunu nasıl düzelteceğimizi Yaygın Hatalar bölümünde ele alacağız.

XLOOKUP()’ta Eksik Değerleri Nasıl Ele Alırsınız

Varsayılan olarak XLOOKUP() eşleşme bulamadığında #N/A hatası verir. if_not_found bağımsız değişkeni, varsayılan hata yerine istediğimizi göstererek bunu düzeltebilir.  

Diyelim ki C9’a veri setimde olmayan bir Ürün ID’si yazıyorum; örneğin “P999”.

Özel bir mesaj döndürmek

Ben G2’ye şunu giriyorum:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

“P999” A sütununda olmadığından, XLOOKUP() hatayı tamamen atlar ve “Not Found” döndürür. Bu ifadeyi “Check Product ID” veya “No Match” gibi istediğiniz sözlerle değiştirebilirsiniz.

Varsayılan hata yerine özel bir mesaj gösterin. Görsel: Yazar.

Boş değer döndürmek

Bazen metinsiz boş bir hücre istersiniz. Bunun için araya hiçbir şey koymadan iki tırnak işareti kullanın; şöyle:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Bunu, formülü bir raporda kullanırken, gerçek değerlerin yanına düşecek “Not Found” gibi bir ifadenin dağınık görünmesini istemediğinizde kullanabilirsiniz.

Varsayılan hata yerine boş bir hücre gösterin. Görsel: Yazar.

Onu gerçek bir hata olarak bırakmak

if_not_found’ı tamamen atladığınızda XLOOKUP() #N/A’ya geri döner. Bu her zaman kötü değildir. Formülü kendi kullanımınız için oluşturuyorsanız ve bir yazım hatasını ya da eksik bir girişi anında yakalamak istiyorsanız, sorunu gizleyen sessiz bir boş hücre yerine #N/A görmeniz daha faydalıdır.

Not: if_not_found yalnızca eksik eşleşmeleri yakalar. Örneğin lookup_array ile return_array farklı boyutlardaysa XLOOKUP() yine #VALUE! hatası verir ve if_not_found bunu durdurmaz. Bu özel durumu aşağıdaki Yaygın Hatalar bölümünde ele alacağız.

Gelişmiş XLOOKUP() Özellikleri

Şimdi XLOOKUP()’ın gelişmiş özelliklerine bakalım.

Aşağıdan yukarı arama

search_mode’u -1 yapın; XLOOKUP() aralığınızı yukarıdan aşağı yerine aşağıdan yukarı tarar. Bunu, verilerinizde yinelenen değerler olduğunda ve ilkini değil en güncel girdiyi istediğinizde kullanın.

Diyelim ki veri setimde üç tane yinelenen Müşteri ID’si var. E2 hücresine, yukarıdan aşağı yaklaşımı kullanacak varsayılan formülü giriyorum: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Bu, tablonun başından itibaren hesaplanır ve ilk eşleşmeden sonucu gösterir. 

Ancak alttan aramak istersem şu formülü kullanırım: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

Bu formülde -1, Excel’e veri setinin altından aramaya başlamasını söyler. Ve bu, alttan ilk eşleşme değeri olan 340’ı döndürür.  

XLOOKUP() verileri aşağıdan yukarı tarar. Görsel: Yazar.

İkili arama modu

search_mode’u 2 yapın; XLOOKUP() satır satır kontrol etmek yerine ikili arama yapar. 

Nasıl çalıştığı şöyle: 

• Önce aralığınızın ortasındaki öğeye bakar. 

• Eşleşme değilse, değerin olamayacağı yarıyı eler ve kalan kısmın ortasına bakar. 

• Bunu, her seferinde veriyi ikiye bölerek eşleşmeyi bulana kadar sürdürür. 

• search_mode’u -2 yaparsanız aynı şekilde çalışır; sadece ilk yerine sondaki öğeden başlar.

Burada E2’ye şunu girin:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Bu formül bunun yerine 200 döndürür; tamamen farklı bir satır.

İkili arama modu yanlış sonuç veriyor. Görsel: Yazar.

Çünkü ikili arama yalnızca lookup_array’iniz sıralıysa çalışır. Müşteri ID sütunumuz sıralı olmadığından C100, C101, C100, C102, C100 şeklinde gidiyor; ikili arama yanlış yarıyı eliyor ve yanlış eşleşme veriyor. 

Şimdi veriler gerçekten sıralandığında ne olduğuna bakalım. 

Aynı günlüğü Müşteri ID’ye göre artan şekilde sıralayın (üç C100 başta olsun) ve aynı formülü tekrar çalıştırın. 

İkili arama modu sıralı verilerle çalışır. Görsel: Yazar.

Bu kez E2, normal bir aramanın da vereceği 120’yi döndürür. Fark budur. İkili arama yalnızca güvenle elenebilecek verileri atlar ve bu da ancak veriler baştan sıralıysa geçerlidir. 

İç içe XLOOKUP() formülleri

Bir aramanın sonucu sabit bir aralık yerine ikinci bir aramaya girdi olacaksa, bir XLOOKUP()’ı diğerinin içine yerleştirin.

Diyelim ki bir fiyat bulmak istiyorsunuz; ancak çektiğiniz sütun, örneğin bir müşterinin fiyatlandırma kademesine bağlı olan ayrı bir aramaya göre belirleniyor.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

Bu formülde içteki XLOOKUP() önce D9’a bağlı olarak hangi sütun aralığından çekileceğini bulur; ardından dıştaki XLOOKUP() bu sonucu kendi return_array’i olarak kullanır. 

İç içe XLOOKUP(). Görsel: Yazar.

XLOOKUP() vs. VLOOKUP()

XLOOKUP()’ın nasıl çalıştığını ayrıntılı olarak ele aldık. İşte VLOOKUP() ile karşılaştırması.

Hızlıca not etmeye değer iki nokta var: 

• VLOOKUP() varsayılan olarak yaklaşık eşleşme yapar. Dördüncü bağımsız değişkeni unutursanız hata göstermez; sessizce yanlış değeri döndürür.

• VLOOKUP() sayfasına bir sütun eklerseniz formüller sessizce yanlış değeri çekmeye başlar. XLOOKUP() sütun saymak yerine doğrudan aralıklara referans vererek bunu önler.

Bunların hiçbiri VLOOKUP()’ı geçersiz kılmaz. Hâlâ basit, statik sayfalar için gayet iyi çalışır. Ancak bundan sonra oluşturulan her şey için, XLOOKUP() daha güvenli varsayılandır.

XLOOKUP() vs. INDEX() ve MATCH()

XLOOKUP() yokken, INDEX() ve MATCH() birlikte VLOOKUP()’ın sınırlarını aşmanın standart yoluydu. İki işlev birden kullanır. 

• MATCH() aranan değerin konumunu bulur. 

• INDEX() sonra bu konumu kullanarak sonucu işaret ettiğiniz yerden çeker: sol ya da sağ önemli değildir.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Bunu, bu makale boyunca kullandığımız XLOOKUP() sürümüyle karşılaştırın:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() yalnızca Microsoft 365 ve Excel 2021 ve sonrasında çalışır; bunu daha önce belirtmiştik. Çalışma kitabınızın Excel 2019 veya 2016’da açılması gerekiyorsa ya da daha eski sürümleri kullanan kişilerle paylaşıyorsanız, INDEX() ve MATCH() her yerde çalışır.

Daha küçük bir durum da vardır. 

INDEX() ve MATCH() “konumu bulma”yı “değeri döndürme”den ayırır; bu, bazı karmaşık iç içe formüllerde yardımcı olabilir. Bu nadiren karşınıza çıkar; çoğu okurun buna ihtiyacı olmayacaktır.

Bu iki durum dışında, yeni bir çalışma kitabı için XLOOKUP() daha iyi bir seçimdir. 

Yaygın XLOOKUP() Hataları ve Nasıl Düzeltilir

Karşılaşma olasılığınız en yüksek konular ve her birini nasıl düzelteceğinize dair hızlı bir başvuru.

#N/A hataları

Bu, XLOOKUP()’ın eşleşme bulamadığında verdiği varsayılan yanıttır. Ben buna daha önce rastladım; lookup_value ve lookup_array hizalı değildi ve XLOOKUP()’ın A sütununda eşleştireceği bir şey yoktu.

Bunu düzeltmek için: lookup_value değerinizin gerçekten lookup_array içinde, aynı şekilde yazılmış ve biçimlendirilmiş olduğundan emin olun. Eksik eşleşmeler beklenen ve normal ise, daha önce ele aldığımız gibi if_not_found bağımsız değişkenini kullanarak #N/A yerine “Not Found” ya da boş hücre gibi daha faydalı bir şeyle değiştirebilirsiniz. 

Yanlış arama aralığı

Bu, lookup_array ile return_array doğru hizalanmadığında olur: yanlış satırlar, yanlış sütunlar veya boyutları uymayan aralıklar. 

Kullandığınız Excel ya da Sheets sürümüne bağlı olarak bu, #VALUE! hatası olarak veya bazen gerçek sorunu açıklayan ayrı bir mesajla birlikte #N/A olarak görünebilir. #N/A sizi bunun eksik eşleşme olduğuna inandırmasın. 

Bunu düzeltmek için: lookup_array ile return_array’in aynı sayıda satırı veya sütunu kapsadığından emin olun. lookup_array A2:A5 ise, return_array da 2’den 5’e kadar olan satırları kapsamalıdır; 4’ten 5’e veya başka bir uyumsuz aralık değil.

Veri tipi uyuşmazlıkları

VLOOKUP() bir sayı beklerken metin aldığı için hata verebilir. XLOOKUP() da benzer bir sorunla karşılaşabilir. lookup_value bir sayıysa ancak lookup_array aynı değerleri metin olarak tutuyorsa (ya da tersi), ekranda aynı görünseler bile XLOOKUP() eşleşme bulamaz.

Bunu düzeltmek için: lookup_value ile lookup_array’inizin aynı veri tipini kullandığını kontrol edin. Metin olarak saklanan sayılar genellikle hücrede sağa yaslı yerine sola yaslı görünür. lookup_value’nızı VALUE() ile sarmalayarak metni sayıya dönüştürün veya tersine ihtiyaç varsa TEXT() kullanın; uyuşmazlığın yönüne bağlı olarak.

Spill (döküm) hataları

Bu, return_array birden fazla sütunu kapsadığında, ancak XLOOKUP()’ın ihtiyaç duyduğu hücreler boş olmadığında olur. Excel’in sonuçları dökebilmesi için formül hücrenizin yanında boş alan gerekir. Orada zaten bir şey varsa, tüm formül başarısız olur.

Bunu düzeltmek için: Sonuçların gideceği hücreleri temizleyin ve sonra formülünüzü girin. Bu hücrelerde bir şeye ihtiyacınız varsa, XLOOKUP() formülünüzü çevresinde boş alan olan başka bir yere taşıyın.

Son Düşünceler 

Artık XLOOKUP()’ı tekil değerler, birden çok sütun, eksik veriler ve hatta iki aralık arasında iç içe aramalar için nasıl kullanacağınızı biliyorsunuz. Oldukça geniş bir alanı kapsadık.

Bundan sonra, yeni bir çalışma kitabı için varsayılanınız XLOOKUP() olsun. VLOOKUP()’tan daha esnek, INDEX/MATCH()’ten daha okunabilir ve her formülü IFERROR() ile sarmanızı gerektirmez.

Daha derine inmek isterseniz, INDEX() ve MATCH()’i de ayrı ayrı öğrenmeye değer.