Excel’de INDEX MATCH: Temel ve gelişmiş aramalar

Excel’de belirli verileri doğru şekilde almak, sıklıkla karşılaşılan temel bir gerekliliktir. İNDEKS ve EŞLEŞTİR işlevlerinin her birinin kendine özgü güçlü yönleri bulunsa da, bu ikisinin bir araya getirilmesi veri aramaları için son derece etkili bir araç seti sunar. Birlikte kullanıldıklarında, temel yatay ve dikey aramaların ötesine geçerek iki yönlü aramalardan, büyük/küçük harfe duyarlı aramalara ve çoklu kriterlere dayalı aramalara kadar geniş bir yelpazede yetenek sağlarlar. VLOOKUP işlevine kıyasla daha gelişmiş olanaklar sunan İNDEKS ve EŞLEŞTİR kombinasyonu, veri arama seçeneklerinizi önemli ölçüde zenginleştirir. Bu eğitimde, bu güçlü ikilinin birlikte ne kadar çok şey başarabileceğinin derinliklerine inceleyelim.

• DİZİN işlevi
• EŞLEŞTİR işlevi
• DİZİN ve EŞLEŞTİR'i birleştirin

• Çift yönlü arama
• Sol yönlü arama
• Büyük/küçük harfe duyarlı arama
• En yakın eşleşme
• Birden çok kriterle arama
• Birden çok sütun arasında arama
• İlk boş olmayan değeri arama
• İlk sayısal değeri ara
• MAKS veya MİN ilişkileri için arama yapın
• İpucu: Kendi #YOK hata mesajlarınızı özelleştirin

Excel'de DİZİN ve EŞLEŞTİR nasıl kullanılır

İNDEKS ve EŞLEŞTİR işlevlerini kullanmadan önce, bu işlevlerin değer aramada bize nasıl yardımcı olabileceğini tam olarak anladığımızdan emin olalım.

Excel'de İNDEKS işlevini nasıl kullanılır

Excel'deki İNDEKS işlevi, belirtilen bir aralıkta verilen konumdaki değeri döndürür. İNDEKS işlevinin söz dizimi aşağıdaki gibidir:

• dizi (gerekli), değerini döndürmek istediğiniz aralığı belirtir.
• satır_sayısı(gerekli; ancak)sütun_sayısı belirtilmişse gerekli değildir), dizideki satır numarasını ifade eder.
• sütun_sayısı(isteğe bağlı; ancak)satır_sayısı atlanmışsa gereklidir), dizideki sütun numarasını belirtir.

Örneğin, listede Jeff'in puanınıöğrenmek için,6numaralı öğrencinin sınav puanını şu şekilde İNDEKS işleviyle alabilirsiniz:

=INDEX(C2:C11,6)

√ Not:C2:C11aralığı puanların listelendiği yerdir;6sayısı ise 6numaralı öğrencinin sınav puanını bulur.

Burada küçük bir test yapalım.=INDEX(A1:C1,2) formülü hangi değeri döndürür? --- Evet, verilen satırda Doğum tarihi değerini, yani 2 numaralı değeri döndürür.

Artık İNDEKS işlevinin yatay ya da dikey aralıklarda mükemmel çalışabildiğini biliyoruz. Peki ya birden fazla satır ve sütunu içeren daha geniş bir aralıkta bir değer döndürmemiz gerekirse? Bu durumda hem satır numarasını hem de sütun numarasını belirtmemiz gerekir. Örneğin, Jeff’in puanını tek bir sütunda değil, tablo aralığında bulmak için A2 ila C11 hücreleri içinde, satır numarası 6 ve sütun numarası 3 parametrelerini kullanarak puanını şu şekilde belirleyebiliriz:

=INDEX(A2:C11,6,3)

Ancak, çok sayıda satır ve sütundan oluşan gerçekten büyük bir veritabanında, formülü tam olarak belirtilmiş satır ve sütun numaralarıyla uygulamak kesinlikle pratik değildir. İşte tam da bu noktada EŞLEŞTİR işlevini birlikte kullanmamız gerekir.

Excel'de EŞLEŞTİR işlevini nasıl kullanılır

Excel'deki EŞLEŞTİR işlevi, belirtilen aralıkta belirli bir öğenin konumunu gösteren sayısal bir değer döndürür. EŞLEŞTİR işlevinin söz dizimi aşağıdaki gibidir:

• arama_değeri (gerekli), arama_dizisi içinde eşleştirilecek değeri ifade eder.
• arama_dizisi (gerekli), EŞLEŞTİR işlevinin arama yapmasını istediğiniz hücre aralığını belirtir.
• eşleşme_türü(isteğe bağlı):1,0veya -1.
  • 1(varsayılan), EŞLEŞTİR, Küçük veya eşit olan arama_değeri’ne en yakın büyük değeri bulur.arama_dizisi’ndeki değerler artan sırada yer almalıdır.
  • 0, EŞLEŞTİR, arama_değeri’ne tam olarak eşit olan ilk değeri bulur. arama_dizisi’ndeki değerler herhangi bir sırada olabilir. (Eşleşme türü 0 olarak ayarlandığında joker karakterler kullanabilirsiniz.)
  • -1, EŞLEŞTİR, Büyük veya eşit olan arama_değeri’ne en yakın küçük değeri bulur.arama_dizisi’ndeki değerler azalan sırada yer almalıdır.

Örneğin,Ad Listesi listesinde Vera'nın konumunuöğrenmek için Formülleri Ayırt Et işlevini şu şekilde kullanabilirsiniz:

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

√ Not: "4" sonucu, "Vera" adının listede 4. sırada yer aldığını gösterir.

Artık Excel'de İNDEKS ve EŞLEŞTİR işlevlerinin temel kullanımını bildiğimize göre, kollarımızı sıvayalım ve bu iki işlevi birleştirmeye hazır olalım.

Excel'de İNDEKS ve EŞLEŞTİR işlevlerini nasıl birleştiririz

İNDEKS ve EŞLEŞTİR işlevlerini nasıl birleştirebileceğimizi anlamak için aşağıdaki örneğe bakalım:

Sınav puanlarının Evelyn'in puanını bulmak için, 3 numaralı sütunda olduğunu bildiğimize göre, satır konumunu elle saymadan otomatik olarak belirlemek üzere EŞLEŞTİR işlevini kullanabiliriz. Ardından, belirlenen satırla 3. sütunun kesiştiği noktadaki değeri almak için İNDEKS işlevini kullanabiliriz: değeri:

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

Formül biraz karmaşık görünebilir; o yüzden her bir parçasını ayrı ayrı inceleyelim.

İNDEKS işlevininformülünde üç bağımsız değişken bulunur:

• satır_sayısı:MATCH("Evelyn",A2:A11,0)"Evelyn" değerinin A2:A11aralığındaki satır konumunu DİZİN'e sağlar; bu da5'dir.
• sütun_sayısı: 3, DİZİN'in puanı dizi içinde bulabilmesi için 3ncü sütunu belirtir.
• dizi:A2:C11, DİZİN'e belirtilen satır ve sütunun kesiştiği noktada eşleşen değeri döndürmesini söyler; bu aralık A2'den C11'e kadar uzanır. Sonuç olarak 90 sonucunu elde ederiz.

Yukarıdaki formülde sabitlenmiş bir değer kullandık: "Evelyn". Ancak pratikte sabit değerler pek kullanışlı değildir; çünkü farklı verileri (örneğin başka bir öğrencinin puanını) aramak istediğimizde her seferinde formülü değiştirmek zorunda kalırız. Böyle durumlarda, dinamik formüller oluşturmak için hücre başvurularından yararlanabiliriz. Örneğin, bu durumda "Evelyn" ifadesini F2 ile değiştireceğim:

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

İNDEKS ve Formülleri Ayırt Et örnekleri

Bu bölümde, İNDEKS ve EŞLEŞTİR işlevlerini farklı ihtiyaçlara göre kullanmak amacıyla çeşitli senaryolardan bahsedeceğiz.

İki yönlü arama için İNDEKS ve EŞLEŞTİR kullanımı

Önceki örnekte sütun numarasını biliyorduk ve satır numarasını bulmak için bir Formülleri Ayırt Et kullandık. Peki ya sütun numarasından da emin değilsek ne olur?

Bu tür durumlarda, satır ve sütun numaralarını belirlemek için iki adet EŞLEŞTİR işlevi kullanarak iki yönlü arama (matris araması olarak da bilinir) yapabiliriz. Örneğin, Evelyn'in puanını öğrenmek için şu formülü kullanmalıyız:

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

Sol yönlü arama için İNDEKS ve EŞLEŞTİR kullanımı

Şimdi Evelyn'in sınıfını belirlemeniz gerektiğine dair bir senaryo düşünün. Sınıf sütununun isim sütununun solunda bulunduğunu fark etmiş olabilirsiniz; bu durum, Excel'in başka bir güçlü arama işlevi olan DÜŞEYARA'nın yeteneklerinin ötesindedir.

Aslında, sol taraftan arama yapabilme yeteneği, İNDEKS ve EŞLEŞTİR kombinasyonunun DÜŞEYARA'ya kıyasla üstün olduğu yönlerden biridir.

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

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

Büyük/küçük harfe duyarlı arama için İNDEKS ve EŞLEŞTİR kullanımı

EŞLEŞTİR işlevi doğası gereği büyük/küçük harfe duyarsızdır. Ancak formülünüzün büyük ve küçük harfleri ayırt etmesini istiyorsanız, TÜMÜYLEUYSUN işlevini kullanarak formülünüzü geliştirebilirsiniz. EŞLEŞTİR işlevini TÜMÜYLEUYSUN ile birleştirip bir İNDEKS formülüne entegre ederek, aşağıdaki örnekte gösterildiği gibi büyük/küçük harfe duyarlı bir arama gerçekleştirebilirsiniz:

• dizi değeri, döndürmek istediğiniz aralığı ifade eder.
• arama_değeri, büyük/küçük harf duyarlılığıyla arama_dizisi içinde eşleştirilecek değeri ifade eder.
• arama_dizisi, EŞLEŞTİR işlevinin arama_değeri ile karşılaştırmasını istediğiniz hücre aralığını ifade eder.

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

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

√ Not: Bu bir dizi formülüdür ve Ctrl+Shift+Entertuşlarına basarak girilmesi gerekir; ancak Excel 365, Excel 2021 ve daha yeni sürümler hariçtir.

En yakın eşleşmeyi bulmak için İNDEKS ve EŞLEŞTİR kullanımı

Excel'de bir veri kümesi içinde belirli bir değere en yakın veya en yakın eşleşen değeri bulmanız gerekebilir. Bu tür senaryolarda, İNDEKS ve EŞLEŞTİR işlevlerini MUTLAK ve ENKÜÇÜK işlevleriyle birlikte kullanmak son derece faydalı olabilir.

• dizideğerini döndürmek istediğiniz aralığı ifade eder.
• arama_dizisi, arama_değeri ile en yakın eşleşmenin bulunacağı değer aralığını ifade eder.
• arama_değeri, en yakın eşleşmenin bulunacağı değeri ifade eder.

Örneğin, puanı 85 değerine en yakın olan öğrenciyi bulmak için aşağıdaki formülü kullanın: C2:C11 aralığında 85’e en yakın puanı aramak ve A2:A11 aralığından karşılık gelen değeri almak üzere.

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

√ Not: Bu bir dizi formülüdür ve Ctrl+Shift+Entertuşlarına basarak girilmesi gerekir; ancak Excel 365, Excel 2021 ve daha yeni sürümler hariçtir.

Birden çok kriterle arama yapmak için İNDEKS ve EŞLEŞTİR kullanımı

İki veya daha fazla sütunda arama yaparak birden fazla koşulu karşılayan bir değeri bulmak için 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 belirtilen kriterleri karşılayan istenen değeri bulmanıza olanak tanır.

√ Not: Bu bir dizi formülüdür ve Ctrl+Shift+Enter tuşlarına basarak girilmelidir. Ardından Formül Çubuğu’nda süslü parantez çifti görünür.

• dizideğerini döndürmek istediğiniz aralığı ifade eder.
• (arama_değeri=arama_dizisi) tek bir koşulu temsil eder. Bu koşul, belirli bir arama_değeri değerinin arama_dizisi içindeki değerlere eşit olup olmadığını kontrol eder.

Örneğin, 7/2/2008 doğum tarihine sahip A Sınıfı’ndan Coco’nun puanını bulmak içinaş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 fazla sütunda arama yapma

Birden fazla veri sütunuyla uğraştığınız bir senaryo 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 girdinin kategorisini veya sınıflandırmasını belirlemek için veri sütunlarında arama yapmalı ve bunu referans sütunundaki ilgili anahtarla ilişkilendirmelisiniz.

Örneğin, aşağıdaki tabloda INDEX ve MATCH kullanarak Shawn adlı öğrenciyi sınıfıyla nasıl eşleştirebiliriz? Bunu bir formülle başarabilirsiniz ancak bu formül oldukça uzundur ve anlaması zordur, hatırlamak ya da yazmak ise daha da 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 da bu noktada Kutools for Excel'in Birden çok sütuna göre indeksle ve eşleştir özelliği devreye giriyor. Bu özellik, işlemi basitleştirerek belirli girdileri ilgili kategorilerle hızlı ve kolayca eşlemenizi sağlar. Bu güçlü aracı kullanarak Shawn’ı sınıfıyla zahmetsizce eşleştirmek için tek yapmanız gereken, Kutools for Excel eklentisini indirip yüklemek ve ardından aşağıdaki adımları izlemektir:

1. Eşleşen sınıfı görüntülemek istediğiniz hedef hücreyi seçin.
2. Kutools sekmesinde, Formül Yardımcısı > Arama ve Başvuru > Birden Çok Sütuna Göre İndeksle ve Eşleştir seçeneğine tıklayın.
3. 
3. Açılan iletişim kutusunda şu adımları izleyin:
   1. İlk düğmesine tıklayarak, döndürmek istediğiniz temel bilgileri (sınıf adlarını) içeren sütunu Lookup_col yanından seçin. (Burada yalnızca tek bir sütun seçebilirsiniz.)
   2. İkinci düğmesine tıklayarak Table_rng yanındaki hücreleri seçin; bunlar, seçili Lookup_col sütunundaki değerlere (öğrenci adlarına) eşleştirilecek hücrelerdir.
   3. Üçüncü düğmesine tıklayarak Lookup_value yanındaki öğrencinin sınıfıyla eşleştirmek istediğiniz adını içeren hücreyi seçin; bu örnekte öğrenci adı Shawn’dur.
   4. OK’yi tıklayın.
   5. 

Sonuç

Kutools otomatik olarak formülü oluşturdu ve hedef hücrede Shawn’ın sınıf adını hemen göreceksiniz.

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

Hataları yok sayarak bir sütun veya satırdaki ilk boş olmayan değeri almak için INDEX ve MATCH işlevlerine dayalı bir formül kullanabilirsiniz. Ancak aralığınızdan hataları yok saymak istemiyorsanız, ISBLANK işlevini ekleyin.

• Hataları yoksayarak bir sütun veya satırdaki ilk boş olmayan değeri alın:

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

• Hataları dahil ederek bir sütun veya satırdaki 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 arama

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

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

INDEX ve MATCH ile MAKS veya MİN değerlerine ilişkin verileri arama

Bir aralıktaki en büyük veya en küçük değere ilişkin veriyi almanız gerekiyorsa, MAKS veya MİN işlevini INDEX ve MATCH işlevleriyle birlikte kullanabilirsiniz.

• DİZİN ve EŞLEŞTİR ile Maksimum Değer ile ilişkilendirilmiş bir değer alın:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• DİZİN ve EŞLEŞTİR ile Minimum Değer ile ilişkilendirilmiş bir değer alın:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Yukarıdaki formüllerde iki bağımsız değişken vardır:
• • dizi, ilgili bilgilerin döndürüleceği aralığı ifade eder.
  • arama_dizisi, belirli kriterlere göre incelenecek veya aranacak değer kümesini temsil eder; örneğin, maksimum değer veya minimum değer.

Örneğin, en yüksek puana sahip olan kişiyi belirlemek istiyorsanız, aşağıdaki formülü kullanın:

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

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

Excel’de INDEX ve MATCH işlevleriyle çalışırken, eşleşen sonuç bulunamadığında #YOK (#N/A) hatasıyla karşılaşabilirsiniz. Örneğin, aşağıdaki tabloda Samantha adlı bir öğrencinin puanını bulmaya çalıştığınızda, veri kümesinde yer almadığı için #YOK hatası görüntülenir.

Elektronik tablolarınızı daha kullanıcı dostu hale getirmek için bu hata mesajını özelleştirebilirsiniz; bunun için INDEX Formülleri Ayırt Et formülünüzü EĞERYOKSA (IFNA) işlevi içine alın:

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

Yukarıda Excel’de INDEX ve MATCH işlevlerine dair tüm bilgiler yer alıyor. Umarız bu eğitim faydalı olmuştur! Daha fazla Excel ipucu ve püf noktası keşfetmek istiyorsanız, lütfen buraya tıklayın ve binlerce eğitimin yer aldığı kapsamlı koleksiyonumuza erişin.