20+ Excel Başlangıç ve İleri Düzey Kullanıcılar İçin VLOOKUP Örnekleri

VLOOKUP işlevi, Excel’in en popüler işlevlerinden biridir. Bu öğreticide, VLOOKUP işlevinin Excel’de nasıl kullanılacağı; onlarca temel ve ileri düzey örnek üzerinden adım adım açıklanacaktır.

VLOOKUP işlevine giriş – Söz dizimi ve bağımsız değişkenler

Excel'de VLOOKUP işlevi, çoğu Excel kullanıcısı için güçlü bir işlevdir; Veri Aralığı sol tarafında bir değeri aramanıza ve aşağıdaki ekran görüntüsünde gösterildiği gibi aynı satırda belirttiğiniz bir sütundan eşleşen bir değeri döndürmenize olanak tanır.

VLOOKUP işlevinin söz dizimi:

Bağımsız değişkenler:

İşlev Notları:

Temel VLOOKUP örnekleri

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

2,1 Tam eşleşme ve yaklaşık eşleşme VLOOKUP

2,1.1 Tam eşleşme VLOOKUP yapın

Normalde VLOOKUP işleviyle tam eşleşme arıyorsanız, son bağımsız değişken olarak YANLIŞ kullanmanız yeterlidir.

Örneğin, belirli kimlik numaralarına göre ilgili Matematik puanlarını almak için şunu yapın:

Lütfen aşağıdaki formülü boş bir hücreye (burada G2'yi seçtim) 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?

İlk olarak, tablonun en sol sütununda C1005 kimliğini arar ve yukarıdan aşağıya doğru ilerleyerek A6 hücresindeki değeri bulur.

Değeri bulduğu anda, üçüncü sütuna doğru ilerler ve oradaki değeri çeker.

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

2,1.2 Yaklaşık eşleşmeli VLOOKUP yapın

Yaklaşık eşleşme, Arama değeri için Aralık içinde kullanışlıdır. Tam eşleşme bulunamazsa, yaklaşık eşleşmeli VLOOKUP, arama değerinden küçük olan en büyük değeri döndürür.

Örneğin, aşağıdaki veri aralığınız varsa ve belirtilen siparişler Siparişler sütununda yer almıyorsa, B sütunundaki en yakın indirimi nasıl elde edersiniz?

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

Sonucu görüntülemek istediğiniz hücreye aşağıdaki formülü kopyalayıp yapıştırın, ardından formülü diğer hücrelere uygulamak için doldurma tutamacını aşağı doğru sürükleyin.

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

Sonuç:

Artık 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 harfe duyarlı VLOOKUP yapın

Varsayılan olarak VLOOKUP işlevi büyük/küçük harf duyarlılığı olmadan arama yapar; yani küçük ve büyük harfleri aynı şekilde değerlendirir. Ancak bazen Excel’de büyük/küçük harfe duyarlı bir arama yapmanız gerekebilir—bu durumda standart VLOOKUP işlevi yeterli olmaz. Böyle senaryolarda EXACT işlevini INDEX ve MATCH ya da LOOKUP ile birlikte kullanabilirsiniz.

Örneğin, aşağıdaki Veri Aralığım var ve Kimlik sütunu, “Tümü Büyük Harf” veya “Küçük Harfli Dizilere Göre Filtrele” içeren metin dizileri barındırıyor. Şimdi, belirtilen kimlik numarasına karşılık gelen Matematik puanını almak istiyorum.

Adım 1: Aşağıdaki formüllerden herhangi birini uygulayın ve diğer hücrelere doldurun

Lütfen 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ı, bu formülü uygulamak istediğiniz hücrelere kadar aşağı doğru sürükleyin.

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

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

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

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

Sonuç:

Artık ihtiyacınız olan doğru sonuçları elde edeceksiniz. Ekran görüntüsüne göz atın:

2,3 Excel'de değerleri sağdan sola VLOOKUP ile arayın

VLOOKUP işlevi, her zaman bir veri aralığının en solundaki sütunda bir değer arar ve bu değere karşılık gelen sonucu sağdaki bir sütundan getirir. Ancak sağdaki bir sütunda belirli bir değer arayıp, bunun karşılığını en sol sütunda döndürmek istediğinizde “ters VLOOKUP” yapmış olursunuz — aşağıdaki ekran görüntüsünde gösterildiği gibi:

Bu görevle ilgili ayrıntılı adımları öğrenmek için tıklayın…

2,4 Excel'de ikinci, n'inci veya son eşleşen değeri VLOOKUP ile bulun

Normalde VLOOKUP işlevi kullanıldığında birden fazla eşleşen değer varsa yalnızca ilk eşleşme 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 alabileceğinizi göstereceğim.

2,4.1 İkinci veya n'inci eşleşen değeri VLOOKUP ile bulun ve döndürün

Diyelim ki A sütununda müşteri adları, B sütununda ise satın aldıkları eğitim kursları yer alıyor. Şimdi, belirtilen müşterinin aldığı ikinci veya n’inci eğitim kursunu nasıl bulacağınızı merak ediyorsunuz. Ekran görüntüsüne göz atın:

VLOOKUP işlevi bu görevi doğrudan çözemez; ancak alternatif olarak INDEX işlevini kullanabilirsiniz.

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

Örneğin, belirtilen kriterlere göre ikinci eşleşen değeri almak için aşağıdaki formülü boş bir hücreye yazın ve ilk sonucu elde etmek amacıyla **Ctrl + Shift + Enter** tuşlarına birlikte basın. Ardından formül hücresini seçip doldurma tutamacını, bu formülü uygulamak istediğiniz hücrelere kadar aşağı doğru 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ç:

Artık belirtilen isimlere göre tüm ikinci eşleşen değerler aynı anda görüntüleniyor.

2,4.2 Son eşleşen değeri VLOOKUP ile bulun ve döndürün

Aşağıdaki ekran görüntüsünde gösterildiği gibi son eşleşen değeri VLOOKUP ile arayıp döndürmek istiyorsanız, bu Son Eşleşen Değeri VLOOKUP ile Bulma ve Döndürme öğreticisi, son eşleşen değeri adım adım nasıl alabileceğinizi ayrıntılı şekilde gösterebilir.

2,5 İki verilen değer veya tarih arasında eşleşen değerleri VLOOKUP ile bulun

Bazen, iki değer veya tarih arasında bir arama değeri aralığı belirleyip aşağıdaki ekran görüntüsünde gösterildiği gibi ilgili sonuçları döndürmek isteyebilirsiniz. Böyle bir durumda, sıralı bir tabloyla VLOOKUP işlevi yerine LOOKUP işlevini kullanabilirsiniz.

2,5.1 İki verilen değer veya tarih arasında eşleşen değerleri formülle VLOOKUP ile bulun

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

Orijinal tablonuz sıralı bir Veri Aralığı olmalıdır. Ardından aşağıdaki formülü boş bir hücreye kopyalayın veya girin ve ihtiyaç duyduğunuz diğer hücrelere doldurmak için doldurma tutamacını sürükleyin.

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

Sonuç:

Artık 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 İki verilen değer veya tarih arasında eşleşen değerleri kolay bir özellik ile VLOOKUP ile bulun

Yukarıdaki formülü hatırlamakta veya anlamakta zorlanıyorsanız, işinizi kolaylaştıracak harika bir araçtan söz edeyim: «Kutools for Excel». «İki değer arasında veri bul» özelliğini kullanarak, iki değer veya tarih aralığındaki belirli bir değere ya da tarihe göre ilgili öğeyi kolayca getirebilirsiniz.

1. Bu özelliği etkinleştirmek için **Kutools > Süper ARA > İki değer arasında veri bul** seçeneğine tıklayın.
2. Ardından, iletişim kutusundan verilerinize göre işlemleri belirtin.

2,6 VLOOKUP işlevinde kısmi eşleşmeler için joker karakterler kullanma

Excel'de joker karakterler, VLOOKUP işleviyle birlikte kullanılarak arama değerinde kısmi eşleşmeler yapmanıza olanak tanır. Örneğin, bir tablodan yalnızca arama değerinizin bir kısmına dayanarak eşleşen sonucu VLOOKUP ile döndürebilirsiniz.

Diyelim ki aşağıdaki ekran görüntüsünde gösterildiği gibi bir veri aralığım var. Şimdi, “Ad”a göre (tam ad değil) puanı nasıl çıkarabilirim?

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

Lütfen aşağıdaki formülü boş bir hücreye kopyalayın veya girin ve ardından bu formülü ihtiyaç duyduğunuz diğer hücrelere doldurmak için doldurma tutamacını sürükleyin:

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

Sonuç:

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

2,7 Başka bir çalışma sayfasından değerleri VLOOKUP ile bulun

Genellikle birden fazla çalışma sayfasıyla çalışmanız gerekebilir. VLOOKUP işlevi, tek bir çalışma sayfasında veri aramak için kullanılabileceği gibi başka bir sayfadan veri çekmek için de kullanılabilir.

Örneğin, aşağıdaki ekran görüntüsünde gösterildiği gibi iki çalışma sayfanız olsun. Belirttiğiniz çalışma sayfasından ilgili verileri arayıp döndürmek için aşağıdaki adımları izleyin:

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

Eşleşen öğeleri almak istediğiniz boş bir hücreye aşağıdaki formülü girin veya kopyalayın. Ardından, formülü uygulamak istediğiniz hücrelere kadar doldurma tutamacını aşağı doğru sürükleyin.

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

Sonuç:

İhtiyacınız olan ilgili sonuçları elde edeceksiniz; ekran görüntüsüne bakın:

2,8 Başka bir çalışma kitabından değerleri VLOOKUP ile bulun

Bu bölümde, VLOOKUP işlevi kullanılarak farklı bir çalışma kitabından eşleşen değerlerin nasıl aranıp döndürüleceği ele alınacaktır.

Örneğin, iki çalışma kitabınız olduğunu varsayalım. İlk çalışma kitabında ürün listesi ve ilgili maliyetler yer alıyor. İkinci çalışma kitabında ise aşağıdaki ekran görüntüsünde gösterildiği gibi her ürün kaleminin karşılık gelen maliyetini çekmek istiyorsunuz.

Adım 1: Formülü uygulayın

İlk olarak kullanmak istediğiniz her iki çalışma kitabını da açın. Ardından, ikinci çalışma kitabında sonucu görüntülemek istediğiniz hücreye aşağıdaki formülü girin ve bu formülü ihtiyaç duyduğunuz diğer hücrelere sürükleyerek kopyalayın.

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

Sonuç:

2,9 0 veya #N/A hatası yerine boş hücre veya belirli bir metin döndürün

Genellikle VLOOKUP işleviyle bir değer aradığınızda, eşleşen hücre boşsa sonuç olarak 0 döndürülür. Eşleşme bulunamazsa ise aşağıdaki ekran görüntüsünde görüldüğü gibi #N/A hatası alırsınız. Ancak 0 veya #N/A yerine boş bir hücre ya da kendi belirlediğiniz özel bir değer göstermek istiyorsanız, bu 0 veya N/A Yerine Boş veya Belirli Değer Döndüren VLOOKUPöğreticisi tam size göre!

Gelişmiş VLOOKUP örnekleri

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

Bazen hem satır hem sütunda aynı anda değer aramanız gerekebilir; yani iki boyutlu bir arama yapmanız gerekir. Örneğin, aşağıdaki Veri Aralığınız varsa ve belirli bir çeyrek için belirli bir ürünün değerini almanız gerekiyorsa, bu bölüm Excel’de bu görevi gerçekleştirmeniz için size uygun bir formül sunacaktır.

Excel'de çift yönlü arama yapmak için VLOOKUP ve MATCH işlevlerini birlikte kullanabilirsiniz.

Lütfen aşağıdaki formülü boş bir hücreye girin ve sonucu görmek 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 VLOOKUP eşleşen değer

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

3,2.1 Formüllerle iki veya daha fazla kritere göre VLOOKUP eşleşen değer

Bu durumda, Excel'deki LOOKUP ya da MATCH ve INDEX işlevleri, bu görevi hızlı ve kolayca çözmenize yardımcı olabilir.

Örneğin, aşağıdaki veri tablom var; belirli bir ürün ve bedene göre karşılık gelen fiyatı döndürmek için aşağıdaki formüller size yardımcı olabilir.

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

Formül 1: 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ül 2: Aşağıdaki formülü girin ve uygulamak için "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 İki veya daha fazla kritere göre VLOOKUP eşleşen değer Kutools for Excel ile

Yukarıdaki karmaşık formülleri tekrar tekrar uygulamak zorlu olabilir ve çalışma verimliliğinizi düşürebilir. Ancak "Kutools for Excel", yalnızca birkaç tıklama ile bir veya daha fazla koşula göre ilgili sonucu döndürmenizi sağlayan **"Arama – Çoklu Koşul Araması"** özelliğini sunar.

1. Bu özelliği etkinleştirmek için "Kutools" > "Süper ARA" > "Arama – Çoklu Koşul Araması" seçeneğine tıklayın.
2. Ardından, iletişim kutusundan verilerinize göre işlemleri belirtin.

3,3 Bir veya daha fazla kritere göre birden çok değer döndürmek için VLOOKUP

Excel'de VLOOKUP işlevi bir değeri arar ve birden çok eşleşme bulduğunda yalnızca ilk eşleşen değeri döndürür. Ancak bazen tüm eşleşen değerleri bir satırda, bir sütunda veya tek bir hücrede görüntülemek isteyebilirsiniz. Bu bölümde, bir çalışma kitabında bir veya daha fazla koşula göre birden çok eşleşen değerin nasıl döndürüleceği açıklanmaktadır.

3,3.1 Bir veya daha fazla koşula göre tüm eşleşen değerleri yatay olarak VLOOKUP ile getirme

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 gösterildiği gibi "ABD" menşeli tüm isimleri yatay olarak döndürmek istiyorsunuz. Bu görevi tamamlamak için adım adım sonuca ulaşmak için buraya tıklayın.

3,3.2 Bir veya daha fazla koşula göre tüm eşleşen değerleri dikey olarak VLOOKUP ile getirme

Belirli kriterlere göre aşağıdaki ekran görüntüsünde gösterildiği gibi eşleşen tüm değerleri dikey olarak VLOOKUP ile getirmeniz gerekiyorsa, detaylı çözümü görmek 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 getirme

Birden çok eşleşen değeri belirtilen bir ayırıcıyla tek bir hücreye VLOOKUP ile getirmek istiyorsanız, TEXTJOIN işlevinin yeni özelliği bu görevi hızlı ve kolay şekilde çözmenize yardımcı olabilir.

3,4 Eşleşen bir hücrenin Tüm satır değerini döndürmek için VLOOKUP

Bu bölümde, VLOOKUP işlevini kullanarak eşleşen bir değere ait tüm satır değerlerini nasıl alabileceğinizi göstereceğim.

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

Lütfen aşağıdaki formülü, sonucu görüntülemek 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, tüm satır verisi görünene kadar formül hücresini sağa doğru sürükleyin.

=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 göz atın:

3,5 Excel'de iç içe VLOOKUP

Bazen, birden fazla tablo arasında bağlantılı değerleri aramanız gerekebilir. Böyle durumlarda, nihai değeri elde etmek için birden fazla VLOOKUP işlevini iç içe kullanabilirsiniz.

Örneğin, iki ayrı tabloyu içeren bir çalışma sayfam var: İlk tablo tüm ürün adlarını ve bunlara karşılık gelen satıcıları listelerken, ikinci tablo her satıcının toplam satışlarını gösteriyor. Aşağıdaki ekran görüntüsünde gösterildiği gibi her ürünün satışını bulmak istiyorsanız, bu görevi gerçekleştirmek için VLOOKUP işlevini iç içe kullanabilirsiniz.

İç içe VLOOKUP işlevinin genel formülü şöyledir:

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

Lütfen aşağıdaki formülü boş bir hücreye girin ve ardından bu formülü uygulamak istediğiniz hücrelere kadar doldurma tutamacını aşağı doğru 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 alacaksınız:

3,6 Başka bir sütundaki liste verilerine göre değerin var olup olmadığını kontrol etme

VLOOKUP işlevi ayrıca başka bir sütundaki veri listesine göre değerlerin var olup olmadığını kontrol etmenize de yardımcı olabilir. Örneğin, C sütunundaki isimleri aramak ve aşağıdaki ekran görüntüsünde gösterildiği gibi ismin A sütununda bulunup bulunmadığına göre Evet veya Hayır döndürmek istiyorsanız.

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

Lütfen aşağıdaki formülü boş bir hücreye girin, ardından doldurma tutamacını istediğiniz hücrelere kadar aşağı doğru sürükleyin.

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

Sonuç:

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

3,7 Satırlarda veya sütunlarda eşleşen tüm değerleri VLOOKUP ile bulun ve toplayın

Sayısal verilerle çalışırken, bir tablodan eşleşen değerleri çıkarmanız ve birden fazla sütun veya satırdaki sayıları toplamanız gerekebilir. Bu bölüm, bu görevi kolayca yerine getirmenize yardımcı olabilecek bazı formülleri tanıtıyor.

3,7.1 Bir satırda veya birden fazla satırda eşleşen tüm değerleri VLOOKUP ile bulun ve toplayın

Aşağıdaki ekran görüntüsünde gösterildiği gibi, birkaç aya ait satış verileriyle bir ürün listeniz olduğunu varsayalım. Şimdi, belirtilen ürünlere göre tüm aylardaki siparişleri toplamanız gerekiyor.

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

Lütfen aşağıdaki formülü boş bir hücreye kopyalayın veya girin ve ilk sonucu almak için "Ctrl" + "Shift" + "Enter" tuşlarına aynı anda basın. Daha sonra, formülü ihtiyaç duyduğunuz diğer hücrelere kopyalamak için doldurma tutamacını aşağı doğru sürükleyin.

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

Sonuç:

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

3,7.2 Bir sütunda veya birden fazla sütunda eşleşen tüm değerleri VLOOKUP ile bulun ve toplayın

Belirli ayların toplam değerini aşağıdaki ekran görüntüsünde gösterildiği gibi hesaplamak istiyorsanız, standart VLOOKUP işlevi yeterli olmayabilir. Böyle bir durumda, SUM, INDEX ve MATCH işlevlerini bir araya getirerek özel bir formül oluşturmanız gerekir.

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

Aşağıdaki formülü boş bir hücreye girin, ardından doldurma tutamacını aşağı doğru sürükleyerek bu formülü diğer hücrelere kopyalayın.

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

Sonuç:

Şimdi, belirli aydaki bir sütuna göre ilk eşleşen değerler birlikte toplanmıştır, ekran görüntüsüne bakın:

3,7.3 İlk eşleşen veya tüm eşleşen değerleri Kutools for Excel ile VLOOKUP ile bulun ve toplayın

Yukarıdaki formülleri hatırlamak sizin için zor olabilir. Bu durumda, “Kutools for Excel”in güçlü özelliklerinden biri olan “Ara ve Topla” özelliğini öneririm. Bu sayede satır veya sütunlarda ilk eşleşen ya da tüm eşleşen değerleri VLOOKUP ile en kolay şekilde bulup toplayabilirsiniz.

1. Bu özelliği etkinleştirmek için **Kutools** > **Süper ARA** > **Ara ve Topla** seçeneğine tıklayın.
2. Ardından, iletişim kutusundan ihtiyacınıza uygun işlemleri belirtin.

3,7.4 Hem satırlarda hem de sütunlarda eşleşen tüm değerleri VLOOKUP ile bulun ve toplayın

Hem sütun hem de satırı eşleştirmeniz gereken durumlarda değerleri toplamak istiyorsanız—örneğin, aşağıdaki ekran görüntüsünde gösterildiği gibi Mart ayında “Kazak” ürününün toplam değerini almak gibi—

Bu görevi gerçekleştirmek için burada SUMPRODUCT işlevini kullanabilirsiniz.

Lütfen 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 İki tabloyu Anahtar Sütun temel alarak birleştirmek için VLOOKUP

Günlük工作中, veri analizi yaparken tüm gerekli bilgileri bir veya daha fazla anahtar sütuna göre tek bir tabloda toplamanız gerekebilir. Bu görevi yerine getirmek için VLOOKUP işlevi yerine INDEX ve MATCH işlevlerini tercih edebilirsiniz.

3,8.1 İki tabloyu tek bir Anahtar Sütun temel alarak birleştirmek için VLOOKUP

Örneğin, iki tablonuz var: birincisi ürünler ve isimler verisini, ikincisi ise ürünler ve siparişler verisini içeriyor. Şimdi bu iki tabloyu, ortak ürün sütununu eşleştirerek tek bir tabloda birleştirmek istiyorsunuz.

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

Lütfen aşağıdaki formülü boş bir hücreye uygulayın ve ardından bu formülü istediğiniz hücrelere kadar doldurma tutamacını aşağı doğru sürükleyin.

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

Sonuç:

Şimdi, sipariş sütunu Anahtar Sütun verisine göre ilk tabloya eklenerek birleştirilmiş bir tablo elde edeceksiniz.

3,8.2 İki tabloyu birden fazla Anahtar Sütun temel alarak birleştirmek için VLOOKUP

Birleştirmek istediğiniz iki tabloda birden fazla Anahtar Sütun varsa ve bu ortak sütunlara göre tabloları birleştirmek istiyorsanız, lütfen aşağıdaki adımları izleyin.

Genel formül şöyledir:

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

Lütfen sonucu yerleştirmek istediğiniz boş bir hücreye aşağıdaki formülü uygulayın ve ardından 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ım 2: Formülü diğer hücrelere doldurun

Ardından ilk formül hücresini seçin ve bu formülü ihtiyaç duyduğunuz kadar diğer hücrelere kopyalamak için doldurma tutamacını sürükleyin:

3,9 Birden fazla çalışma sayfasında VLOOKUP ile eşleşen değerleri getirme

Excel'de birden fazla çalışma sayfası arasında VLOOKUP yapmanız mı gerekiyor? Örneğin, içinde Aralık verileri olan üç çalışma sayfanız varsa ve bu sayfalardan belirli değerlere göre veri çekmek istiyorsanız, bu görevi başarıyla tamamlamak için adım adım öğreticimizi takip edebilirsiniz:Birden Fazla Çalışma Sayfası Arasında VLOOKUP Değerleri.

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

Eşleşen değerleri ararken orijinal Hücre Biçimi gibi Yazı Tipi Rengi, Arka Plan Rengi, veri biçimi vb. korunmaz. Hücre veya veri biçimlendirmesini korumak için bu bölüm, bu işleri çözmek için bazı püf noktaları sunar.

4,1 VLOOKUP ile eşleşen değeri alın ve hücre rengi ile yazı tipi biçimlendirmesini koruyun

Hepimizin bildiği gibi, normal VLOOKUP işlevi eşleşen değeri yalnızca başka bir veri aralığından getirebilir. Ancak bazen hem ilgili değeri hem de hücre biçimlendirmesini —örneğin doldurma rengi, yazı tipi rengi ve yazı tipi stili— almak isteyebilirsiniz. Bu bölümde, Excel’de eşleşen değerleri kaynak biçimlendirmesiyle birlikte nasıl getirebileceğinizi inceleyeceğiz.

Hücre biçimlendirmesiyle birlikte karşılık gelen değeri aramak ve döndürmek için lütfen aşağıdaki adımları uygulayın:

Adım 1: Kod 1'i Sayfa Kodu Modülüne kopyalayın

1. VLOOKUP yapmak istediğiniz verileri içeren çalışma sayfasının sekmesine sağ tıklayın ve bağlam menüsünden **"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 kodu 1: Arama değerinin yanı sıra hücre biçimlendirmesini de almak için VLOOKUP

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ım 2: 2 kodunu Modül penceresine kopyalayın

1. Hâlâ "Microsoft Visual Basic for Applications" penceresindeyken, "Ekle" > "Modül" seçeneğine tıklayıp aşağıdaki VBA kodunu açılan "Modül" penceresine kopyalayın.
2. VBA kodu 2: Arama değerinin yanı sıra hücre biçimlendirmesini de almak için VLOOKUP

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ım 3: VBAproject seçeneğini seçin

1. Yukarıdaki kodları ekledikten sonra, "Microsoft Visual Basic for Applications" penceresinde **Araçlar > Başvurular** seçeneğine tıklayın. Ardından açılan **"Başvurular – VBAProject"** iletişim kutusunda **"Microsoft Scripting Runtime"** onay kutusunu işaretleyin. Detaylı bilgi için ekran görüntülerine göz atın:
2. Ardından, iletişim kutusunu kapatmak için **Tamam** düğmesine tıklayın ve kod penceresini **Kaydet ve Kapat**.

Adım 4: 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, tüm sonuçları biçimlendirmeleriyle birlikte almak için doldurma tutamacını aşağı doğru sürükleyin. Ekran görüntüsüne göz atın:
   

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

   
   

4,2 VLOOKUP'tan Tarih formatı'ı Dönüş değeri koruyun

VLOOKUP işleviyle tarih formatı içeren bir değer arayıp döndürdüğünüzde sonuç sayı olarak görünebilir. Döndürülen sonucun tarih formatını korumak için VLOOKUP işlevini TEXT işlevi içine almalısınız.

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

Lütfen aşağıdaki formülü boş bir hücreye uygulayın, ardından formülü diğer hücrelere kopyalamak 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ğıda gösterilen ekran görüntüsündeki gibi döndürüldü:

4,3 VLOOKUP'tan Yorum döndürme

Excel’de VLOOKUP kullanarak eşleşen hücre verisini ve ilişkili açıklamayı (aşağıdaki ekran görüntüsünde gösterildiği gibi) almanız mı gerekiyor? Öyleyse, aşağıda sunulan Kullanıcı Tanımlı İşlev bu görevi kolayca yerine getirmenize yardımcı olabilir.

Adım 1: Kodu bir Modüle kopyalayın

1. "ALT" + "F11" tuşlarına basarak 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: Eşleşen değeri Yorum ile birlikte döndüren VLOOKUP:

   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. Ardından kod penceresini kaydedip kapatın.

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

1. Şimdi aşağıdaki formülü girin ve diğer hücrelere kopyalamak için doldurma tutamacını sürükleyin. Bu işlem, hem eşleşen değerleri hem de yorumları aynı anda getirecektir—ekran görüntüsüne göz atın:
   

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

   
   

4,4 Metin olarak saklanan sayılar için VLOOKUP

Örneğin, orijinal tabloda kimlik numarası sayı formatındayken arama hücrelerinde metin olarak saklanıyorsa, standart VLOOKUP işlevi kullandığınızda #YOK hatası alabilirsiniz. Bu durumda doğru sonuçları elde etmek için VLOOKUP işlevini TEXT ve DEĞER işlevleriyle birlikte kullanabilirsiniz. Bunu gerçekleştirmek için aşağıdaki formülü uygulayabilirsiniz:

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

Lütfen aşağıdaki formülü boş bir hücreye girin ve ardından formülü kopyalamak için doldurma tutamacını aşağı doğru sürükleyin.

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

Sonuç:

Artık aşağıda gösterilen ekran görüntüsündeki gibi doğru sonuçları alacaksınız: