XLOOKUP ve VLOOKUP (DÜŞEYARA) Farkları: Örnekler ve Hangisini Kullanmalı

XLOOKUP ve VLOOKUP arasında seçim yaparken dosyayı açan herkesin Microsoft 365 için Excel'i, Excel 2021'i veya sonrasını ya da web için Excel'i kullanıyorsa XLOOKUP'ı tercih edin. XLOOKUP (Türkçe Excel'de ÇAPRAZARA), VLOOKUP'ın (Türkçe Excel'de DÜŞEYARA) yaptığı her şeyi daha az hatayla yapar: varsayılan olarak tam eşleşme kullanır, sola bakabilir ve bir sütun eklediğinizde bozulmaz. VLOOKUP'ı yalnızca çalışma kitabının Excel 2019'da veya daha eski bir sürümde çalışması gerektiğinde kullanın.

Bu uyumluluk noktası asıl çekincedir. XLOOKUP, Excel 2016 ve 2019'da yoktur; bu yüzden XLOOKUP formülleriyle dolu bir dosya, hâlâ bu sürümleri kullananlarda #NAME? hataları gösterir.

Formüller, Excel'in İngilizce sürümündeki biçimiyle gösterilmiştir. Türkçe Excel'de işlev adları yerelleştirilmiştir (VLOOKUP → DÜŞEYARA, XLOOKUP → ÇAPRAZARA, HLOOKUP → YATAYARA, INDEX → İNDİS, MATCH → KAÇINCI, TRIM → KIRP) ve bağımsız değişkenler virgül yerine noktalı virgülle (;) ayrılır. Örnek tablodaki veriler, görsellerle aynı olsun diye İngilizce bırakılmıştır.

Söz dizimi yan yana

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Büyük fark, Excel'e cevabın nerede olduğunu nasıl söylediğinizdir. VLOOKUP, bir tablonun tamamını ve o tablonun sol kenarından sayılan bir sütun numarasını alır. XLOOKUP ise iki ayrı aralık alır: aranacak sütun ve değerin döndürüleceği sütun.

Aynı arama iki şekilde yazılmış

İşte A1:D6'daki küçük bir ürün listesi ve G2'ye yazılmış bir ürün kimliği:

P-104'ün fiyatını bulmak için:

=VLOOKUP(G2, A2:D6, 4, FALSE)
=XLOOKUP(G2, A2:A6, D2:D6)

İkisi de $59.00 döndürür. VLOOKUP, A2:D6'nın ilk sütununu arar ve 4. sütunu döndürür; FALSE tam eşleşmeyi zorlar. XLOOKUP, A2:A6'yı arar ve D2:D6'daki eşleşen hücreyi döndürür; tam eşleşme varsayılandır.

Formülü bir sütun boyunca aşağı kopyalayacaksanız tabloyu mutlak başvurularla kilitleyin, örneğin =XLOOKUP(G2, $A$2:$A$6, $D$2:$D$6). İmleç bir başvurunun içindeyken $ işaretlerini eklemek için Windows'ta F4, Mac'te Cmd+T tuşlarına basın.

VLOOKUP'ın iki klasik tuzağı

1. FALSE (veya 0) yazmayı unutmak

VLOOKUP'ın son bağımsız değişkeni isteğe bağlıdır ve onu atladığınızda Excel yaklaşık eşleşme kullanır. Yaklaşık eşleşmede VLOOKUP, ilk sütunun sıralı olduğunu varsayar ve arattığınız değere eşit veya ondan küçük en yakın değeri, hiçbir uyarı vermeden döndürür.

Yukarıdaki tabloda =VLOOKUP("P-110", A2:D6, 4) formülünü deneyin. P-110 yoktur, ancak #N/A yerine $32.75 alırsınız; bu, P-105'in fiyatıdır, çünkü "P-105" en yakın küçük değerdir. Sıralanmamış verilerde sonuçlar daha da rastgele olabilir.

Tam eşleşmeli bir VLOOKUP'ı her zaman FALSE veya 0 ile bitirin. XLOOKUP, siz aksini istemedikçe tam eşleşme kullandığı için bu sorunu yaşatmaz.

2. Koda gömülü sütun numarası

VLOOKUP formülündeki 4 yalnızca bir sayıdır. Biri Product ile Category arasına bir Supplier sütunu eklerse tablo A:E olur, Price E sütununa kayar ve formül hâlâ 4. sütunu, yani artık Category'yi döndürür. $59.00 yerine "Video" görürsünüz.

XLOOKUP fiyat sütununun kendisini (D2:D6) gösterir; bu yüzden Excel onu otomatik olarak E2:E6'ya günceller ve cevap doğru kalır.

XLOOKUP'ın yapıp VLOOKUP'ın yapamadıkları

Sola bakma

VLOOKUP yalnızca arama sütununun sağındaki sütunları döndürebilir. XLOOKUP yönü önemsemez. G2'deki bir ürün adının ürün kimliğini bulmak için:

=XLOOKUP(G2, B2:B6, A2:A6)

G2'de "Desk Lamp" varken bu, A sütunu B sütununun solunda olsa bile P-105 döndürür.

Hiçbir şey bulunamadığında anlaşılır bir ileti gösterme

=XLOOKUP(G2, A2:A6, D2:D6, "Not found")

VLOOKUP'taki karşılığı bir sarmalayıcı gerektirir: =IFNA(VLOOKUP(G2, A2:D6, 4, FALSE), "Not found").

Tek formülde birden fazla sütun döndürme

=XLOOKUP(G2, A2:A6, B2:D6)

P-104 için bu, Webcam HD, Video ve $59.00 değerlerini yan yana üç hücreye taşırır. O hücreleri boş bırakın, yoksa #SPILL! hatası alırsınız.

İlk eşleşme yerine son eşleşmeyi bulma

Aynı müşterinin birçok kez göründüğü bir kayıtta, alttan yukarı aramak ve en son girdiyi almak için search_mode değerini -1 yapın:

=XLOOKUP(G2, A2:A500, C2:C500, "Not found", 0, -1)

Sıralamadan yaklaşık eşleşme

Kademeli fiyatlar veya komisyon oranları için VLOOKUP'ın yaklaşık eşleşmesi yalnızca ilk sütun artan sırada sıralıysa çalışır. XLOOKUP'ın match_mode -1 (tam eşleşme veya bir sonraki küçük öğe) ve 1 (tam eşleşme veya bir sonraki büyük öğe) değerleri sıralı veri gerektirmez. En düşük sipariş tutarları J2:J5'te ve indirimler K2:K5'teyken:

=VLOOKUP(B2, J2:K5, 2, TRUE)
=XLOOKUP(B2, J2:J5, K2:K5, , -1)

Joker karakterler

İkisi de * ve ? karakterlerini destekler, ancak XLOOKUP bunları joker karakter olarak ele alması için match_mode 2 ister:

=VLOOKUP("Desk*", B2:D6, 3, FALSE)
=XLOOKUP("Desk*", B2:B6, D2:D6, , 2)

İkisi de Desk Lamp için $32.75 döndürür.

INDEX/MATCH: klasik alternatif

XLOOKUP'tan önce VLOOKUP'ın sınırlarının standart çözümü INDEX ile MATCH birleşimiydi. Sola bakar, eklenen sütunlara dayanır ve Excel'in her sürümünde çalışır; bu da onu Excel 2019 veya daha eski sürümlerde açılması gereken dosyalar için en iyi seçim yapar:

=INDEX(D2:D6, MATCH(G2, A2:A6, 0))
=INDEX(A2:A6, MATCH(G2, B2:B6, 0))

Birincisi G2'deki kimliğin fiyatını döndürür; ikincisi sola bakma örneğidir. MATCH'taki 0 tam eşleşme demektir ve VLOOKUP'taki FALSE gibi unutulması kolaydır. XLOOKUP, aynı fikrin yalnızca daha kısa ve daha güvenli sürümüdür.

VLOOKUP formülleri XLOOKUP'a nasıl dönüştürülür?

Yerleşik bir dönüştürücü yok, ancak çeviri mekaniktir:

1. VLOOKUP'larınızı bulun: Ctrl+F (Mac'te Cmd+F) tuşlarına basın, VLOOKUP (Türkçe Excel'de DÜŞEYARA) yazın, ek ayarlar gizliyse Seçenekler'e (Options) tıklayın, Bakılacak yer (Look in) ayarını Formüller (Formulas) yapın ve Tümünü Bul'a (Find All) tıklayın.
2. İlk bağımsız değişkeni (arama değeri) olduğu gibi bırakın.
3. Tabloyu, lookup_array olarak ilk sütunuyla değiştirin.
4. Sütun numarasını, return_array olarak karşılık gelen sütunla değiştirin. A sütunundan başlayan bir tablonun 5. sütunu E sütunudur.
5. FALSE'u kaldırın. Özgün formül TRUE ile bitiyorsa ya da son bağımsız değişkeni yoksa "bir sonraki küçük" davranışını korumak için match_mode -1 ekleyin.

Örneğin:

=VLOOKUP(A2, Products!$A$2:$F$500, 5, FALSE)
=XLOOKUP(A2, Products!$A$2:$A$500, Products!$E$2:$E$500)

Bütün bir sayfayı dönüştürmeden önce birkaç sonucu eski formülle karşılaştırarak test edin. Sıralanmamış veride yaklaşık eşleşmeli VLOOKUP'lar baştan beri yanlış değerler döndürüyor olabilir; bu yüzden XLOOKUP'ın cevapları haklı olarak farklı çıkabilir.

Bir XLOOKUP dosyası Excel 2016 veya 2019'da açıldığında ne olur?

Çalışma kitabı açılır, ancak bu sürümler işlevi tanımaz. Formül çubuğunda =_xlfn.XLOOKUP(...) olarak görünür. Hücreler başlangıçta son kaydedilen sonuçları gösterebilir, ancak formül yeniden hesaplanır hesaplanmaz #NAME? döndürür.

Dosyaları eski sürümleri kullanan kişilerle paylaşıyorsanız:

• Bunun yerine INDEX/MATCH veya VLOOKUP kullanın ya da
• Sonuçların değer olarak yapıştırıldığı bir kopya gönderin (Kopyala → Özel Yapıştır → Değerler, Copy → Paste Special → Values).

Aynısı FILTER, UNIQUE ve XMATCH gibi diğer yeni işlevler için de geçerlidir.

Google Sheets'te XLOOKUP

Google Sheets'te de aynı bağımsız değişken sırasıyla XLOOKUP vardır: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). Bu yazıdaki formüller orada olduğu gibi çalışır; bu nedenle XLOOKUP, Excel ile Sheets arasında dolaşan dosyalar için güvenli bir seçimdir.

Aramanız neden #N/A veya yanlış değer döndürüyor?

• Fazladan boşluklar. Sonunda boşluk olan "P-104 ", "P-104" ile eşleşmez. Veriyi TRIM ile temizleyin.
• Metin olarak saklanan sayılar. Metin olarak yazılmış bir kimlik, sayı olarak saklanan aynı kimlikle eşleşmez. Hücrenin köşesindeki yeşil üçgene bakın ve sütunu tek bir türe dönüştürün.
• VLOOKUP'ta eksik FALSE. #N/A yerine makul ama yanlış bir cevap alırsınız.
• Formülü kopyalarken kayan aralıklar. Arama tablosu için $ mutlak başvurularını kullanın.
• VLOOKUP'tan #REF!. Sütun numarası, tablodaki sütun sayısından büyüktür.
• XLOOKUP'tan #VALUE!. Arama ve döndürme aralıkları farklı boyuttadır; örneğin A2:A6 ve D2:D60.
• #NAME? İşlev adı yanlış yazılmıştır ya da dosya XLOOKUP'ı olmayan bir sürümde açıktır.

Sıkça Sorulan Sorular

XLOOKUP, VLOOKUP'tan daha mı iyi? Evet, hemen her kullanım için. Varsayılan olarak tam eşleşmedir, her yöne bakar, sütunlar kaydığında bozulmaz ve eksik değerleri sarmalayıcı olmadan ele alır. VLOOKUP'ın tek gerçek avantajı Excel'in her sürümünde çalışmasıdır.

XLOOKUP, VLOOKUP'tan daha hızlı mı? Günlük çalışma kitaplarında fark etmezsiniz. Çok büyük sıralı listelerde XLOOKUP'ın ikili arama modları (search_mode 2 veya -2) işleri hızlandırabilir, ancak veri sıralı değilse yanlış sonuç döndürürler.

XLOOKUP neden bilgisayarımda #NAME? gösteriyor? Excel sürümünüzde XLOOKUP yoktur. Microsoft 365'te, Excel 2021 ve sonrasında ve web için Excel'de bulunur; Excel 2019 veya 2016'da bulunmaz. Bunun yerine INDEX/MATCH kullanın.

XLOOKUP, HLOOKUP'ın da yerini alır mı? Evet. Arama ve döndürme aralıkları sütun yerine satırsa XLOOKUP aynı söz dizimiyle yatay arar.

XLOOKUP yalnızca ilkini değil, tüm eşleşmeleri döndürebilir mi? Hayır, bir eşleşme (veya sütunlardan oluşan tek bir satır) döndürür. Eşleşen her satırı almak için FILTER kullanın; örneğin =FILTER(A2:D6, C2:C6="Office").

VLOOKUP'ı hâlâ öğrenmeli miyim? Tanımakta fayda var, çünkü eski çalışma kitaplarında ve şablonlarda karşınıza çıkar. Yeni formüller için XLOOKUP'ı öğrenin ve eski Excel'de açılması gereken dosyalar için INDEX/MATCH'i aklınızda tutun.

Arama formülünü yapay zekâ yazsın

Arama formülleri, eksik bir FALSE ya da kaymış bir aralık gibi küçük ayrıntıların en sessiz hataları yarattığı yerdir. Excel için CoreGPT, "G2'deki ürün kimliğinin fiyatını bul" ya da "her satırı hedefin üstünde veya altında olarak işaretle" gibi ihtiyacınızı tarif etmenizi sağlar ve formülü kısa bir açıklamayla birlikte sayfanıza yazar. Başlamak ücretsizdir ve Windows, Mac ve web için Excel'de çalışır.

Deneyin: Excel için CoreGPT

İlgili yazılar:

• Excel'de Yinelenenleri Kaldırma: 5 Yöntem (Veri Silerek ve Silmeden)
• Microsoft 365 Excel İpuçları: Her Hafta Zaman Kazandıran Yöntemler
• ChatGPT Excel'de Nasıl Kullanılır: Eksiksiz Rehber