Excel INDEX MATCH: Temel ve gelişmiş aramalar

Excel'de, belirli verileri doğru şekilde almak çoğu zaman sıkça karşılaşılan bir ihtiyaçtır. INDEX ve MATCH fonksiyonlarının her biri kendi başına güçlü özellikler sunsa da, birlikte kullanıldıklarında veri arama işlemleri için oldukça etkili bir araç seti oluştururlar. Bu kombinasyon, temel yatay ve dikey aramalardan iki yönlü, büyük/küçük harf duyarlı ve çoklu kriterli aramalara kadar gelişmiş arama yetenekleri sunar. VLOOKUP fonksiyonuna kıyasla daha fazla esneklik sağlayan INDEX ve MATCH birlikte kullanıldığında, çok daha geniş kapsamlı veri arama seçenekleri elde edilir. Bu eğitimde, bu iki fonksiyonun birlikte neler başarabileceğine detaylıca göz atalım.

• INDEX fonksiyonu
• MATCH fonksiyonu
• INDEX ve MATCH'i birleştirme

• İki yönlü arama
• Sola doğru arama
• Büyük/küçük harf duyarlı arama
• En yakın eşleşme
• Çoklu kriterli arama
• Birden çok sütunda arama
• İlk boş olmayan değeri bulma
• İlk sayısal değeri bulma
• Maksimum veya minimum ile ilişkili değeri bulma
• İpucu: Kendi #YOK hata mesajınızı özelleştirin

Excel'de INDEX ve MATCH nasıl kullanılır

INDEX ve MATCH fonksiyonlarını kullanmadan önce, bu fonksiyonların değer aramalarında bize nasıl yardımcı olabileceğini anlamamız gerekir.

Excel'de INDEX fonksiyonu nasıl kullanılır

Excel'deki INDEX fonksiyonu, belirli bir aralıktaki verilen konumda bulunan değeri döndürür. INDEX fonksiyonunun söz dizimi aşağıdaki gibidir:

• array (zorunlu), değeri almak istediğiniz aralığı belirtir.
• row_num (zorunlu, column_num girilmediyse) aralıktaki satır numarasını belirtir.
• column_num (isteğe bağlı, ancak row_num girilmediyse zorunlu) aralıktaki sütun numarasını belirtir.

Örneğin, listedeki6. öğrenci olan Jeff'in notunu öğrenmek için INDEX fonksiyonunu şu şekilde kullanabilirsiniz:

=INDEX(C2:C11,6)

√ Not: C2:C11 aralığı notların bulunduğu yerdir ve6 sayısı,6. öğrencinin sınav notunu bulur.

Şimdi küçük bir test yapalım. =INDEX(A1:C1,2) formülünde hangi değer döner? --- Evet, verilen satırdaki 2. değer olan Birth date dönecektir.

Artık INDEX fonksiyonunun yatay veya dikey aralıklarla mükemmel şekilde çalıştığını biliyoruz. Ancak, birden fazla satır ve sütundan oluşan daha büyük bir aralıkta değer döndürmek istersek ne olacak? Bu durumda hem satır hem de sütun numarası kullanmalıyız. Örneğin, Jeff'in notunu tek bir sütun yerine tablo aralığında bulmak için, A2'den C11'e kadar olan hücrelerde satır numarası6 ve sütun numarası3 ile şu şekilde bulabiliriz:

=INDEX(A2:C11,6,3)

Ancak, çok satırlı ve sütunlu büyük bir veri tabanında, tam satır ve sütun numarasıyla formül uygulamak pratik olmayabilir. İşte bu noktada MATCH fonksiyonunu da kullanmamız gerekir.

Excel'de MATCH fonksiyonu nasıl kullanılır

Excel'deki MATCH fonksiyonu, belirli bir öğenin verilen aralıktaki konumunu sayısal olarak döndürür. MATCH fonksiyonunun söz dizimi şöyledir:

• lookup_value (zorunlu), lookup_array içinde eşleştirilecek değeri belirtir.
• lookup_array (zorunlu), MATCH'in arama yapacağı hücre aralığını belirtir.
• match_type (isteğe bağlı): 1, 0 veya -1.
  • 1(varsayılan), MATCH, lookup_value değerine eşit veya daha küçük olan en büyük değeri bulur. lookup_array içindeki değerler artan sırada olmalıdır.
  • 0, MATCH, lookup_value ile tam olarak eşleşen ilk değeri bulur. lookup_array içindeki değerler herhangi bir sırada olabilir. (Eşleşme türü0 olarak ayarlandığında joker karakterler kullanılabilir.)
  • -1, MATCH, lookup_value değerine eşit veya daha büyük olan en küçük değeri bulur. lookup_array içindeki değerler azalan sırada olmalıdır.

Örneğin, isimler listesindeki Vera'nın konumunu öğrenmek için MATCH formülünü şu şekilde kullanabilirsiniz:

=MATCH("Vera",A2:A11,0)

√ Not: Sonuç “4”, “Vera” isminin listedeki4. konumda olduğunu gösterir.

Artık Excel'deki INDEX ve MATCH fonksiyonlarının temel kullanımını biliyoruz, şimdi bu iki fonksiyonu birleştirmeye hazırız.

Excel'de INDEX ve MATCH nasıl birleştirilir

Aşağıdaki örneğe bakarak INDEX ve MATCH fonksiyonlarını nasıl birleştirebileceğimizi görebilirsiniz:

Evelyn’in notunu bulmak için, sınav notlarının 3. sütunda olduğunu bildiğimizden, MATCH fonksiyonunu kullanarak satır konumunu otomatik olarak belirleyebiliriz; böylece manuel olarak saymamıza gerek kalmaz. Ardından, INDEX fonksiyonu ile belirlenen satır ve 3. sütunun kesişimindeki değeri alabiliriz:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Formül biraz karmaşık görünebilir, bu yüzden her bir bölümünü adım adım inceleyelim.

INDEX formülünde üç argüman bulunur:

• row_num: MATCH("Evelyn",A2:A11,0)Bu kısım, INDEX'e "Evelyn" değerinin A2:A11 aralığındaki satır konumunu sağlar, bu da5.
• column_num:3, INDEX'in dizideki notu bulacağı 3. sütunu belirtir.
• array: A2:C11, INDEX'in belirtilen satır ve sütunun kesişimindeki değeri döndürmesini sağlar; bu aralık A2'den C11'e kadar uzanır. Sonuç olarak90 elde edilir.

Yukarıdaki formülde sabit bir değer olan "Evelyn" kullandık. Ancak pratikte, sabit değerler kullanmak uygun değildir; çünkü farklı bir öğrencinin notunu aramak istediğimizde formülü değiştirmemiz gerekir. Bu gibi durumlarda, hücre başvuruları kullanarak dinamik formüller oluşturabiliriz. Örneğin, burada "Evelyn" yerine F2 hücresini kullanacağım:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

INDEX ve MATCH formül örnekleri

Bu bölümde, INDEX ve MATCH fonksiyonlarının farklı ihtiyaçlara yönelik nasıl kullanılabileceğini ele alacağız.

INDEX ve MATCH ile iki yönlü arama uygulama

Önceki örnekte, sütun numarasını biliyor ve satır numarasını bulmak için MATCH formülünü kullanıyorduk. Peki ya sütun numarasından da emin değilsek?

Bu gibi durumlarda, iki MATCH fonksiyonu kullanarak iki yönlü (matris) arama yapabiliriz: biri satır numarasını, diğeri sütun numarasını bulur. Örneğin, Evelyn’in notunu öğrenmek için şu formülü kullanmalıyız:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX ve MATCH ile sola doğru arama uygulama

Şimdi, Evelyn'in sınıfını belirlemeniz gereken bir senaryoyu düşünelim. Sınıf sütununun, isim sütununun solunda yer aldığını fark etmiş olabilirsiniz; bu durum, Excel'in güçlü arama fonksiyonlarından biri olan VLOOKUP'ın yeteneklerini aşar.

Aslında, sola doğru arama yapabilmek, INDEX ve MATCH kombinasyonunun VLOOKUP'a göre öne çıkan avantajlarından biridir.

Evelyn’in sınıfını bulmak için, Evelyn’i B2:B11 aralığında arayıp, karşılık gelen değeri A2:A11 aralığından almak üzere aşağıdaki formülü kullanın.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX ve MATCH ile büyük/küçük harf duyarlı arama uygulama

MATCH fonksiyonu varsayılan olarak büyük/küçük harf duyarlı değildir. Ancak, formülünüzün harflerin büyük/küçük olmasına göre ayrım yapmasını istiyorsanız, EXACT fonksiyonunu ekleyebilirsiniz. MATCH fonksiyonunu EXACT ile birleştirerek INDEX formülünde büyük/küçük harf duyarlı arama yapabilirsiniz, aşağıda gösterildiği gibi:

• array, değeri almak istediğiniz aralığı belirtir.
• lookup_value, karakterlerin büyük/küçüklüğünü dikkate alarak, lookup_array içinde eşleştirilecek değeri belirtir.
• lookup_array, MATCH'in lookup_value ile karşılaştırma yapacağı hücre aralığını belirtir.

Örneğin, JIMMY’nin sınav notunu öğrenmek için aşağıdaki formülü kullanın:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Not: Bu, Ctrl + Shift + Enter ile girilmesi gereken bir dizi formüldür; yalnızca Excel365, Excel2021 ve daha yeni sürümlerde Enter ile girilebilir.

INDEX ve MATCH ile en yakın eşleşmeyi bulma

Excel'de, bir veri kümesinde belirli bir değere en yakın eşleşmeyi bulmanız gereken durumlarla karşılaşabilirsiniz. Böyle durumlarda, INDEX ve MATCH fonksiyonlarının yanı sıra ABS ve MIN fonksiyonlarını birlikte kullanmak oldukça faydalı olabilir.

• array, değeri almak istediğiniz aralığı belirtir.
• lookup_array, lookup_value değerine en yakın eşleşmeyi bulmak istediğiniz değerler aralığını belirtir.
• lookup_value, en yakın eşleşmesini bulmak istediğiniz değeri belirtir.

Örneğin, notu85'e en yakın olan öğrenciyi bulmak için, C2:C11 aralığında85'e en yakın notu arayıp, karşılık gelen değeri A2:A11 aralığından almak için aşağıdaki formülü kullanın.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Not: Bu, Ctrl + Shift + Enter ile girilmesi gereken bir dizi formüldür; yalnızca Excel365, Excel2021 ve daha yeni sürümlerde Enter ile girilebilir.

INDEX ve MATCH ile çoklu kriterli arama uygulama

Birden fazla koşulu karşılayan bir değeri bulmak için, iki veya daha fazla sütunda arama yapmanız gerekiyorsa aşağıdaki formülü kullanın. Bu formül, farklı sütunlarda çeşitli koşulları belirterek çoklu kriterli arama yapmanıza ve tüm kriterleri karşılayan istediğiniz değeri bulmanıza olanak tanır.

√ Not: Bu, Ctrl + Shift + Enter ile girilmesi gereken bir dizi formüldür. Formül çubuğunda bir çift süslü parantez göreceksiniz.

• array, değeri almak istediğiniz aralığı belirtir.
• (lookup_value=lookup_array), tek bir koşulu temsil eder. Bu koşul, belirli bir lookup_value'nun lookup_array içindeki değerlerle eşleşip eşleşmediğini kontrol eder.

Örneğin, doğum tarihi7/2/2008 olan Sınıf A'dan Coco'nun notunu bulmak için aşağıdaki formülü kullanabilirsiniz:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX ve MATCH ile birden çok sütunda arama uygulama

Birden fazla veri sütunu ile çalıştığınız bir senaryoyu düşünün. İlk sütun, diğer sütunlardaki verileri sınıflandırmak için anahtar görevi görür. Belirli bir kaydın hangi kategoriye ait olduğunu belirlemek için, veri sütunlarında arama yapıp, referans sütunundaki ilgili anahtarla eşleştirmeniz gerekir.

Örneğin, aşağıdaki tabloda, öğrenci Shawn'ı INDEX ve MATCH ile ilgili sınıfıyla nasıl eşleştirebiliriz? Bunu bir formülle yapmak mümkün, ancak formül oldukça uzun ve hem anlaması hem de yazması zordur.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

İşte tam bu noktada Kutools for Excel'in Birden çok sütunu eşleştir ve bul özelliği devreye giriyor. Bu özellik, belirli kayıtları ilgili kategorileriyle hızlı ve kolay şekilde eşleştirmenizi sağlar. Bu güçlü aracı kullanmak ve Shawn'ı sınıfıyla zahmetsizce eşleştirmek için, Kutools for Excel eklentisini indirip yükleyin ve aşağıdaki adımları izleyin:

1. Eşleşen sınıfı göstermek istediğiniz hedef hücreyi seçin.
2. Kutools sekmesinde, Formül Yardımcısı > Arama & Referans > Birden çok sütunu eşleştir ve bul seçeneğine tıklayın.
3. 
3. Açılan iletişim kutusunda şu adımları izleyin:
   1. 1. butona tıklayın yanındaki düğmeye tıklayın Arama sütunu döndürmek istediğiniz anahtar bilgileri içeren sütunu seçin, yani sınıf isimleri. (Burada yalnızca tek bir sütun seçebilirsiniz.)
   2. 2. butona tıklayın yanındaki düğmeye tıklayın Referans Tablosu seçilen Arama sütunundaki değerlerle eşleşecek hücreleri seçin. Arama sütunu, yani öğrenci isimleri.
   3. 3. butona tıklayın yanındaki düğmeye tıklayın Arama değeri eşleştirmek istediğiniz öğrenci adını içeren hücreyi seçin; bu örnekte Shawn.
   4. Tamam'a tıklayın.
   5. 

Sonuç

Kutools otomatik olarak formülü oluşturdu ve Shawn'ın sınıf adı hemen hedef hücrede görüntülenecek.

INDEX ve MATCH ile ilk boş olmayan değeri bulma

Bir sütun veya satırdan, hataları göz ardı ederek ilk boş olmayan değeri almak için INDEX ve MATCH fonksiyonlarına dayalı bir formül kullanabilirsiniz. Ancak, aralıktaki hataları göz ardı etmek istemiyorsanız, ISBLANK fonksiyonunu ekleyin.

• Bir sütun veya satırda hataları göz ardı ederek ilk boş olmayan değeri alın:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Bir sütun veya satırda hatalar dahil ilk boş olmayan değeri alın:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX ve MATCH ile ilk sayısal değeri bulma

Bir sütun veya satırdan ilk sayısal değeri almak için, INDEX, MATCH ve ISNUMBER fonksiyonlarına dayalı formülü kullanın.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX ve MATCH ile maksimum veya minimum ile ilişkili değeri bulma

Bir aralıktaki maksimum veya minimum değerle ilişkili bir değeri almak istiyorsanız, MAX veya MIN fonksiyonunu INDEX ve MATCH fonksiyonlarıyla birlikte kullanabilirsiniz.

• INDEX ve MATCH ile maksimum değerle ilişkili değeri almak için:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array,0))
• INDEX ve MATCH ile minimum değerle ilişkili değeri almak için:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array,0))
• Yukarıdaki formüllerde iki argüman bulunur:
• • array, ilişkili bilgiyi almak istediğiniz aralığı belirtir.
  • lookup_array, maksimum veya minimum değer gibi belirli kriterler için incelenecek değerler kümesini temsil eder.

Örneğin, en yüksek nota sahip olan kişiyi belirlemek için aşağıdaki formülü kullanın:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

İpucu: Kendi #YOK hata mesajınızı özelleştirin

Excel'in INDEX ve MATCH fonksiyonlarıyla çalışırken, eşleşen bir sonuç bulunamadığında #YOK hatasıyla karşılaşabilirsiniz. Örneğin, aşağıdaki tabloda Samantha adında bir öğrencinin notunu bulmaya çalıştığınızda, veri kümesinde olmadığı için #YOK hatası görünecektir.

Çalışma sayfalarınızı daha kullanıcı dostu hale getirmek için, INDEX MATCH formülünüzü IFNA fonksiyonu ile sarmalayarak bu hata mesajını özelleştirebilirsiniz:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Yukarıda, Excel'deki INDEX ve MATCH fonksiyonlarıyla ilgili tüm önemli içerikleri bulabilirsiniz. Umarım bu eğitim sizin için faydalı olmuştur. Daha fazla Excel ipucu ve püf noktası keşfetmek isterseniz, binlerce eğitime ulaşmak için buraya tıklayın.