Excel'de XLOOKUP() İşlevini Birden Çok Ölçütle Kullanma

XLOOKUP(), Excel'deki arama işlevleri grubunun (LOOKUP(), VLOOKUP(), HLOOKUP() ve XLOOKUP()) en yeni üyesi olup pek çok avantaj, gelişmiş işlevsellik ve daha fazla esneklik sunar.

Bu eğitimde önce XLOOKUP() Excel işlevinin ne işe yaradığını ve eski arama işlevlerine göre nasıl daha iyi olduğunu tartışacağız; ardından temel sözdizimine göz atacak ve sonra da esas konuya — XLOOKUP() işlevinin birden çok ölçütle kullanımına — geçeceğiz.

Excel'in temellerini öğrenmeniz gerekiyorsa, başlangıç seviyesine uygun Introduction to Excel kursu doğru başlangıç noktasıdır.

Excel'de Neden XLOOKUP() Kullanılır

XLOOKUP() işlevi bir veri aralığını veya dizisini tarar ve ilk eşleşmeye karşılık gelen öğeyi döndürür. Eşleşme bulunamazsa, belirli bir eşleşme türü sağlanmışsa XLOOKUP() yaklaşık bir eşleşme döndürebilir. Pek çok açıdan XLOOKUP(), Excel'deki seleflerini (VLOOKUP(), HLOOKUP() ve LOOKUP()) geride bırakır.

Özellikle şu imkânları sağlar:

• veriyi hem yatay hem dikey ve herhangi bir yönde arama
• birden çok arama ölçütü
• varsayılanı tam eşleşme olmak üzere yaklaşık eşleşme
• kısmi eşleşme
• birden çok sütun ve satır döndürme
• eşleşme bulunamadığında özelleştirilmiş bir metin döndürme.

Ayrıca XLOOKUP(), özellikle büyük veri kümelerinde arama yaptığımızda önemli olan, Excel'deki eski arama işlevlerinden daha hızlı çalışır.

XLOOKUP() Tek Koşulla Nasıl Kullanılır

XLOOKUP() işlevinin temel sözdizimine kısaca bakalım:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

burada gerekli parametreler şunlardır:

• lookup_value—aranacak değer

• lookup_array—aranacak dizi veya aralık

• return_array—döndürülecek dizi veya aralık

ve isteğe bağlı parametreler şunlardır:

• [if_not_found]—eşleşme bulunamadığında döndürülecek metin

• [match_mode]—eşleşme türü (tam veya yaklaşık) ve eşleşme bulunamadığında ne döndürüleceği.

• [search_mode]—kullanılacak arama modu (doğrudan veya ters arama, artan ya da azalan sıralı arama dizisinde ikili arama).

Bu eğitimde ileride isteğe bağlı parametrelerin kullanımına örnekler göreceğiz.

Excel'de diğer yararlı işlev ve formülleri öğrenmek veya tazelemek için Herkesin Bilmesi Gereken 15 Temel Excel Formülü yazısını ve Excel Formülleri El Kılavuzunu inceleyebilirsiniz.

XLOOKUP() Birden Çok Ölçütle Nasıl Kullanılır

Temel sözdiziminden gördüğümüz gibi, XLOOKUP() Excel işlevi varsayılan olarak belirli bir değişkenin tek bir arama değeriyle çalışacak şekilde tasarlanmıştır.

Ancak bu işlevi aynı anda birden çok değişkende kullanacak şekilde uyarlayabilir, her değişkende farklı bir arama değeri arayabiliriz. Başka bir deyişle, XLOOKUP() ile birden çok ölçüt uygulayarak arama yapabiliriz. Bunu yapmak için iki temel yaklaşım vardır: birleştirme (concatenation) ve boolean ifadeleri; ikisini de birazdan ele alacağız.

Teknik ayrıntılara dalmadan önce üzerinde çalışacağımız Excel tablosuna bakalım. Bu tablo 10 kediye ait ad, renk ve yaş bilgilerinden oluşuyor:

Deneylerimizde, XLOOKUP() ile bir kedinin üç özelliğinden birini, diğer ikisini kullanarak bulacağız.

Tablomuzun çok basit olması ve her durumda istediğimizi herhangi bir işlev olmadan da açıkça bulabilmemiz sorun değil. Burada, XLOOKUP()'u birden çok ölçütle kullanmanın temel ilkelerini anlamamız gerekiyor. Bu ilkeleri bilirsek, iki ölçütten fazlası olduğunda veya belirli gereksinimleri karşılamamız gerektiğinde olduğu gibi, gerçek hayattaki daha karmaşık senaryolara kolayca uyarlayabiliriz.

Birleştirme kullanarak birden çok ölçütle XLOOKUP()

Birleştirme yaklaşımı oldukça basittir: ilgili arama değerlerini ve dizilerini kendi aralarında birleştirmemiz gerekir. Nasıl çalıştığına bakalım.

Diyelim ki beyaz ve gri renkli, Nala adlı bir kedinin yaşını bulmak istiyoruz. Kolaylık olması için, bu iki ölçütü tablomuzun bulunduğu sayfaya ekledik:

Bu durumda XLOOKUP() formülü şu şekilde olacaktır:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)

Bu, ilk beyaz ve gri Nala'nın (benim kedim 😺) yaşı olan 2,5'i döndürecektir. Unutmayın, XLOOKUP() ilk eşleşmeye karşılık gelen öğeyi döndürür.

Yukarıdaki formülün ilk iki bileşeninin —ve arasındaki & işaretiyle olanların— neyi temsil ettiğini anlamak için her birini ayrı bir Excel hücresinde çalıştırabiliriz:

=B2:B11&C2:C11

Sonuç NalaWhite ve Grey olur.

=B2:B11&C2:C11

Sonuç şöyledir:

Evet, bu kadar basit: Az önce ilgili arama değerlerini ve arama dizilerini kendi aralarında, daha önce tartıştığımız temel XLOOKUP() sözdizimini izleyerek birleştirdik.

Boolean ifadeleri kullanarak birden çok ölçütle XLOOKUP()

Şimdi aynı görevi — beyaz ve gri renkli, Nala adlı bir kedinin yaşını bulmayı — ikinci yaklaşımla, yani boolean ifadeleriyle gerçekleştirelim. Bu durumda XLOOKUP() formülü şöyle olacaktır:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Beklendiği gibi bu da 2,5 döndürecektir; bu, ilk beyaz ve gri Nala'nın yaşıdır.

Yukarıdaki formülün bileşenlerini inceleyelim. Burada 1, TRUE anlamına gelir; yani formülün ikinci bileşeniyle temsil edilen arama dizisinde — (B2:B11=G2)*(C2:C11=G3) — TRUE değerini arıyoruz.

Bu bileşen ise iki çarpandan oluşur ve her biri belirli bir koşulu kontrol eder: ilki adın aradığımız ada karşılık gelip gelmediğini, ikincisi rengin aradığımız renge karşılık gelip gelmediğini kontrol eder. Her bir çarpanı ayrı bir Excel hücresinde çalıştıralım:

=B2:B11=G2

Sonuç şöyledir:

=C2:C11=G3

Sonuç şöyledir:

Şimdi formülün tüm ikinci bileşenini çalıştıralım:

=(B2:B11=G2)*(C2:C11=G3)

Sonuç şöyledir:

Yukarıdaki sonuçta iki adet 1 görsek de (iki TRUE değeri anlamına gelir), XLOOKUP() işlevi ilk eşleşmeye karşılık gelen öğeyi döndürür.

XLOOKUP() işlevini birden çok ölçütle çalıştırırken boolean ifadeleri kullanma yaklaşımı, birleştirme yaklaşımına kıyasla çok daha karmaşık ve sezgisel olmayan bir görünüm sergiler. İki ölçütten fazla kullansaydık bu daha da zorlaşırdı. O hâlde neden her zaman birleştirme kullanmıyoruz?

Boolean ifade yaklaşımı, XLOOKUP()'u birden çok ölçütle kullanırken çok daha fazla esneklik sağlar. Birleştirme yaklaşımına göre avantajlarına bakalım.

FALSE Değerini Kontrol Etme

Yukarıdaki formülde, 1 değerini vererek boolean ifadenin TRUE olup olmadığını kontrol ediyorduk:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Bazı durumlarda boolean ifadenin aslında FALSE olup olmadığını kontrol etmemiz gerekebilir. Örneğin, tablomuzdaki beyaz ve gri olmayan ilk Nala'nın yaşını bulmak isteyelim. Formülümüzü şu şekilde değiştirmeliyiz:

=XLOOKUP(0, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Sonuç 1 olacaktır; bu da beyaz ve gri olmayan ilk Nala'nın yaşıdır (daha doğrusu, üç renkli bir Nala'dır ama beyaz ve gri değildir).

Mantıksal Operatörleri Kullanma

Boolean ifadelerle, yalnızca eşitliği kontrol etmekle sınırlı değiliz. Diyelim ki 2 yıldan küçük Nala adlı bir kedinin rengini bulmak istiyoruz:

Bu durumda XLOOKUP() formülü şöyle olacaktır:

=XLOOKUP(1, (B2:B11=G2)*(D2:D11<G3), C2:C11)

Sonuç Tricolor olacaktır.

En Az Bir Ölçütü Karşılama

Şimdiye kadar yaptığımız, sağlanan ölçütlerin tümüne uyumu kontrol etmekti. Başka senaryolarda, en az bir ölçütün karşılanması gerekebilir.

Nasıl çalıştığını görmek için başlangıçtaki görevimize — beyaz ve gri renkli, Nala adlı bir kedinin yaşını bulmaya — dönelim:

Bu kez ise Nala adıyla anılan ya da beyaz ve gri olan bir kedinin yaşını bulmak istiyoruz. Bu durumda XLOOKUP() formülü şöyle olacaktır:

=XLOOKUP(1, (B2:B11=G2)+(C2:C11=G3), D2:D11)

Bu, üç renkli bir Nala'nın yaşı olan 1 değerini döndürür.

Hatırlatma olarak, hem Nala adlı hem de beyaz ve gri bir kedinin yaşını aradığımızda formül şu şekildeydi:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

* işaretini + ile değiştirmek farkı yarattı.

Excel’in tüm potansiyelini keşfetmekle ilgileniyorsanız, kapsamlı, uygulama odaklı ve dengeli bir Excel Fundamentals beceri yolunu değerlendirin.

Birden çok ölçütle XLOOKUP() için isteğe bağlı parametreler

XLOOKUP()'un tek ölçütle normal kullanımında olduğu gibi, birden çok ölçütte çalıştırırken de bu işlevin ek seçeneklerini kullanabiliriz. İşte [if_not_found], [match_mode] ve [search_mode] isteğe bağlı parametreleri burada devreye girer.

Bu parametrelerin her birinin XLOOKUP() ve birden çok ölçütle kullanımına hızlıca örnekler bakalım. Basitlik adına, her örnekte birleştirme yaklaşımını uygulayacağız.

Eşleşme Bulunamadığında Belirtilen Metni Döndürme

Burada tablomuzda bulunmayan siyah bir Nala'nın yaşını arıyoruz:

Bu durumda XLOOKUP() formülüne [if_not_found] isteğe bağlı parametresini, eşleşme bulunamadığında döndürülecek metni atayarak ekledik:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, "No cat is found")

Nitekim işlev sağlanan metni döndürdü: No cat is found. Herhangi bir metin sağlamasaydık, işlev #N/A döndürürdü.

Yaklaşık Eşleşme Döndürme

Diyelim ki üç renkli bir Nala'nın yaşını arıyoruz, ancak renginin tabloda "Tricolor" mu yoksa "Tricolour" mu yazıldığından emin değiliz. Bu durumda yaklaşık, joker karakter tabanlı eşleşme kullanmamız ve XLOOKUP() işlevine 2'ye eşit [match_mode] argümanını vermemiz gerekir.

Yukarıdaki tabloda, 0 dahil herhangi bir sayıda karakteri temsil eden yıldız işaretini (*) kullandık. Diğer joker karakterleri Microsoft Office belgelerinde bulabilirsiniz.

XLOOKUP() formülü burada şöyle olacaktır:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , 2)

Ve tablomuzdaki ilk (ve tek) üç renkli Nala'nın yaşı olan 1'i döndürecektir.

Yukarıdaki formülde 2 argümanının, "Tricolo*r" renkli Nala'nın yaşını aramaktan ziyade yaklaşık eşleşme istediğimizi ifade ettiğine dikkat edin. Bizim örneğimizde yıldız işareti "Tricolor" kelimesini yakaladı; aynı işi "Tricolour" veya diyelim ki "Tricolooor" üzerinde de yapardı.

Ters Arama Gerçekleştirme

Başlangıçtaki aynı egzersizi yapalım — beyaz ve gri bir Nala'nın yaşını bulalım — ama bu kez aramaya sondaki öğeden başlayalım.

Bunu yapmak için XLOOKUP() işlevine aşağıdaki gibi -1 olarak ayarlanmış [search_mode] isteğe bağlı parametresini eklememiz gerekir:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , , -1)

İşlev, arama dizisinin sonundan ilk beyaz ve gri Nala'nın yaşı olan 7'yi döndürür.

XLOOKUP() ve INDEX() ile MATCH() Karşılaştırması

Excel'in eski sürümlerinde, XLOOKUP() işlevinin birden çok ölçütle kullanımına benzer bir işlevselliği çoğaltmak için INDEX() ve MATCH() işlevlerini birleştirmemiz gerekir. Her iki işlevin sözdizimine girmeden, eski yöntem olan INDEX() + MATCH() ile yeni XLOOKUP() işlevini kullanarak ilk beyaz ve gri Nala'nın yaşını nasıl bulabileceğimize bakalım (hücre başvuruları için önceki tabloya bakabiliriz):

=INDEX(D2:D11, MATCH(1, (G2=B2:B11)*(G3=C2:C11), 0))
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Argümanlar bir ölçüde benzer görünse de, XLOOKUP() işlevini eski yönteme tercih etmenin başlıca avantajlarını belirtelim:

• Bu işlem için tek bir işlev yeterlidir — işlevleri birleştirmeye gerek yoktur.

• Uygun olduğunda birleştirme yaklaşımını uygulama olanağı.

• İsteğe bağlı parametreleri kullanabilme olanağı (MATCH() işlevinde bunlar yoktur).