DÜŞEYARA

DÜŞEYARA (VLOOKUP), bir tablonun ilk sütununda aranan değeri bulur ve aynı satırda belirttiğiniz sütundaki değeri verir. Kod, ürün adı ya da şehir gibi bir anahtara bağlı fiyat, oran veya değer getirmek için kullanılır. Arama tam eşleşmeyle ya da sıralı listelerde yaklaşık eşleşmeyle yapılabilir.

Söz dizimi ve parametre​

İşlev her zaman tablo_dizisi aralığının ilk sütununda arar ve sonucu aynı satırdaki başka bir sütundan getirir. Türkçe Excel'de bağımsız değişkenler çoğunlukla ; ile, İngilizce Excel'de çoğunlukla , ile ayrılır; gerçek ayırıcı bölge ayarınıza göre değişebilir.

1. DÜŞEYARA nasıl çalışır​

DÜŞEYARA dört bilgiyle çalışır: ne aranacağı, nerede aranacağı, sonucun kaçıncı sütundan alınacağı ve eşleşmenin türü. Aşağıdaki tabloda D sütunundaki ürün kodu, A2:C5 aralığının ilk sütununda aranır ve bulunduğu satırın 3. sütunundaki fiyat getirilir. E2 hücresine aşağıdaki formülü yazıp aşağı kopyalayın:
Sonuçlar sırasıyla 180, 650 ve 450 olur. İşlev her satırda D sütunundaki kodu tablonun ilk sütunu olan A sütununda arar, kodun bulunduğu satıra iner ve o satırın 3. sütununu, yani Fiyat sütununu okur. Sütun numarası tablonun başından sayılır; çalışma sayfasının A sütunundan değil, tablo_dizisi aralığının ilk sütunundan.

2. DÜŞEYARA yalnızca sağa bakar​

Aranan değer her zaman tablonun ilk sütununda aranır ve sonuç bu sütunun sağındaki bir sütundan alınır. Bu tabloda kod B sütunundadır, ürün adı ise kodun solundadır. Tabloyu A sütunundan başlatırsanız kod hiç bulunamaz:
Tabloyu kodun bulunduğu B sütunundan başlatırsanız sağdaki fiyatı alabilirsiniz, ama soldaki ürün adına artık ulaşamazsınız:
Soldaki değeri almak için İNDİS ve KAÇINCI birlikte kullanılır:
Sonuçlar sırasıyla #YOK, 180 ve Fare olur. İlk formülde tablonun ilk sütunu ürün adlarıdır; B-200 kodu bu sütunda olmadığı için eşleşme bulunamaz. İkinci formül kodu doğru sütunda arar ve sağındaki fiyatı getirir. Üçüncü formülde KAÇINCI kodun satırını bulur, İNDİS de aynı satırdaki ürün adını soldaki sütundan alır.

3. DÜŞEYARA ilk eşleşmeyi bulur​

Aranan değer tabloda birden fazla kez geçiyorsa DÜŞEYARA yukarıdan aşağıya ilerler ve ilk bulduğu satırla yetinir. Ayşe ve Mehmet tabloda ikişer kez yer alıyor. E2 hücresine aşağıdaki formülü yazıp aşağı kopyalayın:
Sonuçlar sırasıyla 70 ve 85 olur. Her iki öğrencinin de final puanı tabloda bulunduğu halde işlev vize satırlarını döndürür, çünkü onlar daha yukarıdadır. Son eşleşmeyi almak gerekiyorsa ÇAPRAZARA işlevinin arama_modu değerini -1 yaparak aramayı sondan başlatabilirsiniz.

4. Büyük küçük harf ayrımı yapmaz​

DÜŞEYARA metinleri karşılaştırırken büyük ve küçük harfi aynı sayar. abc, Abc ve ABC kodları onun için aynı koddur ve her aramada ilk satır döner. D2 hücresindeki formül:
Harfe duyarlı arama için ÖZDEŞ işlevi her kodu aranan metinle harf harf karşılaştırır, KAÇINCI da DOĞRU sonucun konumunu bulur. E2 hücresindeki formül:
Sonuçlar D sütununda sırasıyla 10, 10 ve 10, E sütununda ise 30, 20 ve 10 olur. DÜŞEYARA üç aramada da ilk satırdaki abc kodunu bulur. ÖZDEŞ ile kurulan formül ise yalnızca harfleri birebir aynı olan kodu kabul eder ve her satırda doğru değeri getirir.

5. İki eşleşme modu vardır​

Son parametre aralık_bak eşleşme türünü seçer. YANLIŞ yalnızca birebir aynı değeri kabul eder. DOĞRU ise aranan değer yoksa ondan küçük olan en yakın değere razı olur; satış tutarına göre komisyon oranı bulmak gibi aralık aramalarında kullanılır. D2 ve E2 hücrelerindeki formüller:
Sonuçlar D sütununda sırasıyla 3%, 5%, 5% ve 8%, E sütununda #YOK, #YOK, 5% ve #YOK olur. Yaklaşık eşleşme 15.000 için 10.000 sınırını, 25.000 için 20.000 sınırını, 75.000 için 50.000 sınırını seçer. Tam eşleşme yalnızca tabloda birebir bulunan 20.000 tutarında sonuç verir; diğer tutarlar sınır listesinde olmadığı için hata döner.

6. Dikkat: varsayılan eşleşme yaklaşıktır​

aralık_bak yazılmazsa DÜŞEYARA bunu DOĞRU sayar ve yaklaşık eşleşme yapar. Liste sıralı değilse bu, hata vermeden yanlış sonuç üretebilir. D2 hücresinde dördüncü parametre yoktur, E2 hücresinde YANLIŞ yazılmıştır:
Sonuçlar D sütununda sırasıyla Muhasebe, İnsan Kaynakları, İnsan Kaynakları ve İnsan Kaynakları, E sütununda Muhasebe, İnsan Kaynakları, #YOK ve Satış olur. Parametresiz formül Ali ve Ece için doğru bölümü bulur, ama bu yalnızca bir rastlantıdır. Listede hiç olmayan Kaan için hata vermez, İnsan Kaynakları yazar; Zeynep için de Satış yerine İnsan Kaynakları getirir. YANLIŞ yazılan sütun ise dört satırın hepsinde doğru sonucu verir ve Kaan için eşleşme olmadığını açıkça bildirir. Bu yüzden kod, ad, numara gibi aramalarda dördüncü parametreyi asla boş bırakmayın.

7. Tam eşleşmeyi zorlamak​

Tam eşleşme için son parametreye YANLIŞ ya da 0 yazılır; ikisi aynı anlama gelir. Kod, numara, ad gibi kimlik değerlerinde her zaman tam eşleşme kullanın. D2 ve E2 hücrelerindeki formüller:
Sonuçlar iki sütunda da aynıdır: İstanbul, İzmir ve #YOK. 7 plaka kodu tabloda olmadığı için tam eşleşme yakın bir değere razı olmaz, #YOK hatası döndürür. Bu davranış istenen şeydir: yanlış bir ili sessizce göstermek yerine eşleşme olmadığını açıkça bildirir.

8. Yaklaşık eşleşme istemek​

Aralık aramalarında son parametreye DOĞRU ya da 1 yazılır. Bu tabloda kargo ücreti paketin ağırlığına göre kademelidir; A sütunu her kademenin alt sınırını gösterir ve küçükten büyüğe sıralıdır. D2 ve E2 hücrelerindeki formüller:
Sonuçlar iki sütunda da aynıdır: 40, 65, 120 ve 200. 0,5 kg ilk kademeye (0–1 kg), 3 kg ikinci kademeye (1–5 kg), 7,5 kg üçüncü kademeye (5–10 kg), 12 kg ise son kademeye düşer. İşlev her pakette ağırlığı aşmayan en büyük alt sınırı seçer.

9. Yaklaşık eşleşmede veriler sıralı olmalı​

Yaklaşık eşleşme ilk sütunun küçükten büyüğe sıralı olduğunu varsayar ve listeyi baştan sona taramak yerine ikiye bölerek arar. Bu tabloda komisyon sınırları karışık sırayla girilmiştir. D2 hücresine aşağıdaki formülü yazıp aşağı kopyalayın:
Sonuçlar sırasıyla 1%, 3%, 8% ve 1% olur; E sütunundaki doğru oranlarla karşılaştırın. 15.000 için %3 yerine %1, 25.000 için %5 yerine %3 bulunur. 60.000 ve 5.000 satırları doğru görünse de bu da rastlantıdır; sıra bozukken hangi sonucun doğru çıkacağı öngörülemez ve Excel hiçbir uyarı vermez. A sütununu küçükten büyüğe sıraladığınızda dört satırın tamamı E sütunuyla aynı olur.

10. Farklı tabloları birleştirebilir​

Sipariş listesinde yalnızca müşteri numarası var; müşteri adları ayrı bir tabloda (E:F). DÜŞEYARA bu iki tabloyu numara üzerinden birleştirir. C2 hücresine aşağıdaki formülü yazıp aşağı kopyalayın:
Sonuçlar sırasıyla Kaya A.Ş., Ece Ltd., Deniz Ticaret ve Kaya A.Ş. olur. Her sipariş satırı, müşteri numarasını ikinci tabloda bulur ve adını yanına yazar. Aynı müşteri birden fazla siparişte geçse de adı tek bir yerde tutulur; ad değişirse yalnızca müşteri tablosunu güncellemeniz yeterlidir.

11. Verileri sınıflandırabilir​

Yaklaşık eşleşme, bir değeri ait olduğu gruba yerleştirmek için idealdir. Müşterilerin yıllık alışveriş tutarına göre üyelik seviyesini bulalım; A sütunu her seviyenin başladığı tutardır. D2 hücresine aşağıdaki formülü yazıp aşağı kopyalayın:
Sonuçlar sırasıyla Bronz, Altın, Gümüş ve Platin olur. 3.200 hiçbir sınırı aşmadığı için ilk seviyede kalır; 12.750 ise 10.000 sınırını geçtiği halde 25.000'e ulaşmaz. Tam 5.000 olan tutar da sınırın kendisine eşit olduğu için o seviyeye dahil edilir. Yeni bir seviye eklemek için tabloya bir satır eklemeniz yeterlidir; formül değişmez.

12. Mutlak başvurular formülü taşınabilir yapar​

Formülü aşağı kopyaladığınızda göreli başvurular da kayar. D sütunundaki formülde tablo aralığı göreli yazılmıştır ve her satırda bir aşağı iner; E sütunundaki formülde ise dolar işaretleriyle sabitlenmiştir. D2 ve E2 hücrelerindeki formüller:
Sonuçlar D sütununda sırasıyla 10, #YOK, 30 ve #YOK, E sütununda 10, 10, 30 ve 20 olur. Göreli formül 3. satıra kopyalandığında aralığı A3:B5'e kayar ve en üstteki K1 kodunu artık göremez; 5. satırda aralık A5:B7 olur ve tablonun tamamen dışına çıkar. Mutlak başvurulu formül her satırda aynı tabloya bakar ve doğru sonucu verir.

13. Adlandırılmış aralıklar formülü okunur kılar​

Tablo aralığına bir ad verdiğinizde formülde hücre adresleri yerine bu ad görünür. Bu çalışma sayfasında A2:C4 aralığına Formüller > Ad Tanımla ile Fiyatlar adı verilmiştir. Ad tanımlı aralıklar zaten mutlaktır; kopyalarken dolar işaretine gerek kalmaz. E2 hücresindeki formül:
Sonuçlar sırasıyla 3200, 450 ve 180 olur. Formülü okuyan kişi, fiyat listesinde arama yapıldığını adresleri çözmeden anlar. Tabloya satır eklediğinizde Ad Yöneticisi'nden aralığı bir kez genişletmeniz bütün formülleri günceller.

14. Sütun eklemek formülü bozabilir​

Sütun numarası formüle sabit sayı olarak yazıldığında tabloya araya sütun eklemek sonucu değiştirir. Bu tabloda önceden 3. sütun Fiyat'tı; Ürün ile Fiyat arasına Kategori sütunu eklenince 3 numarası artık Kategori'yi gösterir. E sütunu sabit 3 kullanır, F sütunu sütun numarasını başlıktan KAÇINCI ile bulur:
Sonuçlar E sütununda sırasıyla Çevre birimi, Çevre birimi ve Ekran, F sütununda 450, 180 ve 3200 olur. Sabit 3 kullanan formül hata vermez ama artık fiyat yerine kategori döndürür; bu tür sessiz hatalar fark edilmesi en zor olanlardır. KAÇINCI Fiyat başlığının yerini her seferinde yeniden bulduğu için sütun eklense de doğru değeri getirir.

15. SÜTUN ile sütun numarası hesaplamak​

Aynı aramadan birden fazla sütun almak istediğinizde her formüle ayrı numara yazmak yerine SÜTUN işlevini kullanabilirsiniz. SÜTUN(B1) 2, SÜTUN(C1) 3 verir; formül sağa kopyalandıkça başvuru da kayar ve numara kendiliğinden artar. E2 hücresine aşağıdaki formülü yazıp sağa ve aşağı kopyalayın:
Sonuçlar E sütununda sırasıyla Fare, Monitör ve Klavye, F sütununda 180, 3200 ve 450 olur. Tek bir formül yazıp kopyalayarak iki sütunu birden doldurdunuz. Dolar işareti D sütununu ve 1. satırı sabitler; böylece aranan kod hep D sütunundan, sütun numarası hep başlık satırından okunur.

16. DÜŞEYARA ve KAÇINCI ile tam dinamik sütun​

Hangi sütunun getirileceğini bir hücreye yazılan başlık belirlesin. KAÇINCI başlığı tablonun başlık satırında arar ve konumunu sütun numarası olarak DÜŞEYARA'ya verir. G2 hücresine aşağıdaki formülü yazıp aşağı kopyalayın:
Sonuçlar sırasıyla 180, 8 ve Klavye olur. Her satır hem farklı bir ürünü hem farklı bir alanı soruyor ama formül aynı. Böyle bir yapıyla açılır listeden alan seçilen küçük bir sorgu ekranı kurabilirsiniz; tabloya sütun eklemek de formülü bozmaz.

17. Kısmi eşleşme için joker karakterler​

Tam eşleşme modunda joker karakterler kullanılabilir: * herhangi sayıda karakterin, ? tek bir karakterin yerini tutar. D2 hücresine aşağıdaki formülü yazıp aşağı kopyalayın:
Sonuçlar sırasıyla İzmir, Ankara, Bursa ve #YOK olur. Ay* Ay ile başlayan, *Lojistik Lojistik ile biten, C?ren* ise ikinci harfi herhangi bir karakter olan ve C ile başlayan ilk müşteriyi bulur. Son satırda joker yoktur; tam olarak Tekstil adında bir müşteri olmadığı için eşleşme bulunamaz. Joker karakterin kendisini aramak için önüne ~ koyun.

18. #YOK hatasını yakalayıp anlaşılır mesaj göstermek​

Aranan değer bulunamadığında çıkan #YOK hatası EĞERYOKSA ile yakalanır ve yerine bir metin gösterilir. EĞERHATA da aynı işi yapar ama yalnızca #YOK'u değil bütün hataları gizler; yanlış sütun numarası gibi gerçek hataları da örttüğü için aramada EĞERYOKSA daha güvenlidir. D2 ve E2 hücrelerindeki formüller:
Sonuçlar D sütununda sırasıyla 20, #YOK ve 30, E sütununda 20, Bulunamadı ve 30 olur. Bulunan kodlarda iki formül aynı sonucu verir; yalnızca K9 satırında hata yerine okunur bir mesaj görünür.

19. Metin olarak saklanan sayılar eşleşmeyi bozar​

Görünüşte aynı olan 102 sayısı ile metin olarak saklanmış "102" DÜŞEYARA için farklı değerlerdir. Bu tabloda kodlar metin olarak girilmiştir (sistemden aktarılan verilerde sık görülür), aranan değerler ise sayıdır. D sütunu doğrudan arar, E sütunu aranan sayıyı &"" ile metne çevirir:
Sonuçlar D sütununda sırasıyla #YOK, #YOK ve #YOK, E sütununda Fare, Monitör ve Klavye olur. Değerler ekranda aynı görünse de türleri farklı olduğu için doğrudan arama hiçbir satırda eşleşmez. Durum tersiyse, yani tablo sayı aranan değer metinse, aranan değeri SAYIYAÇEVİR ile sayıya dönüştürün. Hücrenin sol üst köşesindeki yeşil üçgen, sayının metin olarak saklandığının işaretidir.

20. İç içe EĞER yerine DÜŞEYARA​

Puanı harf notuna çeviren iç içe EĞER formülü her yeni kademede uzar ve okunması zorlaşır. Aynı işi kademeleri bir tabloya yazıp yaklaşık eşleşmeli DÜŞEYARA ile yapabilirsiniz. D2 ve E2 hücrelerindeki formüller:
Sonuçlar iki sütunda da aynıdır: Kaldı, İyi, Pekiyi ve Geçti. Fark bakımdadır: kademe sınırı değiştiğinde ya da yeni bir not eklendiğinde EĞER formülünü bütün hücrelerde yeniden yazmanız gerekir; DÜŞEYARA ile yalnızca A:B tablosunu düzeltirsiniz.

21. Tek bir ölçütle arar​

DÜŞEYARA yalnızca tek bir değeri arar. Personel ve ay gibi iki ölçüte göre arama yapmak için iki değeri birleştiren yardımcı bir anahtar sütunu tablonun başına eklenir. A2 hücresindeki anahtar formülü:
E2 hücresine aşağıdaki formülü yazıp aşağı kopyalayın:
Sonuçlar sırasıyla 140, 90 ve #YOK olur. Anahtar sütunu Ali|Ocak, Ali|Şubat gibi değerler üretir ve arama bu birleşik değer üzerinden yapılır. Ece|Mart tabloda olmadığı için son satırda eşleşme yoktur. Araya | gibi bir ayırıcı koymak, AliOcak ile Alio + cak gibi farklı ikililerin aynı anahtarı üretmesini önler. Yardımcı sütun eklemek istemiyorsanız ÇAPRAZARA çoklu ölçütü doğrudan destekler.

22. İki DÜŞEYARA tek DÜŞEYARA'dan hızlı olabilir​

Tam eşleşme listeyi baştan sona tek tek tarar; yüz binlerce satırda bu yavaşlar. Yaklaşık eşleşme sıralı listeyi ikiye bölerek çok daha az adımda arar, ama aranan değer yoksa yanlış satırı döndürür. İkisinin iyi yanlarını birleştirmek için önce yaklaşık eşleşmeyle bulunan kodun aranan koda gerçekten eşit olup olmadığı kontrol edilir; eşitse ikinci DÜŞEYARA tutarı getirir. Liste A sütununa göre sıralı olmalıdır. D2 hücresindeki formül:
Sonuçlar sırasıyla 380, Yok ve 95 olur. 1004 tabloda yoktur; yaklaşık eşleşme 1003'ü bulur, kontrol bunun aranan koda eşit olmadığını görür ve yanlış tutar yerine Yok yazar. Bu küçük tabloda hız farkı hissedilmez; büyük listelerde ise iki hızlı arama tek bir yavaş aramadan belirgin biçimde hızlıdır.

23. İNDİS ve KAÇINCI, DÜŞEYARA'nın yaptığı her şeyi ve fazlasını yapar​

KAÇINCI aranan değerin hangi satırda olduğunu bulur, İNDİS de istenen sütunun o satırındaki değeri verir. Arama sütunu ile sonuç sütunu ayrı ayrı seçildiği için sonuç solda da olabilir, sütun eklemek formülü bozmaz. F2 ve G2 hücrelerindeki formüller:
Sonuçlar F sütununda sırasıyla Murat, Derya ve Selin, G sütununda 22150, 15900 ve 18400 olur. Temsilci adı aranan kodun solunda olduğu halde sorunsuz gelir. Excel 2021 ve Microsoft 365 kullanıyorsanız aynı işi tek işlevle yapan ÇAPRAZARA daha da kısa bir yol sunar.

Kullanırken dikkat edin​

• aranan_değer: aranan_değer sayı, metin, mantıksal değer, ad verilmiş aralık ya da hücre başvurusu olabilir. Metin aramasında büyük ve küçük harf ayrımı yapılmaz. Joker karakterler desteklenir: ? tek bir karakterin, * ise birden çok karakterin yerini tutar. Joker karakterin kendisini aramak için önüne yaklaşık işareti (tilde) koyun: ~?, ~* ve ~~.
• tablo_dizisi: Arama her zaman tablo_dizisi aralığının ilk sütununda yapılır. İşlev, arama sütununun solundaki sütunlardan değer döndüremez; aranan anahtar ilk sütunda değilse İNDİS (INDEX) ile KAÇINCI (MATCH) işlevlerinin birleşimini kullanın. tablo_dizisi içinde yinelenen değerler varsa sonuç bulunan ilk eşleşmeye göre verilir.
• sütun_indis_sayısı: sütun_indis_sayısı 1 ise tablo_dizisi içindeki ilk sütunun, 2 ise ikinci sütunun değeri kullanılır. sütun_indis_sayısı için <1 değeri verilirse #DEĞER! (#VALUE!) hatası döner. sütun_indis_sayısı, tablo_dizisi içindeki sütun sayısından büyükse #BAŞV! (#REF!) hatası döner.
• aralık_bak: aralık_bak eşleşme türünü belirler. DOĞRU (ya da sıfırdan farklı bir değer) en yakın eşleşmeyi seçer: tam eşleşme varsa o, yoksa aranan değerden küçük olan en yakın değer kullanılır. YANLIŞ (ya da 0) tam eşleşme arar ve ilk sütunun sıralı olması gerekmez. aralık_bak belirtilmezse varsayılan değer DOĞRU kabul edilir, yani yaklaşık eşleşme yapılır. Tam eşleşme istenen çoğu durumda dördüncü parametreyi açıkça YANLIŞ olarak yazmanız önerilir.
• Yaklaşık eşleşmenin davranışı: aralık_bak DOĞRU iken ilk sütunun artan sırada dizilmiş olması gerekir; sıralama düzeni sayılar, metinler ve mantıksal değerler için -1, 0, 1, A, a, YANLIŞ, DOĞRU biçimindedir. aranan_değer listedeki en küçük değerden küçükse #YOK (#N/A) hatası döner. aranan_değer en küçük ve en büyük değer arasındaysa ondan küçük olan en yakın değer kullanılır. aranan_değer listedeki en büyük değerden büyükse en büyük değer kullanılır; bu da kendisinden küçük en yakın değerdir. Liste sıralı değilse sonuçlar yanıltıcı olabilir; hata değeri ya da yanlış bir satır dönebilir.
• Eşleşme bulunamazsa: aralık_bak YANLIŞ iken aranan_değer bulunamazsa #YOK (#N/A) hatası döner. Bu hatayı gizleyip yerine başka bir değer göstermek için EĞERYOKSA (IFNA) işlevini kullanabilirsiniz. B2 hücresindeki bir aramayı aşağıdaki biçimde sarmalayabilirsiniz:
  
  Excel formülü

  TR

  =EĞERYOKSA(DÜŞEYARA(A2;$B$2:$C$4;2;YANLIŞ);"Bulunamadı")

  EN

  =IFNA(VLOOKUP(A2,$B$2:$C$4,2,FALSE),"Bulunamadı")

• Uzun metin sınırı: İşlev yalnızca 256 karakterden kısa değerleri döndürebilir. Eşleşen değer 255 karakterden uzunsa #YOK (#N/A) hatası döner.
• İlgili işlevler: Excel 2021 ile eklenen ÇAPRAZARA (XLOOKUP) işlevi bu işlevin yerini almak üzere tasarlanmıştır. Bir değerin konumunu bulmak için KAÇINCI (MATCH) ya da ÇAPRAZEŞLEŞTİR (XMATCH) işlevini kullanabilirsiniz. İlk satırda arama yapıp aynı sütundan değer getirmek için YATAYARA (HLOOKUP), bir satır ya da sütundaki eşleşmeyi bulmak için ARA (LOOKUP), bir satır ile sütunun kesişimini almak için İNDİS (INDEX) işlevi uygundur.
• Sürüm ve VBA notu: İşlev Excel 1997 sürümünden beri kullanılabilir. VBA'da WorksheetFunction.VLookUp her sürümde düzgün çalışmayabilir; hatalardan kaçınmak için Application.VLookUp kullanın.

Mini Sayfa içindeki sonuç hücresini seçerek gerçek formülü görebilirsiniz. Formüllü örnekleri indirilebilir Excel dosyasında da kullanabilirsiniz.