Index Match Excel: Verileri Aramanın Daha İyi Bir Yolu

INDEX MATCH, iki Excel işlevi olan INDEX() ve MATCH()'in birleşimidir ve yaygın olarak kullanılan VLOOKUP() işlevine bir alternatiftir.

Bu kılavuzda, INDEX() ve MATCH()'in ayrı ayrı ve birlikte nasıl çalıştığını, dikey ve yatay aramaların nasıl yapıldığını ve pratik örneklerle INDEX MATCH kullanımı için en iyi uygulamaları adım adım anlatacağım.

Excel'de INDEX MATCH Nedir?

INDEX MATCH, iki işlevin iç içe kullanılmasına kısaca atıfta bulunmanın bir yoludur. INDEX(MATCH()) fikrine eşdeğerdir. Her birinin ayrı ayrı ve birlikte nasıl çalıştığına bakalım.

Excel'de INDEX() işlevi

Excel'deki INDEX() işlevi, bir aralık veya tablodan bir değeri ya da bir değere başvuruyu döndürür. Bunu iki şekilde kullanabilirsiniz: dizi biçimi ve başvuru biçimi.

Dizi biçimi

Sabit bir tablo veya diziyle çalışırken dizi biçimini kullanabilirsiniz. Bu biçimde INDEX(), belirttiğiniz satır ve sütun numarasına göre bir değeri çeker. Sözdizimi şöyledir:

INDEX(array, row_num, [column_num])Altyapı:

Bu bağımsız değişkenleri anlayalım:

• array (zorunlu) üzerinde çalıştığınız değer aralığı veya dizisidir.

• row_num (zorunlu) değerin döndürüleceği satır numarasıdır.

• column_num (isteğe bağlı) değerin döndürüleceği sütun numarasıdır.

Excel'in değeri bulabilmesi için row_num veya column_num'dan en az birini belirtmeliyiz.

Ancak, bu sözdizimiyle çalışırken aklınızda tutmanız gereken bazı kısa notlar şunlardır:

• Dizide yalnızca bir satır veya sütun varsa, row_num veya column_num'u atlayabilirsiniz.

• Hem row_num hem de column_num dahil edilirse, INDEX kesiştikleri yerdeki değeri döndürür.

• row_num veya column_num'u 0 olarak ayarlarsanız, Excel tüm sütunu veya satırı döndürür (sonucu görmek için formülü dizi formülü olarak girmeniz gerekir).

Başvuru biçimi

Birden çok aralıkla çalışırken ve hangisine bakılacağını belirtmeniz gerektiğinde başvuru biçimini kullanabilirsiniz.

Sözdizimi şöyledir:

INDEX(reference, row_num, [column_num], [area_num])Altyapı:

Bu bağımsız değişkenleri anlayalım:

• reference (zorunlu) bir veya daha fazla hücre aralığına başvurmak için kullanılır.

• row_num (zorunlu) seçilen alandaki satır numarasıdır.

• column_num (isteğe bağlı) seçilen alandaki sütun numarasıdır.

• area_num (isteğe bağlı) birden fazla aralık varsa Excel'e hangisini kullanacağını söyler.

Aklınızda tutmanız gereken bazı ek noktalar şunlardır:

• Eğer area_num boş bırakılırsa, Excel varsayılan olarak ilk aralığı kullanır.

• Bu biçimde farklı sayfalardaki aralıkları karıştıramazsınız; hata oluşur.

• Hem satır hem de sütun atlanırsa, belirtilen alanın tamamını döndürür.

Excel'de MATCH() işlevi

MATCH() işlevi bir değerin bir satır veya sütundaki konumunu bulmanıza yardımcı olur. Değerin kendisini döndürmek yerine, listede nerede olduğunu söyler.

Sözdizimi şöyledir:

MATCH(lookup_value, lookup_array, [match_type])Altyapı:

Bu bağımsız değişkenleri anlayalım:

• lookup_value (zorunlu) bulmak istediğiniz değerdir. Bir sayı, metin, mantıksal değer (TRUE/FALSE) veya bir hücre başvurusu olabilir.

• lookup_array (zorunlu) Excel'in içinde arama yapacağı hücre aralığıdır.

• match_type (isteğe bağlı) Excel'in eşleşmeyi nasıl arayacağını tanımlar. Varsayılan 1'dir. Burada 1, lookup_value'dan küçük veya ona eşit en büyük değeri bulur (artan sırada olmalıdır). 0 tam eşleşmeyi bulur (herhangi bir sıra). -1 ise lookup_value'dan büyük veya ona eşit en küçük değeri bulur (azalan sırada olmalıdır).

MATCH işlevi büyük/küçük harf duyarlı değildir; yani büyük ve küçük harfleri aynı kabul eder. Metinle çalışıyor ve * (herhangi bir sayıda karakter) veya ? (tek bir karakter) gibi joker karakterleri kullanmak istiyorsanız, match_type'ı 0 olarak ayarladığınızdan emin olun. Çünkü joker karakter aramasını destekleyen tek mod budur.

INDEX() ve MATCH() Excel'de Birlikte Nasıl Çalışır?

MATCH()'in en yaygın kullanımlarından biri, onu INDEX() işleviyle eşleştirmektir. MATCH() bir değerin konumunu bulurken, INDEX() o konumdaki değeri getirir. 

MATCH()'i INDEX()'in içine yerleştirmek, zamanla sütunlar veya satırlar yer değiştirirken de sağlam kalan dinamik bir formül sağlar. 

Örneğin, örnek bir veri kümem var. Ve eğer A sütununda yer alan Banana ile aynı satırda B sütununda ne olduğunu bulmak istersem, INDEX MATCH'i şu şekilde birlikte kullanabilirim:

=INDEX(B2:B5, MATCH("Banana", A2:A5, 0))Altyapı:

Burada, MATCH("Banana", A2:A5, 0) A sütununda Banana'nın konumunu bulur ve INDEX(B2:B5, ...) B sütununda aynı satırdaki değeri döndürür.

INDEX MATCH vs. VLOOKUP(): Hangisini Kullanmalısınız?

INDEX MATCH, VLOOKUP()'ın yapamadığı işleri yapabilir. Ancak aralarında seçim yapmak, Excel sayfalarınızda ihtiyaç duyduğunuz esneklik ve performans düzeyine bağlıdır. Hadi karşılaştıralım.

Sütun esnekliği ve veri bütünlüğü

VLOOKUP()'ın en büyük dezavantajlarından biri, sabit sütun numaralarına bağımlı olmasıdır. Sütun ekler veya taşırsanız, formülünüz bozulabilir.

Şu örneği ele alalım:

A Sütunu ile B Sütunu arasına yeni bir sütun eklenirse, üçüncü sütun kayar ve aşağıdaki görselde gördüğünüz gibi formül yanlış sonuçlar verir.

=VLOOKUP("Apple", A2:C5, 2, FALSE)Altyapı:

INDEX MATCH, sabit sütun konumları yerine tanımlı aralıklarla çalışarak bu sorunu önler:

=INDEX(B2:B5, MATCH("Apple", A2:A5, 0))Altyapı:

Sütunlar yeniden düzenlense bile bu formül etkili kalır; bu da onu daha güvenilir ve bakımını daha kolay kılar. Burada, B sütunundan önce bir sütun eklenmiştir; formül otomatik olarak güncellenir ve sonuçlar doğru kalır. İşte INDEX MATCH'in sihri budur.

Arama yönü ve çok yönlülük

VLOOKUP() yalnızca soldan sağa arama yapabilir. Bu, arama sütununuzun aralığınızda her zaman ilk sütun olması gerektiği anlamına gelir. İhtiyacınız olan değer arama sütununun solundaysa, VLOOKUP() bunu bulamaz.

Bununla birlikte, meyve adına göre sağdaki rengini ararsak, bu formül çalışır. Örneğin, listemizden bir muzun rengini bulmak istersek, doğru sonucu verir: 

INDEX MATCH'te bu sınırlama yoktur. Her yönde arama yapabilirsiniz: sol, sağ, yukarı veya aşağı. Ayrıca verilerinizi nasıl organize ettiğiniz üzerinde size daha fazla kontrol sağlayan yatay aramaları da destekler. 

Bu örneğe INDEX MATCH uygulayalım ve hem sola hem de sağa aramaya nasıl izin verdiğini görelim.

Sola arama için aşağıdaki formülü kullanabilirsiniz ve doğru çıktıyı verir: 

=INDEX(A2:A5, MATCH(“yellow”, B2:B5,0))Altyapı:

Sağa arama için aşağıdaki formülü kullanabilirsiniz ve doğru çıktıyı verir: 

=INDEX(B2:B5, MATCH(“Banana”,A2:A5,0))Altyapı:

Excel'de performans ve verimlilik

VLOOKUP() bir eşleşme bulmak ve değeri tek seferde getirmek için tüm tabloyu tarar. Ancak INDEX MATCH görevi böler: MATCH() satırı veya sütunu bulur, INDEX() değeri getirir. 

Bu, özellikle karmaşık veya yüksek hacimli çalışma kitaplarında daha hızlı ve bellek açısından daha verimli olmasını sağlar.

Hangi işlevin hangi işlevselliği desteklediğine dair daha iyi bir fikir vermek için bir tablo:

INDEX MATCH ne zaman kullanılır

INDEX MATCH'i şu durumlarda kullanabilirsiniz:

• Hızın önemli olduğu büyük veri kümeleriyle çalışıyorsanız
• Modeliniz dinamikse ve sütunlar ekleniyor veya yeniden düzenleniyorsa
• Sola doğru veya yatay aralıklarda değer aramanız gerekiyorsa

Excel dosyalarınız küçük ve basitse, VLOOKUP() işi hâlâ görebilir. Ancak ileri düzey modelleme ve raporlama için INDEX MATCH daha güvenli bir seçenektir.

INDEX MATCH Nasıl Kullanılır: Adım Adım

Diyelim ki iki sütununuz var: Fruits ve Sales. “Apple” için satış değerini bulmak istiyorsunuz.

Bunun için birleşik INDEX MATCH formülünü yazın ve Enter tuşuna basın:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Altyapı:

Burada, MATCH("Apple", A2:A6, 0) “Apple”ın Fruits listesindeki konumunu bulur. Ve INDEX(B2:B6, ...) bu konumu kullanarak Sales sütunundan karşılık gelen satış değerini döndürür.

Formülün 12000 döndürdüğünü görüyorsunuz; bu Apple için satış değeridir. Yani sonucunuz bu.

INDEX MATCH Hatalarından Kaçınma

INDEX MATCH iyi bir ikili olsa da, doğru kurulmazsa yine de hatalar verebilir. İşte en yaygın sorunları nasıl gidereceğiniz ve formüllerinizi bozabilecek hatalardan nasıl kaçınacağınız.

#N/A hataları ve nasıl çözülür

#N/A hatası, Excel'in aradığınız değeri bulamadığı anlamına gelir. Bunun bazı yaygın nedenleri şunlar olabilir:

• Yazım hataları, fazladan boşluklar veya uyuşmayan metinler (ör. "apple" ve "Apple")

• Arama aralığında değer olmaması

• Yanlış match_type, örneğin tam eşleşme için 0 yerine 1

• Verilerde gizli karakterler (örneğin kesmeyen boşluklar)

Bunu düzeltmenin iki yolu vardır:

Önde veya sonda yer alan boşlukları temizlemek için TRIM() kullanın:

=MATCH(TRIM("Apple"), A2:A6, 0)Altyapı:

Hata yerine yedek bir değer göstermek için IFERROR() ekleyin:

=IFERROR(INDEX(B2:B6, MATCH("Apple", A2:A6, 0)), "Not found")Altyapı:

INDEX MATCH formüllerinde #REF! hataları

#REF! hatası genellikle bozuk bir başvuruya işaret eder—çoğu zaman şu nedenlerle:

• INDEX() ve MATCH() aralıklarınızın satır sayılarının uyuşmaması

• Formülünüzde kullanılan satır veya sütunların silinmesi

• Hücre başvurularını güncellemeden formülleri kopyalayıp yapıştırmak

Ama bunu düzeltmenin kolay yolları var: 

• INDEX() ve MATCH() aralıklarının aynı boyutta olduğundan emin olun:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Altyapı:

• Aralıkları mutlak başvurularla (ör. $A$2:$A$6) sabitleyerek kopyala-yapıştır sırasında formülleri kararlı tutun.

• Formüllerin bağlı olduğu hücreleri silmekten kaçının.

Yavaş performans ve büyük veri kümeleri

Binlerce satırla çalışıyorsanız, optimize edilmemiş INDEX MATCH formülleri Excel'i yavaşlatabilir.

Bunu şöyle optimize edebilirsiniz:

• Tüm sütunlara başvurmak yerine arama aralıklarını sınırlayın:

=INDEX(B2:B1000, MATCH("Apple", A2:A1000, 0))Altyapı:

• OFFSET(), INDIRECT() ve NOW() gibi uçucu işlevleri aramalarınıza yakın kullanmaktan kaçının.

• Düzeni korumak ve performansı iyileştirmek için Excel Tabloları veya adlandırılmış aralıklar kullanın.

Excel 365'te taşma hatalarını yönetme

Excel 365, sonuçları bitişik hücrelere “taşırabilen” dinamik dizileri tanıtır. Bu işlemde bir sorun olursa, bir #SPILL! hatası alırsınız.

Bazı yaygın nedenler şunlar olabilir:

• Bitişik hücreler boş değildir.
• Formül, engellenmiş veya birleştirilmiş bir hücre aralığına birden çok değer döndürüyor.
• Yalnızca tek bir değere ihtiyaç duyulan yerde dinamik bir dizi kullanıyorsunuz.

Ancak bu sorunlar çözülebilir. Şöyle üstesinden gelebilirsiniz:

• Formülün altındaki veya yanındaki hücreleri temizleyin.
• Taşma alanındaki birleştirilmiş hücreleri ayırın.
• Yalnızca tek bir değer istiyorsanız, @ ile tek sonuç zorlayın:

=@INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Altyapı:

Son Düşünceler

VLOOKUP()'a güveniyorsanız, artık daha istikrarlı bir şeye geçmenin zamanı geldi. Çalışma dosyanızın bir kopyasında INDEX MATCH kullanarak birkaç basit aramayla başlayabilirsiniz. Sütun değişikliklerinin hatalara yol açtığı yerlerde ya da veri yapınızın esneklik gerektirdiği durumlarda yeni formülü deneyin. Bu durumları ne kadar temiz bir şekilde ele aldığını gördüğünüzde geri dönmek zor olacaktır.