DÜŞEYARA (İngilizce VLOOKUP) formülü =DÜŞEYARA(F2;A2:D6;3;YANLIŞ), F2'deki değeri A2:D6'nın ilk sütununda arar ve aynı satırın üçüncü sütunundaki değeri döndürür. Sondaki YANLIŞ "yalnızca tam eşleşme" demektir. F2'de başka bir ürün seçin, fiyat değişir.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
=DÜŞEYARA(F2;A2:D6;3;YANLIŞ)A2:D6 tablosunu çerçeveli görmek için G2'ye tıklayın. Formüldeki 3'ü 2 yapın, G2 fiyat yerine kategoriyi döndürür, çünkü Category tablonun ikinci sütunudur. Eşleşme harf büyüklüğünü yok sayar: pear Pear'ı bulur.
DÜŞEYARA söz dizimi
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Bağımsız değişken | Nedir | Örnekte |
|---|---|---|
lookup_value (aranan_değer) | Aranacak değer. | F2 (Pear) |
table_array (tablo_dizisi) | Aramanın yapılacağı tablo. DÜŞEYARA yalnızca onun ilk sütununda arar. | A2:D6 |
col_index_num (sütun_indis_sayısı) | Tablonun hangi sütununun döndürüleceği; tablonun ilk sütunundan (1) itibaren sayılır. | 3 (Price) |
range_lookup (aralık_bak) | Tam eşleşme için YANLIŞ veya 0. Yaklaşık eşleşme için DOĞRU, 1 ya da hiçbir şey. | YANLIŞ |
Sütun numarası sayfanın A sütunundan değil, tablonun başından sayılır. C sütununda başlayan bir tabloda col_index_num 2, D sütunu demektir. Tablonun genişliğinden büyük bir sayı #BAŞV! (İngilizce #REF!), 0 ise #DEĞER! (İngilizce #VALUE!) döndürür; tablo hata adlarını İngilizce gösterir.
Türkçe Excel ondalık virgül kullandığı için bağımsız değişkenler noktalı virgülle ayrılır: =DÜŞEYARA(F2;A2:D6;3;YANLIŞ). Tablodaki formülleri de bu şekilde, Türkçe adlarla ve noktalı virgülle yazabilirsiniz.
Döndürülecek sütunu KAÇINCI ile seçme
Elle yazılmış bir 3, biri tablonun içine bir sütun eklediğinde sessizce bozulur: formül artık başka bir şey tutan üçüncü sütunu döndürmeye devam eder. Bunun yerine sütun numarasını başlıktan KAÇINCI'ya (İngilizce MATCH) buldurun. Burada G1 bir açılır listedir: Stock veya Category'yi seçin, G2 onu izler.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Carrot | 0.8 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
=DÜŞEYARA(F2;A2:D6;KAÇINCI(G1;A1:D1;0);YANLIŞ)KAÇINCI(G1;A1:D1;0) başlık satırında "Price"ın konumunu, 3'ü döndürür ve DÜŞEYARA bunu sütun numarası olarak kullanır: Carrot için 0.8. Bu iki yönlü bir aramadır: ürüne göre seçilen bir satır, başlığa göre seçilen bir sütun. Aynı fikrin DÜŞEYARA yerine İNDİS ile yazılmış hâli İNDİS ve KAÇINCI sayfasında.
Yaklaşık eşleşme: DOĞRU ile DÜŞEYARA
Son bağımsız değişken DOĞRU olduğunda DÜŞEYARA eşit bir değer aramaz. Aranan değerden küçük veya ona eşit en büyük değeri bulur. Dilimler için istenen budur: vergi dilimleri, notlar, kargo ücretleri, prim kademeleri. İlk sütun küçükten büyüğe sıralı olmalıdır.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 0 | 0% | Ana | 750 | 0% | |
| 3 | 1000 | 3% | Ben | 4,200 | 3% | |
| 4 | 5000 | 5% | Cara | 5,000 | 5% | |
| 5 | 10000 | 8% | Dev | 12,500 | 8% |
=DÜŞEYARA(E2;$A$2:$B$5;2;DOĞRU)Ben'in 4,200 tutarındaki satışı A sütununda yok. Onu aşmayan en büyük değer 1.000'dir, bu yüzden 3% alır. Cara'nın 5,000 satışı 5.000 satırıyla tam eşleşir ve 5% alır. Dev'in 12,500 satışı son dilimin üzerindedir ve son oranı, 8%'i alır. İlk dilimin altındaki bir değer (burada negatif bir satış rakamı) #YOK (İngilizce #N/A) döndürür; tablonun 0'dan başlamasının nedeni budur.
$A$2:$B$5 içindeki $ işaretleri, F2 F5'e kadar aşağı doldurulduğunda tabloyu yerinde tutar. Onlar olmadan F3, A3:B6'da arar ve ilk dilimi atlar.
Dördüncü bağımsız değişkeni yazmamak DOĞRU ile aynıdır. Sıralanmamış bir ürün listesinde bu sessiz bir hatadır: Excel liste sıralıymış gibi arar ve yanlış satırdan bir fiyat ya da listede olan bir değer için #YOK döndürebilir. Ad, kod veya kimlik ararken her zaman YANLIŞ ile bitirin.
DÜŞEYARA neden #YOK döndürür
#YOK "bulunamadı" demektir. Aşağıdaki tablo üç sık nedeni gösteriyor; G sütunu her aramayı EĞERYOKSA ve KIRP ile sarmalanmış olarak tekrarlıyor.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | Fixed |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | #N/A | Not found |
| 3 | Pear | Fruit | $1.50 | 25 | Milk | #N/A | $1.10 |
| 4 | Carrot | Vegetable | $0.80 | 60 | Fruit | #N/A | Not found |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A Aradığın değer arama aralığında yok.Türkçe Excel’de: =DÜŞEYARA(E2;$A$2:$D$6;3;YANLIŞ)- Değer tabloda yok. Kiwi A2:A6'da yok. Bu gerçek bir "bulunamadı" durumudur ve
EĞERYOKSA(...;"Not found")onu okunabilir bir metne çevirir. E2'yi Apple yapın, iki sütun da fiyatı gösterir. - Fazladan boşluklar. E3 sonunda boşluk olan
"Milk "değerini içerir, bu yüzdenMilkile eşit değildir.KIRP(E3)boşluğu kaldırır ve G3 fiyatı bulur. Boşluklar tabloda ise A sütununu her aramada değil, bir kez KIRP ile temizleyin. - Değer başka bir sütunda. Fruit var, ama B sütununda. DÜŞEYARA yalnızca tablonun ilk sütununda arar, bu yüzden E4 iki sütunda da başarısız olur. Tabloyu aradığınız sütundan başlatın ya da arama sütununu ve döndürme sütununu ayrı ayrı alan ÇAPRAZARA'yı kullanın.
Bir aramanın etrafında EĞERHATA yerine EĞERYOKSA kullanın. EĞERYOKSA yalnızca #YOK hatasını yakalar, böylece yanlış bir sütun numarasından gelen #BAŞV! "Not found" olarak gizlenmek yerine görünmeye devam eder.
İki neden daha:
- Metin olarak saklanan sayılar. A sütunu metin olarak yazılmış ürün kodları içeriyorsa (çoğu zaman içe aktarmadan sonra, köşede küçük yeşil bir üçgenle) ve F2'de 101 sayısı varsa, 101 listede görünse bile
=DÜŞEYARA(F2;A2:B6;2;YANLIŞ)#YOKdöndürür. Taraflardan birini dönüştürün:=DÜŞEYARA(F2&"";A2:B6;2;YANLIŞ)"101" metnini arar, F2 metinse=DÜŞEYARA(SAYIYAÇEVİR(F2);A2:B6;2;YANLIŞ)bir sayı arar. - Sıralanmamış verilerde yaklaşık eşleşme, yukarıdaki bölümde anlatıldığı gibi.
DÜŞEYARA boş yerine 0 döndürüyor
DÜŞEYARA'nın denk geldiği hücre boşsa Excel boş bir hücre değil, 0 gösterir. Stock sütunundaki bir 0, stok hiç girilmemişken "stokta yok" gibi okunur. Formüle &"" ekleyin ya da sonucun uzunluğunu sınayın:
=VLOOKUP(F2,A2:D6,4,FALSE)&""
=IF(LEN(VLOOKUP(F2,A2:D6,4,FALSE))=0,"",VLOOKUP(F2,A2:D6,4,FALSE))
Türkçe Excel'de bunlar =DÜŞEYARA(F2;A2:D6;4;YANLIŞ)&"" ve =EĞER(UZUNLUK(DÜŞEYARA(F2;A2:D6;4;YANLIŞ))=0;"";DÜŞEYARA(F2;A2:D6;4;YANLIŞ)) olur. İlki daha kısadır ama döndürdüğü her sayıyı metne çevirir, bu yüzden sonraki bir TOPLA onu atlar. İkincisi sayıları sayı olarak tutar.
Başka bir sayfadan DÜŞEYARA
Tablodan önce sayfa adını ve ! işaretini yazın. Formülü Excel'de oluştururken diğer sayfanın sekmesine tıklayıp aralığı seçin: Excel sizin için Prices!A2:B6 yazar. Burada Orders sekmesi fiyatları Prices sekmesinde arar.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Qty | Price | Total |
| 2 | 1001 | Pear | 3 | $1.50 | $4.50 |
| 3 | 1002 | Milk | 2 | $1.10 | $2.20 |
| 4 | 1003 | Apple | 5 | $1.20 | $6.00 |
| 5 | 1004 | Bread | 1 | $2.40 | $2.40 |
=DÜŞEYARA(B2;Prices!$A$2:$B$6;2;YANLIŞ)Prices sekmesini açın ve Apple'ın fiyatını değiştirin: sipariş toplamı güncellenir. İki ayrıntı:
- Boşluk içeren bir sayfa adı tek tırnak ister:
=DÜŞEYARA(B2;'Price list'!$A$2:$B$6;2;YANLIŞ). - Başka bir çalışma kitabındaki bir tabloya köşeli parantez içinde dosya adı eklenir,
[Prices.xlsx]Prices!$A$2:$B$6. O dosya kapalıyken Excel formülde dosyanın tam yolunu gösterir ve arama kaydedilmiş dosyadan çalışmaya devam eder.
Joker karakterlerle DÜŞEYARA (kısmi eşleşme)
YANLIŞ ile aranan değer joker karakterler içerebilir: * herhangi sayıda karakterin, ? tam olarak bir karakterin yerini tutar. "*"&E2&"*", adı E2'deki metni içeren ilk ürünü bulur.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
=DÜŞEYARA("*"&E2&"*";A2:C6;3;YANLIŞ)"coffee" hem Iced coffee hem de Coffee beans ile eşleşir; DÜŞEYARA yukarıdan ilkini, $2.90'ı döndürür. $8.50 için E2'yi bean yapın ya da juice deneyin. Gerçek bir yıldız veya soru işareti aramak için önüne tilde koyun: "~*".
Sola doğru DÜŞEYARA
DÜŞEYARA aradığı sütunun solundaki bir sütunu döndüremez: col_index_num yalnızca sağa doğru sayar ve negatif sayılar hatadır. Belirli bir fiyatın ürününü bulmak için C sütununda arayıp A sütununu ÇAPRAZARA (İngilizce XLOOKUP) ya da İNDİS ve KAÇINCI ile döndürün:
=XLOOKUP(2.4, C2:C6, A2:A6) Excel 2021 and Microsoft 365
=INDEX(A2:A6, MATCH(2.4, C2:C6, 0)) every version
Türkçe Excel'de: =ÇAPRAZARA(2,4;C2:C6;A2:A6) ve =İNDİS(A2:A6;KAÇINCI(2,4;C2:C6;0)). İkisi de ilk tablonun verileriyle Bread döndürür. Ayrıntılı açıklama ÇAPRAZARA sayfasında.
Alıştırma: ağırlığa göre kargo ücreti
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Cost | Weight (kg) | Cost | |
| 2 | 0 | $4.50 | 7 | ||
| 3 | 2 | $6.00 | |||
| 4 | 5 | $9.50 | |||
| 5 | 10 | $14.00 | |||
| 6 | 20 | $22.00 |
Sıra sende: Her ücret kendi ağırlığından listedeki bir sonraki ağırlığa kadar geçerlidir. E2 hücresinde DÜŞEYARA ile D2'deki paket ağırlığının kargo ücretini döndürün.
İki ölçütle DÜŞEYARA
DÜŞEYARA tek bir aranan değer alır. İki sütuna göre eşleştirmek için ikisini birleştiren bir yardımcı sütun oluşturun, onu tablonun başına koyun ve aynı birleştirilmiş metni arayın. Aşağıdaki A sütunu aşağı doldurulmuş =B2&"-"&C2 formülüdür, yani Coffee-Small, Coffee-Large gibi değerler içerir.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Product | Size | Price | Product | Size | Price |
| 2 | Coffee-Small | Coffee | Small | $2.50 | Tea | Large | |
| 3 | Coffee-Large | Coffee | Large | $3.50 | |||
| 4 | Tea-Small | Tea | Small | $2.00 | |||
| 5 | Tea-Large | Tea | Large | $3.00 | |||
| 6 | Juice-Small | Juice | Small | $3.00 |
Sıra sende: A sütunu ürünü ve boyutu bir kısa çizgiyle birleştirir. G2 hücresinde E2'deki ürünün ve F2'deki boyutun fiyatını döndürün.
Ayırıcı önemlidir: "Tea"&"Large" TeaLarge verir ve bu, A sütunundaki hiçbir şeyle eşleşmez. Excel 2021 ve Microsoft 365'te yardımcı sütunu =ÇAPRAZARA(1;(B2:B6=E2)*(C2:C6=F2);D2:D6) ile atlayabilirsiniz; birden fazla ölçütle arama sayfası bunu ve İNDİS/KAÇINCI sürümünü gösteriyor.
Sıkça Sorulan Sorular
Excel'de DÜŞEYARA nasıl yapılır?
=DÜŞEYARA( yazın ve dört bağımsız değişken verin: aranacak değer, tablo (ilk sütunu bu değeri içermelidir), döndürülecek sütunun numarası ve tam eşleşme için YANLIŞ. =DÜŞEYARA("Pear";A2:D6;3;YANLIŞ) Pear'ı A sütununda bulur ve o satırın C sütunundaki değeri döndürür.
DÜŞEYARA'nın sonundaki DOĞRU veya YANLIŞ ne anlama gelir?
YANLIŞ (veya 0) tam eşleşme ister ve değer yoksa #YOK döndürür. DOĞRU (veya 1 ya da bağımsız değişkeni hiç yazmamak) yaklaşık eşleşme ister: aranan değerden küçük veya ona eşit en büyük değer. Bu yalnızca ilk sütun artan sırada sıralıysa çalışır.
DÜŞEYARA formülüm neden #YOK döndürüyor?
Değer tablonun ilk sütununda bulunamadı. Olağan nedenler bir yazım hatası, fazladan bir boşluk ("Milk " ile "Milk" aynı değildir), yalnızca bir tarafta metin olarak saklanan bir sayı ya da başka bir sütunda duran bir değerdir. Kendi metninizi göstermek için formülü EĞERYOKSA ile sarmalayın: =EĞERYOKSA(DÜŞEYARA(F2;A2:D6;3;YANLIŞ);"Not found").
DÜŞEYARA sola bakabilir mi?
Hayır. DÜŞEYARA yalnızca tablonun ilk sütununun sağındaki sütunları döndürür. Excel 2021 veya Microsoft 365'te =ÇAPRAZARA(F2;C2:C6;A2:A6), her sürümde =İNDİS(A2:A6;KAÇINCI(F2;C2:C6;0)) kullanın.
Başka bir sayfadan DÜŞEYARA nasıl yapılır?
Aralığın önüne sayfa adını ve bir ünlem işareti yazın: =DÜŞEYARA(B2;Prices!$A$2:$B$6;2;YANLIŞ). Sayfa adında boşluk varsa adı tek tırnak içine alın: 'Price list'!$A$2:$B$6.
