Arama ve Başvuru Formüller

Excelde Düşeyara (VLOOKUP) Formülünü Nasıl Kullanırım

vlookup_rsm1

Merhabalar,

Düşeyara formülü excel’in en yararlı ve en önemli formüllerinden biridir. Genellikle büyük veriler arasından belirli bir değeri aramak için kullanılır. Orjinal ismi VLOOKUP olan formülün başındaki V “Vertical” yani dikey anlamına gelmektedir.

Düşeyara formülü belirtilen değeri, aramak istediğimiz veri topluluğunun içinde dikey( yukarıdan aşağıya) arar. Bir tek değer aranabileceği gibi çoklu veri araması da yapılabilir.Kullanımı aşağıdaki gibidir.

= DÜŞEYARA (aranan_değer, aranacak_tablo_dizisi, bulunursa_getirilecek_sütunun_numarası, eşleştirme çeşiti)

Düşeyara formülünde ilk olarak aramak istediğimiz veriyi belirtiyoruz,sonrasında bu veriyi nerede arayacağımızı belirtiyoruz,aradığımız değer bulunursa hangi sütunu görmek istiyorsak onun numarasını yazıyoruz,son olarakta tam eşleştirme yada yaklaşık eşleştirme mi olacağını belirtiyoruz.

vlookup_rsm1

Formülde ilk belirtilen kısım yani “A3” kısmı aranacak veri, ikinci kısımda ise “A5:C13”  burası ise aramak istediğimiz verinin bakılacağı yer.Sonraki kısımda 3 ile belirtiğimiz yer ise eğer aradığımız değer bulunursa yazılmasını istediğimiz sütunun sıra numarası, en sonda bulunan “YANLIŞ” yazısı ise aradığımız veri ile tam eşleştirme yapması için.Formülün sonucu aşağıdaki gibidir;

vlookup_rsm2

Formül “Cem”  verisini “Personel” sütununda aradı ve eşleştikten sonra 3 index numarasını verdiğimiz için “Tutar” kısmında bulunan değeri döndürdü bize.

Eğer ki formülde index numarası yerine yani “3” yerine “2” yazdığımız zaman bizde “Ürün Sayısı” sütunundan 16 numarasını getirecektir.


Şimdi biraz daha karışık bir düşeyara formülü yazalım.

vlookup_rsm3

Formülümüz A sütunundaki 1. hücreden 8. hücreye kadar olan verileri Personel tablomuzun isim kısmında arıyor.Eğer ki tam olarak veri eşleşmesi olur ise personel tablosundaki “Ürün sayısı” sütunundaki veriyi döndürüyor.

vlookup_rsm4

A sütunundaki isimler tablodaki sıradan farklı olarak yazılmış ancak düşeyara formülü için herhangi bir sorun oluşturmuyor.B sütunundaki değerleri bulduğumuz formüller yan taraflarına yazılmıştır.

Düşeyara formülünün fazlasıyla kullanım örnekleri mevcut, biz şimdilik giriş seviyesindeki örneklere değindik.İleri zamanlarda daha karmaşık daha büyük verilerin olduğu yazımız olacaktır.

40 Yorumlar

  • Ahmet

    Hocam merhabalar,
    Sizin yukarıda yaptığınız örnek üzerinden kendi dosyamda yaptm ancak yalnızca ilk baştaki bir kaç isimleri buluyor sonraki verileri bulamıyor.Ama bulunmayan verileri kendim baktığım zaman bulabiliyorum.Neden olabilir?
    teşekkürler

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Ahmet Bey,
      Sanırım formülü ilk satıra yazıp aşağı doğru kopyalama yapıyorsunuz.Genelde çok sık karşılaşılan bir hatadır bu.
      Yukarıdaki yazımda en sonda bulunan ekran görüntüsünde mevcut olan kodu aşağıdaki gibi düzenlendim.
      Eğer ki sizde bu şekilde aradığınız veri kısmına yani benim kodumda A1 ve A8 kısmına ve arama yaptığınız yerin hücre numaralarına (benim kodumda A10 ve D18 ) $ işareti koyar ve sonrasında aşağı sütunlara kopyalarsanız diğer verilerinizde bulunacaktır.

      =DÜŞEYARA($A$1:$A$8;$A$10:$D$18;2;YANLIŞ)

      Kolay Gelsin

      Yanıtla
  • nezih öztimur

    Hocam ellerinize sağlık 12 sayfalık herbiri 250 kişilik listeyi vermiş olduğunuz bilgiler doğrultusunda 15 dakikada bitirdim. Çok teşekkür ederim. Engin bilgilerinizden dolayı.

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhabalar,

      Rica ederim yardımcı olabildiğimi duymak çok güzel 🙂

      İyi çalışmalar

      Yanıtla
      • Mehmet

        Merhaba,

        Uzun bir çek-senet dosyam var. Bunların ay ay ne kadar çek-senedim olduğunu VLOOKUP formölü ile bulmak istiyorum. Yardımcı olabilir misiniz?

        Yanıtla
  • zafer

    merabalar

    tez ödevim için excelden dosya eşleştirmesi yapıyorum.kullandığım veriler 37 bin adet eşleştirmeye çalıştığım veriler ise 62 bin civarı tahmin edeceğiniz üzerine işlem uzun suruyo ve en kısa zamanda bitirmem gerekiyo.bunu nasıl yapıcam bilginiz varmı yukarıdaki verdiğiniz örnek işime yaramıyo sanırım? teşekkürler

    Yanıtla
  • Yılmaz Temiz
    Yılmaz Temiz

    Zafer Bey Merhabalar,
    Yukarıdaki örneklerim de formül tüm hücreleri aynı anda arıyor.Sizin dosyanızda olduğu gibi yüksek sayıda veri bulunan ve aranılan metinin,tablonun durumuna göre formülün cevap süresi değişiyor. Bu gibi durumlarda düşeyara formülünü daha performanslı hale getirmek için bazı düzenlemeler yapıyoruz.Örnek olarak ;
    1-) Formüldeki aranan değer kısmını tek hücre olarak belirtiyoruz yani “=DÜŞEYARA(A1:A100;” olan kısmı “=DÜŞEYARA(A1;” olarak değiştiriyoruz ki tek hücre kontrolü yapsın.Formülümüzü aşağı doğru indirdikçe hücre numarasını güncelleyecek.
    2-)Arama kısmını sınırlamak “=DÜŞEYARA(aranan_değer;$B$1:$C$1000 ” şeklinde değiştiriyoruz.
    3-)Bulunduğunda yazılacak sütunu arama yaptığımız yerin bir sağ yanına koymak.Yani aradığımız değer bulunduğunda yazılacak sütunun numarası 2 olmalı.Çünkü 10 sütunu olan bir tablonun en solundaki sütunda arama yapıp aradığımız değer bulunduğunda 9. sütunu yazdırmak istediğimizde aradaki sütunlarda hafızaya alınıyor buda performans kaybına yol açıyor.
    4-) Aranılan değerin olduğu veriler ve arama yapılan verilerin bulunduğu yerlerin tablo olmamasına dikkat edin.

    Yukarıdaki düzenlemelerden sonra formül daha performanslı çalışacaktır.

    İyi Çalışmalar

    Yanıtla
    • zafer

      anladım sağolsun.dünde bu yazdıklarınızı bayağı denedim de sanırım benim elle yapmam gerekiyo işin içinden çıkamadım 🙂 çok teşekkürler sağolsun

      Yanıtla
      • Yılmaz Temiz
        Yılmaz Temiz

        Zafer Bey Merhabalar,
        Dosya yapınızı ve verilerinizin yapısını bilmediğim için başka hangi formüller yardımcı olur işlemlerinizde bilmiyorum.Ancak indis ve kaçıncı formülleri ile de düşeyara benzeri sonuçlar elde edilebiliyor.Eğer ki dosyayı gönderme şansınız olursa yardımcı olmaya çalışırım.

        İyi Çalışmalar

        Yanıtla
        • zafer

          çalıştığım dosya bu 20 bine yakın elle eşleştirilmiş hali baktığınızda görüceksiniz 20 binden sonrası yer yer karışık tek tek kontrol ederek bitmiyo 🙁 burada bi kısmı var dosya sınırı olduğu için

        • Yılmaz Temiz
          Yılmaz Temiz

          Zafer Bey Merhabalar,

          Konuyla ilgili adresinize bir mail gönderdim.

          İyi Çalışmalar

  • SAK

    Merhaba,
    formülde bir yerlerde hatalı yapıyorum sanırım. email ile dosyayı paylaşsam destek olabilirmisiniz

    Yanıtla
  • sinasi balta

    Selam,
    benim de sizin yardımınıza ihtiyacım var. Benim sorum şu; eğer ki aynı anda iki afet kriterin aranmasını nasıl sağlayabilirim? diyelim ki sipariş numaraları ve bu sipariş numaralarına ait bedenler mevcut. A1 nolu siparişe ait olan S beden ürünlerin toplamını bulmak istiyorum. Şu ana kadar genelde aranan kriterler tek değer oldukları için sorun olmuyordu. bu iki kriteri aynı anda nasıl aratabilirim?

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhabalar,

      İstediğiniz toplama işlemini düşeyara ile yapmak biraz karışık olacaktır.Bu sebeple Etopla ve Çoketopla formülleri sizin isteğinizi fazlasıyla karşılar.Bu konuyla ilgili yazıma Excelde ETOPLA ve ÇOKETOPLA Formüllerin Kullanımı adresinden ulaşabilirsiniz.

      İyi Çalışmalar

      Yanıtla
  • Şubuo

    Hocam çok teşekkürler heryerde yazılar var hiçbirşey anlamamış ve yapamamıştım . ancak yazınızdan sonra artık formüllerden korkmuyorum 🙂

    Yanıtla
  • mustafa

    merhaba hocam

    benim elimde iki ayrı sayfa var normal düzeşey arada herşeyin aynı olması gerekmekte ama ben sekiz tane kelime yan yana ve içinde birisi bile geçse bulmasını istiyorum nasıl yapabilirim bunu

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Mustafa,

      Örnek bir dosya yükleyebilirsen daha anlaşılır olacaktır sorun.

      İyi Çalışmalar

      Yanıtla
  • osmanogretmen

    İyi akşamlar Yılmaz Bey
    =EĞER(A1=1;DÜŞEYARA(M1;sınav1!A5:AF41;2;0); ( M1 Sınavda 1. olanların sınıf sırası var),(A1 ise sınavın nosu) Ara diyorum 1 kişi olunca buluyor. Ancak 1. olan 2 veya daha fazla kişi ise sadece ilk sıradakini alıyor. Tüm 1. leri nasıl listeleyebilirim?

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Osman,

      Bana örnek bir dosya gönderebilir misin.Belki başka formüller ile istediğin işlemi yapabiliriz.

      İyi Çalışmalar

      Yanıtla
  • hüseyin özkıyıcı

    hocam iyi günler sıkıntılı bir tablo ile karşı karşıya kaldım yardımlarınızı bekliyorum.
    4 sutunda tarih / isim/ ürün adı/ miktar var diğer 4 sutunda aynı değerler var ve 3700 kadar satır mevcut bu şekilde karşılaştırma yapmak istiyorum ilk 4 sutunda bulnan bilgilerin diğer 4 sutunda olup olmadığı karşılaştırması isterseniz dosyada gönderebilirim.

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Hüseyin,
      Konuyla ilgili facebook’taki sayfamızdan cevap verdim sana.

      İyi Çalışmalar

      Yanıtla
  • Muzaffer Taş

    merhaba hocam,
    8000 satırın üzerinde olan exell sayfasında stok sayımı için aynı kod ta olan satırların daha kolay toplamını nasıl yapabilirim

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Muzaffer,
      Yapmak istediğin işlemi özet (pivot) tablo yada formüller kullanarak.Pivot tablo ile ilgili bilgin yoksa önümüzdeki günlerde bir yazı yazacağım.

      Formülle ise kodların olduğu sütunu bir yere kopyalayıp “Veri” sekmesi altından “Yinelenen Değerleri Kaldır” seçeneği ile her kodu tek hale getirebilirsin.Eğer tam olarak yapmak istediğin işlem her kodu saydırmak ise EĞERSAY formülünü kullanmalısın (Excelde EĞERSAY Formülünün Kullanımı), eğer kodlara ait verileri toplayacaksan ETOPLA formülünü kullanmalısın (Excelde ETOPLA Formülünü Kullanımı)

      İyi Çalışmalar

      Yanıtla
  • Begüm

    Merhabalar,
    Benim elimde aylık bir veri var . burada iş emrinin numarası ve her işin bir operasyon numaraları bulunmakta. Excelin 2. sayfasında da her iş emri ve operasyona ait birer değer bulunmakta. Benim yapmak istediğim iş emrini ve operasyon numarasını diğer sayfa ile karşılaştırarak değerleri boş ilk sayfadaki boş sütuna yazdırmak. Burada aylık bir veri olduğu için aynı iş emri ve op numarasından birden fazla sütunda olması. Nasıl yapabilirim yardımcı olursanız sevinirim.

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Begüm,

      Örnek olması için dosyanı iletişim sayfasından gönderebilir misin.

      İyi Çalışmalar

      Yanıtla
      • begüm

        Gönderdim

        Yanıtla
        • Yılmaz Temiz
          Yılmaz Temiz

          Merhaba Begüm,

          Mailini cevapladım

          İyi çalışmalar

        • begüm

          mailde goremedim hocam ulasmadı sanırım bana

        • Yılmaz Temiz
          Yılmaz Temiz

          Merhaba Begüm,
          Gün içerisinde tekrar göndermiştim.Önemsiz/spam klasörünü kontrol eder misin.

          İyi Çalışmalar

  • Ceyda

    Merhaba,

    Peki aynı isimden iki kişi var her ikisinin değerlerini getirmesini istiyorum fakat ilk ismin değerini her ikisine yazıyor şu şekilde mesela;

    ahmet 3
    ahmet 4
    ahmet 8

    bunları düşeyara yapıyorum farklı bir çalışma sayfasında ilk ahmetin değerini yazıyor

    ahmet 3
    ahmet 3
    ahmet 3 gibi

    nasıl çözebiliriz, teşekkürler

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba,
      Aynı verilerin olduğu tabloda düşeyara her zaman ilk bulduğu değerin karşılığını getirecektir.Tablo da yinelenen değerleri kaldırmak yada bunlara unique bir numara yada veri ekleyerek sorunu çözebilirsin.
      İyi Çalışmalar

      Yanıtla
      • Hazal

        Bulmak istediğim veri sütununa 2 tane Ahmet yazıyorum mesela. Birine 20 birine 30 gelmesini istiyorum. Unique numara vermeden bir formülle sağlama şansım yok mu? Size örnek bir tablo gönderme şansım var mı acaba

        İyi çalışmalar

        Yanıtla
        • Yılmaz Temiz
          Yılmaz Temiz

          Merhaba Hazal,
          İletişim sayfasından bana dosyanı gönderebilirsin.

          iyi çalışmalar

  • Kadir

    Hocam öncelikle teşekkür ediyorum. Bu formüle göre işlem yaptığımda değer sağlanmadığında #YOK diye ibare getiriyor. Bunun yerine yoksa hiçbirşey getirmeyip boş hücre olarak gösterebilir miyim? Yardımcı olursanız memnun olurum.

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Kadir,
      Eğerhata formülü tam olarak istediğin işlemi yapmaktadır.
      =Eğerhata(formülün;hata olursa yapılacak olan) boş gözükmesini istiyorsan “” yapabilirsin

      iyi çalışmalar

      Yanıtla
  • elif

    Merhaba
    a sutununda işçilerin işe giriş yılları var işe giriş yıllarına göre verilecek zamlar değişmekte verilecek zamlar b sutununda yazmakta c stununa a deki işe giriş yılına göre b sutununda ki zam rakamını yazdırmak istiyorum

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Elif,
      Yukarıda Düşeyara makalesinde tam olarak senin istediğin işlem anlatılmakta.C sütununda oluşturacağın formülde A sütununa göre arama yapıp sonuç olarak B sütununu getireceksin.
      İyi Çalışmalar

      Yanıtla
  • Ali Araç

    Hocam iyi günler dilerim. Bende şunu merak ediyorum. Aradığımız rakam bir aralıkta. Yani örneğin ders notumu puan olarak girdiğimde eğer 45 ile 55 arasında ise bu aralığın solunda mesela DC yazan hücreyi bana getirmesini istiyorum.

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Ali,
      İstediğin işlemi EĞER formülüne (EĞER Formülünün Kullanımı ) VE formülü ile Düşeyara formülünü ekleyerek yapabilirsin.Yalnız burada dikkat etmen gereken nokta, Düşeyara formülü her zaman sağ taraftaki sütunu getirir.Yani senin DC yazan sütunun puanın solunda ise düşeyara formülü olmaz.İndis kullanman gerekir.

      Örnek formül =EĞER(VE(A4>=45;A4<=55);Düşeyara_formülü;YANLIŞ)

      İyi Çalışmalar

      Yanıtla
  • Pınar Kaya

    Merhaba
    Yararlı bilgiler için çok teşekkürler.
    Çok açıklayıcı bir anlatım olmuş. İşimi çok kolaylaştırdı.

    Yanıtla

Yorum Yap

Pin It on Pinterest