Excel Başlangıç ve İleri Düzey Kullanıcılar İçin20+ VLOOKUP (DÜŞEYARA) Örneği

VLOOKUP (DÜŞEYARA) fonksiyonu, Excel'deki en popüler fonksiyonlardan biridir. Bu eğitimde, Excel'de VLOOKUP fonksiyonunun nasıl kullanılacağını, temel ve ileri düzeyden birçok örnekle adım adım öğreneceksiniz.

VLOOKUP fonksiyonunun tanıtımı – Söz Dizimi ve Argümanlar

Excel'de VLOOKUP fonksiyonu, çoğu kullanıcı için oldukça güçlü bir araçtır. Veri aralığının en solundaki bir değeri aramanıza ve belirttiğiniz sütundan aynı satırda eşleşen değeri döndürmenize olanak tanır. Aşağıdaki ekran görüntüsünde gösterildiği gibi çalışır.

VLOOKUP fonksiyonunun söz dizimi:

Argümanlar:

Fonksiyon Notları:

Temel VLOOKUP örnekleri

Bu bölümde, sıkça kullandığınız bazı VLOOKUP formüllerinden bahsedeceğiz.

2.1 Tam eşleşme ve yaklaşık eşleşme VLOOKUP

 2.1.1 Tam eşleşme VLOOKUP uygulama

Genellikle, VLOOKUP fonksiyonu ile tam bir eşleşme arıyorsanız, son argüman olarak FALSE kullanmanız yeterlidir.

Örneğin, belirli ID numaralarına göre ilgili Matematik notunu almak için şu adımları izleyin:

Aşağıdaki formülü boş bir hücreye (burada G2 seçildi) kopyalayıp yapıştırın ve sonucu almak için "Enter" tuşuna basın:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

VLOOKUP formülü nasıl çalışır?

Öncelikle, ID - C1005'i tablonun en sol sütununda arar. Yukarıdan aşağıya doğru ilerler ve değeri A6 hücresinde bulur.

Değeri bulduğu anda, sağa üçüncü sütuna geçer ve oradaki değeri alır.

Sonuç olarak, aşağıdaki ekran görüntüsünde gösterildiği gibi sonucu elde edersiniz:

 2.1.2 Yaklaşık eşleşme VLOOKUP uygulama

Yaklaşık eşleşme, veri aralıkları arasında değer aramak için kullanışlıdır. Tam eşleşme bulunamazsa, yaklaşık VLOOKUP arama değerinden küçük olan en büyük değeri döndürür.

Örneğin, aşağıdaki veri aralığına sahipseniz ve belirtilen siparişler Orders sütununda yoksa, B sütunundaki en yakın İndirimi nasıl bulabilirsiniz?

Adım1: VLOOKUP formülünü uygulayın ve diğer hücrelere doldurun

Aşağıdaki formülü sonucu almak istediğiniz hücreye kopyalayıp yapıştırın, ardından formülü diğer hücrelere uygulamak için doldurma tutamacını aşağıya sürükleyin.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Sonuç:

Şimdi, verilen değerlere göre yaklaşık eşleşmeleri elde edeceksiniz, ekran görüntüsüne bakın:

2.2 Excel'de büyük/küçük harf duyarlı VLOOKUP uygulama

Varsayılan olarak, VLOOKUP fonksiyonu büyük/küçük harf duyarsız arama yapar, yani küçük ve büyük harfleri aynı kabul eder. Ancak bazen, Excel'de büyük/küçük harf duyarlı bir arama yapmak isteyebilirsiniz; bu durumda normal VLOOKUP fonksiyonu yeterli olmayabilir. Böyle durumlarda, INDEX ve MATCH fonksiyonlarını EXACT fonksiyonu ile veya LOOKUP ve EXACT fonksiyonlarını birlikte kullanabilirsiniz.

Örneğin, aşağıdaki veri aralığında ID sütununda hem büyük hem küçük harfli metinler var. Şimdi, verilen ID numarasına karşılık gelen Matematik notunu döndürmek istiyorum.

Adım1: Herhangi bir formülü uygulayın ve diğer hücrelere doldurun

Aşağıdaki formüllerden herhangi birini, sonucu almak istediğiniz boş bir hücreye kopyalayıp yapıştırın. Ardından, formül hücresini seçin ve doldurma tutamacını aşağıya sürükleyerek formülü diğer hücrelere doldurun.

Formül1: Formülü yapıştırdıktan sonra "Ctrl" + "Shift" + "Enter" tuşlarına basın.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Formül2: Formülü yapıştırdıktan sonra "Enter" tuşuna basın.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Sonuç:

Ardından ihtiyacınız olan doğru sonuçları elde edeceksiniz. Ekran görüntüsüne bakın:

2.3 Excel'de sağdan sola doğru VLOOKUP ile değer arama

VLOOKUP fonksiyonu her zaman bir veri aralığının en sol sütununda bir değer arar ve sağdaki bir sütundan karşılık gelen değeri döndürür. Eğer tersine, yani sağdaki bir sütunda belirli bir değeri arayıp, sol sütundan karşılık gelen değeri döndürmek isterseniz, aşağıdaki ekran görüntüsünde olduğu gibi bir ters VLOOKUP işlemi yapmanız gerekir:

Bu işlemin adım adım detaylarını öğrenmek için tıklayın…

2.4 Excel'de ikinci, n'inci veya son eşleşen değeri VLOOKUP ile bulma

Normalde, VLOOKUP fonksiyonu ile birden fazla eşleşen değer bulunduğunda, yalnızca ilk eşleşen kayıt döndürülür. Bu bölümde, bir veri aralığında ikinci, n'inci veya son eşleşen değeri nasıl alacağınızı anlatacağım.

 2.4.1 İkinci veya n'inci eşleşen değeri VLOOKUP ile bulma ve döndürme

A sütununda bir isim listesi ve B sütununda satın aldıkları eğitim kursları olduğunu varsayalım. Şimdi, verilen müşterinin ikinci veya n'inci satın aldığı eğitimi bulmak istiyorsunuz. Ekran görüntüsüne bakın:

Burada, VLOOKUP fonksiyonu bu işlemi doğrudan çözemez. Ancak, alternatif olarak INDEX fonksiyonunu kullanabilirsiniz.

Adım1: Formülü uygulayın ve diğer hücrelere doldurun

Örneğin, verilen kritere göre ikinci eşleşen değeri almak için aşağıdaki formülü boş bir hücreye uygulayın ve ilk sonucu almak için "Ctrl" + "Shift" + "Enter" tuşlarına birlikte basın. Ardından, formül hücresini seçin ve doldurma tutamacını aşağıya sürükleyin.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Sonuç:

Şimdi, verilen isimlere göre ikinci eşleşen tüm değerler aynı anda görüntülendi.

 2.4.2 Son eşleşen değeri VLOOKUP ile bulma ve döndürme

Eğer aşağıdaki ekran görüntüsünde olduğu gibi son eşleşen değeri bulmak ve döndürmek istiyorsanız, bu VLOOKUP And Return The Last Matching Value eğitimi, son eşleşen değeri ayrıntılı olarak elde etmenize yardımcı olabilir.

2.5 İki değer veya tarih arasında eşleşen değerleri VLOOKUP ile bulma

Bazen, iki değer veya tarih arasında değer aramak ve karşılık gelen sonuçları döndürmek isteyebilirsiniz. Böyle bir durumda, sıralanmış bir tablo ile VLOOKUP yerine LOOKUP fonksiyonunu kullanabilirsiniz.

 2.5.1 Formül ile iki değer veya tarih arasında eşleşen değerleri VLOOKUP ile bulma

Adım1: Verileri düzenleyin ve aşağıdaki formülü uygulayın

Orijinal tablonuz sıralanmış bir veri aralığı olmalıdır. Ardından, aşağıdaki formülü boş bir hücreye kopyalayın veya yazın. Sonra, doldurma tutamacını sürükleyerek bu formülü ihtiyacınız olan diğer hücrelere uygulayın.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Sonuç:

Şimdi, verilen değere göre tüm eşleşen kayıtları elde edeceksiniz, ekran görüntüsüne bakın:

 2.5.2 Pratik bir özellikle iki değer veya tarih arasında eşleşen değerleri VLOOKUP ile bulma

Yukarıdaki formülü hatırlamak ve anlamak zor geliyorsa, burada size kolay bir araç tanıtacağım – "Kutools for Excel". "İki Değer Arasında Ara" özelliği ile, iki değer veya tarih arasındaki belirli bir değer veya tarihe göre karşılık gelen öğeyi kolayca döndürebilirsiniz.

1. "Kutools" > "Süper ARA" > "İki Değer Arasında Ara" menüsüne tıklayarak bu özelliği etkinleştirin.
2. Ardından, verilerinize göre açılan iletişim kutusundan işlemleri belirtin.

2.6 VLOOKUP fonksiyonunda kısmi eşleşmeler için joker karakterler kullanma

Excel'de, joker karakterler VLOOKUP fonksiyonu içinde kullanılabilir ve bu sayede arama değerinin bir kısmına göre eşleşme yapabilirsiniz. Örneğin, bir arama değerinin sadece bir kısmına göre tablodan eşleşen değeri döndürebilirsiniz.

Aşağıdaki ekran görüntüsünde olduğu gibi bir veri aralığım olduğunu varsayalım, şimdi, yalnızca adın (tam ad değil) ilk kısmına göre puanı çıkarmak istiyorum. Bu işlemi Excel'de nasıl çözebilirim?

Adım1: Formülü uygulayın ve diğer hücrelere doldurun

Aşağıdaki formülü boş bir hücreye kopyalayın veya yazın, ardından doldurma tutamacını sürükleyerek bu formülü ihtiyacınız olan diğer hücrelere uygulayın:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Sonuç:

Tüm eşleşen puanlar aşağıdaki ekran görüntüsünde gösterildiği gibi döndürüldü:

2.7 Başka bir çalışma sayfasından VLOOKUP ile değer arama

Genellikle, birden fazla çalışma sayfası ile çalışmanız gerekebilir. VLOOKUP fonksiyonu, başka bir sayfadan veri aramak için de aynı şekilde kullanılabilir.

Örneğin, aşağıdaki ekran görüntüsünde olduğu gibi iki çalışma sayfanız var. Belirttiğiniz çalışma sayfasından karşılık gelen verileri aramak ve döndürmek için aşağıdaki adımları izleyin:

Adım1: Formülü uygulayın ve diğer hücrelere doldurun

Aşağıdaki formülü, eşleşen öğeleri almak istediğiniz boş bir hücreye girin veya kopyalayın. Ardından, doldurma tutamacını aşağıya sürükleyerek bu formülü diğer hücrelere uygulayın.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Sonuç:

İhtiyacınız olan karşılık gelen sonuçları elde edeceksiniz, ekran görüntüsüne bakın:

2.8 Başka bir çalışma kitabından VLOOKUP ile değer arama

Bu bölümde, VLOOKUP fonksiyonunu kullanarak farklı bir çalışma kitabından eşleşen değerleri arama ve döndürme işlemi anlatılacaktır.

Örneğin, iki çalışma kitabınız olduğunu varsayalım. İlk kitapta ürünlerin ve maliyetlerinin bir listesi var. İkinci kitapta ise, her ürün için karşılık gelen maliyeti çıkarmak istiyorsunuz, aşağıdaki ekran görüntüsünde olduğu gibi.

Adım1: Formülü uygulayın

Kullanmak istediğiniz her iki çalışma kitabını da açın, ardından aşağıdaki formülü ikinci kitapta sonucu almak istediğiniz hücreye uygulayın. Sonra, bu formülü ihtiyacınız olan diğer hücrelere sürükleyip kopyalayın

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Sonuç:

2.90 veya #YOK hata değeri yerine boş veya belirli bir metin döndürme

Genellikle, VLOOKUP fonksiyonu ile karşılık gelen bir değer döndürdüğünüzde, eşleşen hücre boşsa0 döndürülür. Eşleşen değer bulunamazsa, aşağıdaki ekran görüntüsünde olduğu gibi #YOK hata değeri alırsınız.0 veya #YOK yerine boş bir hücre veya belirli bir değer göstermek istiyorsanız, bu VLOOKUP To Return Blank Or Specific Value Instead Of0 Or N/A eğitimi size yardımcı olabilir.

İleri Düzey VLOOKUP örnekleri

3.1 Çift yönlü arama (satır ve sütunda VLOOKUP)

Bazen, hem satırda hem sütunda aynı anda bir değer aramanız gerekebilir. Örneğin, aşağıdaki veri aralığına sahipseniz, belirli bir ürünü belirli bir çeyrekte bulmak isteyebilirsiniz. Bu bölümde, Excel'de bu işi çözmek için bir formül tanıtılacaktır.

Excel'de, çift yönlü arama yapmak için VLOOKUP ve MATCH fonksiyonlarını birlikte kullanabilirsiniz.

Aşağıdaki formülü boş bir hücreye uygulayın ve sonucu almak için "Enter" tuşuna basın.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 İki veya daha fazla kritere göre eşleşen değeri VLOOKUP ile bulma

Tek bir kritere göre eşleşen değeri bulmak kolaydır, ancak iki veya daha fazla kriteriniz varsa ne yapabilirsiniz?

 3.2.1 Formüllerle iki veya daha fazla kritere göre eşleşen değeri VLOOKUP ile bulma

Bu durumda, Excel'deki LOOKUP veya MATCH ve INDEX fonksiyonları bu işi hızlı ve kolayca çözmenize yardımcı olabilir.

Örneğin, aşağıdaki veri tablosuna sahibim. Belirli bir ürün ve boyuta göre eşleşen fiyatı döndürmek için aşağıdaki formüller yardımcı olabilir.

Adım1: Aşağıdaki formüllerden herhangi birini uygulayın

Formül1: Aşağıdaki formülü girin ve "Enter" tuşuna basın.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Formül2: Aşağıdaki formülü girin ve "Ctrl" + "Shift" + "Enter" tuşlarına basın.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Sonuç:

 3.2.2 Kutools for Excel ile iki veya daha fazla kritere göre eşleşen değeri VLOOKUP ile bulma

Yukarıdaki karmaşık formülleri tekrar tekrar uygulamak zorunda kalmak, iş verimliliğinizi yavaşlatabilir. Ancak, "Kutools for Excel" ile "Çoklu Koşul Araması" özelliği sayesinde, yalnızca birkaç tıklamayla bir veya daha fazla koşula göre karşılık gelen sonucu döndürebilirsiniz.

1. "Kutools" > "Süper ARA" > "Çoklu Koşul Araması" menüsüne tıklayarak bu özelliği etkinleştirin.
2. Ardından, verilerinize göre açılan iletişim kutusundan işlemleri belirtin.

3.3 Bir veya daha fazla koşulla birden fazla değeri VLOOKUP ile döndürme

Excel'de, VLOOKUP fonksiyonu bir değer arar ve birden fazla karşılık gelen değer bulunsa bile yalnızca ilkini döndürür. Bazen, tüm karşılık gelen değerleri bir satırda, bir sütunda veya tek bir hücrede döndürmek isteyebilirsiniz. Bu bölümde, bir veya daha fazla koşulla birden fazla eşleşen değeri bir çalışma kitabında nasıl döndüreceğinizi ele alacağız.

 3.3.1 Bir veya daha fazla koşula göre yatayda tüm eşleşen değerleri VLOOKUP ile bulma

A1:C14 aralığında ülke, şehir ve isimleri içeren bir veri tablonuz olduğunu varsayalım. Şimdi, aşağıdaki ekran görüntüsünde olduğu gibi "US" ülkesine ait tüm isimleri yatay olarak döndürmek istiyorsunuz. Bu işlemi adım adım öğrenmek için buraya tıklayın.

 3.3.2 Bir veya daha fazla koşula göre dikeyde tüm eşleşen değerleri VLOOKUP ile bulma

Belirli bir kritere göre tüm eşleşen değerleri dikey olarak VLOOKUP ile bulmak istiyorsanız, aşağıdaki ekran görüntüsünde olduğu gibi, ayrıntılı çözümü öğrenmek için lütfen buraya tıklayın.

 3.3.3 Bir veya daha fazla koşula göre tüm eşleşen değerleri tek bir hücreye VLOOKUP ile bulma

Birden fazla eşleşen değeri belirli bir ayırıcı ile tek bir hücreye VLOOKUP ile döndürmek istiyorsanız, TEXTJOIN fonksiyonunun yeni özelliği bu işi hızlı ve kolayca çözmenize yardımcı olabilir.

3.4 VLOOKUP ile eşleşen hücrenin tüm satırını döndürme

Bu bölümde, VLOOKUP fonksiyonu ile eşleşen bir değerin tüm satırını nasıl alacağınızı anlatacağım.

Adım1: Aşağıdaki formülü uygulayın

Aşağıdaki formülü, sonucu almak istediğiniz boş bir hücreye kopyalayın veya yazın ve ilk değeri almak için "Enter" tuşuna basın. Ardından, formül hücresini sağa doğru sürükleyerek tüm satır verilerini görüntüleyin.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Sonuç:

Şimdi, tüm satır verisinin döndürüldüğünü görebilirsiniz. Ekran görüntüsüne bakın:

3.5 Excel'de iç içe VLOOKUP

Bazen, birden fazla tablo arasında birbirine bağlı değerleri aramanız gerekebilir. Bu durumda, birden fazla VLOOKUP fonksiyonunu iç içe kullanarak nihai değeri elde edebilirsiniz.

Örneğin, iki ayrı tablo içeren bir çalışma sayfam var. İlk tabloda tüm ürün adları ve karşılık gelen satış temsilcileri listelenmiş. İkinci tabloda ise her satış temsilcisinin toplam satışı yer alıyor. Şimdi, aşağıdaki ekran görüntüsünde olduğu gibi, her ürünün satışını bulmak istiyorsanız, VLOOKUP fonksiyonunu iç içe kullanarak bu işlemi gerçekleştirebilirsiniz.

İç içe VLOOKUP fonksiyonu için genel formül:

Adım1: Aşağıdaki formülü uygulayın ve doldurun

Aşağıdaki formülü boş bir hücreye uygulayın, ardından bu formülü diğer hücrelere doldurmak için doldurma tutamacını aşağıya sürükleyin.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Sonuç:

Şimdi, aşağıdaki ekran görüntüsünde gösterildiği gibi sonucu elde edeceksiniz:

3.6 Başka bir sütundaki listeye göre değerin var olup olmadığını kontrol etme

VLOOKUP fonksiyonu, başka bir sütundaki veri listesine göre değerlerin var olup olmadığını kontrol etmenize de yardımcı olabilir. Örneğin, aşağıdaki ekran görüntüsünde olduğu gibi, C sütunundaki isimleri aramak ve isim A sütununda bulunursa Evet, bulunmazsa Hayır döndürmek istiyorsanız.

Adım1: Aşağıdaki formülü uygulayın

Aşağıdaki formülü boş bir hücreye uygulayın, ardından bu formülü doldurmak istediğiniz hücrelere doldurma tutamacını aşağıya sürükleyin.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Sonuç:

Ve ihtiyacınız olan sonucu elde edeceksiniz, ekran görüntüsüne bakın:

3.7 VLOOKUP ile satır veya sütunlardaki tüm eşleşen değerleri toplama

Sayısal verilerle çalışırken, bir tablodan eşleşen değerleri çekip, birkaç sütun veya satırdaki sayıları toplamanız gerekebilir. Bu bölümde, bu işlemi kolayca yapmanızı sağlayacak bazı formüller tanıtılacaktır.

 3.7.1 VLOOKUP ile bir satırda veya birden fazla satırda tüm eşleşen değerleri toplama

Bir ürün listesinde, aşağıdaki ekran görüntüsünde olduğu gibi birkaç ayın satışları olduğunu varsayalım. Şimdi, verilen ürünlere göre tüm ayların siparişlerini toplamanız gerekiyor.

Adım1: Aşağıdaki formülü uygulayın

Aşağıdaki formülü boş bir hücreye kopyalayın veya yazın, ardından ilk sonucu almak için "Ctrl" + "Shift" + "Enter" tuşlarına birlikte basın. Sonra, bu formülü ihtiyacınız olan diğer hücrelere doldurmak için doldurma tutamacını aşağıya sürükleyin.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Sonuç:

İlk eşleşen değerin satırındaki tüm değerler toplandı, ekran görüntüsüne bakın:

 3.7.2 VLOOKUP ile bir sütunda veya birden fazla sütunda tüm eşleşen değerleri toplama

Aşağıdaki ekran görüntüsünde olduğu gibi, belirli aylar için toplam değeri toplamak istiyorsanız, normal VLOOKUP fonksiyonu yeterli olmayabilir. Burada, SUM, INDEX ve MATCH fonksiyonlarını birlikte kullanarak bir formül oluşturmalısınız.

Adım1: Aşağıdaki formülü uygulayın

Aşağıdaki formülü boş bir hücreye uygulayın, ardından bu formülü diğer hücrelere doldurmak için doldurma tutamacını aşağıya sürükleyin.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Sonuç:

Şimdi, belirli bir ayda bir sütunda ilk eşleşen değerler toplandı, ekran görüntüsüne bakın:

 3.7.3 Kutools for Excel ile ilk veya tüm eşleşen değerleri VLOOKUP ile toplama

Yukarıdaki formülleri hatırlamak zor geliyorsa, bu durumda size güçlü bir özellik önereceğim - "Ara ve Topla" özelliği ile "Kutools for Excel". Bu özellik sayesinde, satır veya sütunlarda ilk eşleşen ya da tüm eşleşen değerleri VLOOKUP ile mümkün olan en kolay şekilde toplayabilirsiniz.

1. "Kutools" > "Süper ARA" > "Ara ve Topla" menüsüne tıklayarak bu özelliği etkinleştirin.
2. Ardından, ihtiyacınıza göre açılan iletişim kutusundan işlemleri belirtin.

 3.7.4 Hem satır hem sütunlarda tüm eşleşen değerleri VLOOKUP ile toplama

Hem sütun hem satırda eşleşme yapmanız gerektiğinde değerleri toplamak istiyorsanız, örneğin aşağıdaki ekran görüntüsünde olduğu gibi Mar ayında Sweater ürününün toplam değerini almak için.

Burada, bu işlemi gerçekleştirmek için SUMPRODUCT fonksiyonunu kullanabilirsiniz.

Aşağıdaki formülü bir hücreye uygulayın ve sonucu almak için "Enter" tuşuna basın, ekran görüntüsüne bakın:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 Anahtar sütunlara göre iki tabloyu VLOOKUP ile birleştirme

Günlük işlerinizde, veri analizinde, bir veya daha fazla anahtar sütuna göre tüm gerekli bilgileri tek bir tabloda toplamanız gerekebilir. Bu işlemi gerçekleştirmek için, VLOOKUP fonksiyonu yerine INDEX ve MATCH fonksiyonlarını kullanabilirsiniz.

 3.8.1 Tek anahtar sütuna göre iki tabloyu VLOOKUP ile birleştirme

Örneğin, ilk tabloda ürünler ve isimler, ikinci tabloda ise ürünler ve siparişler verisi var. Şimdi, ortak ürün sütununu eşleştirerek bu iki tabloyu tek bir tabloda birleştirmek istiyorsunuz.

Adım1: Aşağıdaki formülü uygulayın

Aşağıdaki formülü boş bir hücreye uygulayın. Ardından, bu formülü doldurmak istediğiniz hücrelere doldurma tutamacını aşağıya sürükleyin

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Sonuç:

Şimdi, anahtar sütun verilerine göre sipariş sütunu ilk tabloya eklenmiş birleşik bir tablo elde edeceksiniz.

 3.8.2 Birden fazla anahtar sütuna göre iki tabloyu VLOOKUP ile birleştirme

Birleştirmek istediğiniz iki tabloda birden fazla anahtar sütun varsa, bu ortak sütunlara göre tabloları birleştirmek için aşağıdaki adımları izleyin.

Genel formül:

Adım1: Aşağıdaki formülü uygulayın

Aşağıdaki formülü sonucu almak istediğiniz boş bir hücreye uygulayın ve ilk eşleşen değeri almak için "Ctrl" + "Shift" + "Enter" tuşlarına birlikte basın, ekran görüntüsüne bakın:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Adım2: Formülü diğer hücrelere doldurun

Ardından, ilk formül hücresini seçin ve bu formülü ihtiyacınız olan diğer hücrelere doldurmak için doldurma tutamacını sürükleyin:

3.9 Birden fazla çalışma sayfasında eşleşen değerleri VLOOKUP ile bulma

Excel'de birden fazla çalışma sayfasında VLOOKUP yapmak zorunda kaldınız mı? Örneğin, üç çalışma sayfanız var ve her birinde veri aralıkları bulunuyor. Bu sayfalardan belirli kriterlere göre değerleri almak istiyorsanız, adım adım anlatılan VLOOKUP Values Across Multiple Worksheets eğitimini takip edebilirsiniz.

VLOOKUP ile eşleşen değerlerin hücre biçimlendirmesini koruma

Eşleşen değerleri ararken, orijinal hücre biçimlendirmesi (yazı tipi rengi, arka plan rengi, veri biçimi vb.) korunmaz. Hücre veya veri biçimlendirmesini korumak için, bu bölümde bazı pratik ipuçları paylaşılacaktır.

4.1 Eşleşen değeri VLOOKUP ile bulup hücre rengi ve yazı tipi biçimlendirmesini koruma

Bilindiği üzere, normal VLOOKUP fonksiyonu yalnızca başka bir veri aralığından eşleşen değeri alabilir. Ancak, bazen karşılık gelen değeri hücre biçimlendirmesiyle birlikte almak isteyebilirsiniz; örneğin, doldurma rengi, yazı tipi rengi ve yazı tipi stili gibi. Bu bölümde, Excel'de kaynak biçimlendirmeyi koruyarak eşleşen değerleri nasıl alacağınızı ele alacağız.

Aşağıdaki adımları izleyerek, arama yapıp karşılık gelen değeri hücre biçimlendirmesiyle birlikte döndürebilirsiniz:

Adım1: Kod1'i Sayfa Kod Modülüne kopyalayın

1. VLOOKUP yapmak istediğiniz verilerin bulunduğu çalışma sayfasında, sayfa sekmesine sağ tıklayın ve açılan menüden "Kodu Görüntüle" seçeneğini seçin. Ekran görüntüsüne bakın:
   
2. Açılan "Microsoft Visual Basic for Applications" penceresinde, aşağıdaki VBA kodunu Kod penceresine kopyalayın.
3. VBA kodu1: VLOOKUP ile arama değeriyle birlikte hücre biçimlendirmesini alma

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

Adım2: Kod2'yi Modül penceresine kopyalayın

1. Hâlâ "Microsoft Visual Basic for Applications" penceresindeyken, "Ekle" > "Modül" seçeneğine tıklayın ve ardından aşağıdaki VBA kodu2'yi "Modül" penceresine kopyalayın.
2. VBA kodu2: VLOOKUP ile arama değeriyle birlikte hücre biçimlendirmesini alma

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

Adım3: VBAproject için seçeneği belirleyin

1. Yukarıdaki kodları ekledikten sonra, "Microsoft Visual Basic for Applications" penceresinde "Araçlar" > "Başvurular" menüsüne tıklayın. Ardından, "Başvurular – VBAProject" iletişim kutusunda "Microsoft Scripting Runtime" kutusunu işaretleyin. Ekran görüntülerine bakın:
2. Daha sonra, iletişim kutusunu kapatmak için "Tamam"a tıklayın ve kod penceresini kaydedip kapatın.

Adım4: Sonucu almak için formülü yazın

1. Şimdi, çalışma sayfasına dönün ve aşağıdaki formülü uygulayın. Ardından, doldurma tutamacını aşağıya sürükleyerek tüm sonuçları biçimlendirmeleriyle birlikte alın. Ekran görüntüsüne bakın:
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 VLOOKUP ile döndürülen değerin tarih biçimini koruma

VLOOKUP fonksiyonu ile tarih biçiminde bir değer arayıp döndürdüğünüzde, sonuç bazen sayı olarak görüntülenebilir. Döndürülen sonucun tarih biçimini korumak için, VLOOKUP fonksiyonunu TEXT fonksiyonu ile birlikte kullanmalısınız.

Adım1: Aşağıdaki formülü uygulayın

Aşağıdaki formülü boş bir hücreye uygulayın. Ardından, bu formülü diğer hücrelere doldurmak için doldurma tutamacını sürükleyin.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Sonuç:

Tüm eşleşen tarihler aşağıdaki ekran görüntüsünde gösterildiği gibi döndürüldü:

4.3 VLOOKUP ile hücre yorumunu döndürme

Excel'de VLOOKUP ile hem eşleşen hücre verisini hem de ona ait yorumu almak istediğiniz oldu mu? Aşağıdaki ekran görüntüsünde olduğu gibi, aşağıda verilen Kullanıcı Tanımlı Fonksiyon bu işlemi gerçekleştirmenize yardımcı olabilir.

Adım1: Kodu bir Modüle kopyalayın

1. "ALT" + "F11" tuşlarına basılı tutarak "Microsoft Visual Basic for Applications" penceresini açın.
2. "Ekle" > "Modül" seçeneğine tıklayın, ardından aşağıdaki kodu "Modül" penceresine kopyalayıp yapıştırın.
   VBA kodu: VLOOKUP ile eşleşen değeri ve hücre yorumunu döndürme:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Daha sonra kod penceresini kaydedip kapatın.

Adım2: Sonucu almak için formülü yazın

1. Şimdi, aşağıdaki formülü girin ve bu formülü diğer hücrelere doldurmak için doldurma tutamacını sürükleyin. Böylece hem eşleşen değerleri hem de yorumları aynı anda döndürür, ekran görüntüsüne bakın:
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 Metin olarak saklanan sayıları VLOOKUP ile bulma

Örneğin, orijinal tabloda ID numarası sayı biçiminde, arama yapılan hücrelerde ise metin olarak saklanmış bir veri aralığım var. Normal VLOOKUP fonksiyonu ile #YOK hatası alabilirsiniz. Bu durumda, doğru bilgiyi almak için VLOOKUP fonksiyonu içinde TEXT ve VALUE fonksiyonlarını kullanabilirsiniz. Bunu başarmak için aşağıdaki formülü kullanın:

Adım1: Aşağıdaki formülü uygulayın ve doldurun

Aşağıdaki formülü boş bir hücreye uygulayın, ardından bu formülü doldurmak için doldurma tutamacını aşağıya sürükleyin.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Sonuç:

Şimdi, aşağıdaki ekran görüntüsünde gösterildiği gibi doğru sonuçları elde edeceksiniz: