Excel'de belirli verileri doğru bir şekilde almak çoğu zaman sık karşılaşılan bir gerekliliktir. INDEX ve MATCH işlevlerinin her birinin kendine özgü güçlü yönleri olsa da, bunları birleştirmek veri arama için güçlü bir araç setinin kilidini açar. Birlikte, temel yatay ve dikey aramalardan iki yönlü, büyük/küçük harfe duyarlı ve çok kriterli aramalar gibi daha gelişmiş işlevlere kadar çeşitli arama yeteneklerini kolaylaştırırlar. DÜŞEYARA ile karşılaştırıldığında gelişmiş yetenekler sunan INDEX ve MATCH'ın eşleştirilmesi, daha geniş bir yelpazede veri arama seçeneklerine olanak tanır. Bu eğitimde gelin birlikte başarabilecekleri olasılıkların derinliğini inceleyelim.

• İNDEKS işlevi
• MATCH işlevi
• INDEX ve MATCH'i birleştirin

• İki yönlü arama
• Sol arama
• Büyük/küçük harfe duyarlı arama
• En yakın eşleşme
• Birden çok kriterle arama
• Birden çok sütunda arama
• İlk boş olmayan değeri arama
• İlk sayısal değeri arama
• MAX veya MIN ilişkilerini arama
• İpucu: Kendi #YOK hata mesajlarınızı uyarlayın

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

INDEX ve MATCH fonksiyonlarını kullanmadan önce, INDEX ve MATCH'ın ilk olarak değerleri aramamıza nasıl yardımcı olabileceğini bildiğimizden emin olalım.

Excel'de INDEX işlevi nasıl kullanılır?

The INDEX Excel'deki işlev, belirli bir aralıkta belirli bir konumdaki değeri döndürür. INDEX işlevinin sözdizimi aşağıdaki gibidir:

• dizi (gerekli) değeri döndürmek istediğiniz aralığı ifade eder.
• satır_sayısı (gerekli olmadığı sürece sütun_sayısı mevcut) dizinin satır numarasını ifade eder.
• sütun_sayısı (isteğe bağlı, ancak aşağıdaki durumlarda gereklidir) satır_sayısı atlanmıştır) dizinin sütun numarasını ifade eder.

Örneğin, bilmek Jeff'in puanı, 6Listedeki öğrenciniz varsa, INDEX işlevini şu şekilde kullanabilirsiniz:

=INDEX(C2:C11,6)

√ Not: Aralık C2: C11 puanların listelendiği yer, sayı ise 6 sınav notunu bulur 6öğrenci.

Burada küçük bir test yapalım. formül için =ENDEKS(A1:C1,2), hangi değeri döndürecek? --- Evet geri dönecek Doğum tarihi, 2Verilen satırdaki nd değeri.

Artık INDEX işlevinin yatay veya dikey aralıklarla mükemmel şekilde çalışabileceğini bilmeliyiz. Peki ya birkaç satır ve sütun içeren daha geniş bir aralıkta bir değer döndürmemiz gerekirse? Peki, bu durumda hem satır numarası hem de sütun numarası uygulamalıyız. Örneğin, öğrenmek için Jeff'in puanı tek bir sütun yerine tablo aralığı içinde puanını bir 6 numaralı satır ve 3 numaralı sütun içinde A2'den C11'e kadar olan hücreler bunun gibi:

=INDEX(A2:C11,6,3)

Ancak, birden çok satır ve sütun içeren gerçekten büyük bir veritabanı için, formülü tam bir satır numarası ve sütun numarası ile uygulamak bizim için kesinlikle uygun değildir. Ve bu, MATCH işlevinin kullanımını birleştirmemiz gerektiğinde.

İnanılmaz! Excel'de Chrome, Edge, Firefox ve Safari Gibi Verimli Sekmeler Kullanmak!
Zamanınızın% 50'sinden tasarruf edin ve her gün binlerce fare tıklamasını azaltın!

Excel'de MATCH işlevi nasıl kullanılır?

Excel'deki KAÇINCI işlevi, verilen aralıktaki belirli bir öğenin konumu olan sayısal bir değer döndürür. KAÇINCI işlevinin sözdizimi aşağıdaki gibidir:

• lookup_value (gerekli) eşleşecek değeri ifade eder arama_dizisi.
• arama_dizisi (gerekli) MATCH'ın aramasını istediğiniz hücre aralığını ifade eder.
• eşleşme türü (isteğe bağlı): 1, 0 or -1.
  • 1 (varsayılan), KAÇINCI değerden küçük veya ona eşit olan en büyük değeri bulacaktır. lookup_value. içindeki değerler arama_dizisi artan düzende yerleştirilmelidir.
  • 0, MAÇ tam olarak eşit olan ilk değeri bulacaktır. lookup_value. içindeki değerler arama_dizisi herhangi bir sırada olabilir. (Eşleme türünün 0 olarak ayarlandığı durumlarda joker karakterler kullanabilirsiniz.)
  • -1, KAÇINCI değerden büyük veya ona eşit olan en küçük değeri bulacaktır. lookup_value. içindeki değerler arama_dizisi azalan düzende yerleştirilmelidir.

Örneğin, bilmek Vera'nın isim listesindeki konumu, MAÇ formülünü şu şekilde kullanabilirsiniz:

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

√ Not: “4” sonucu “Vera” isminin listenin 4. sırasında yer aldığını göstermektedir.

Artık INDEX ve MATCH işlevlerinin Excel'deki temel kullanımlarını öğrendiğimize göre, kolları sıvayalım ve iki işlevi birleştirmeye hazırlanalım.

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

INDEX ve MATCH işlevlerini nasıl birleştirebileceğimizi anlamak için lütfen aşağıdaki örneğe bakın:

Bulmak Evelyn'in puanısınav puanlarının güncel olduğunu bilerek 3XNUMX. sütunda bunu yapabiliriz satır konumunu otomatik olarak belirlemek için MATCH işlevini kullanın manuel olarak saymaya gerek kalmadan. Daha sonra, INDEX işlevini kullanarak verileri geri alabiliriz. tanımlanan satır ile 3. sütunun kesişimindeki değer:

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

Formül biraz karmaşık görünebileceği için her bir kısmını tek tek ele alalım.

The INDEX formül üç argüman içerir:

• satır_sayısı: KAÇINCI("Evelyn",A2:A11,0) INDEX'e " değerinin satır konumunu sağlarEvelyn" aralığında A2: A11, Olduğu bir 5.
• sütun_sayısı: 3 belirtir 3Dizi içindeki puanı bulmak için INDEX'in rd sütunu.
• dizi: A2: C11 INDEX'e, belirtilen satır ve sütunun kesişimindeki eşleşen değeri, aşağıdaki aralık dahilinde döndürmesi talimatını verir: A2 - C11. Sonunda sonuca ulaşıyoruz 90.

Yukarıdaki formülde sabit kodlanmış bir değer kullandık, "Evelyn". Ancak pratikte, sabit kodlanmış değerler pratik değildir, çünkü başka bir öğrencinin puanı gibi farklı verileri aramaya çalıştığımız her seferde bunların değiştirilmesi gerekir. Bu tür senaryolarda dinamik formüller oluşturmak için hücre referanslarından yararlanabiliriz. Mesela bu durumda yapacağım "Evelyn"i F2 olarak değiştir:

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

INDEX ve MATCH formül örnekleri

Bu bölümde, farklı ihtiyaçları karşılamak için INDEX ve MATCH işlevlerini kullanmak için farklı durumlardan bahsedeceğiz.

İki yönlü arama uygulamak için INDEX ve MATCH

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

Bu gibi durumlarda, iki MATCH işlevini kullanarak matris araması olarak da bilinen iki yönlü bir arama gerçekleştirebiliriz: biri satır numarasını bulmak için, diğeri sütun numarasını belirlemek için. Örneğin bilmek Evelyn'in puanı, şu formülü kullanmalıyız:

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

Soldan arama uygulamak için INDEX ve MATCH

Şimdi Evelyn'in sınıfını belirlemeniz gereken bir senaryoyu ele alalım. Sınıf sütununun ad sütununun solunda konumlandırıldığını fark etmiş olabilirsiniz; bu durum, başka bir güçlü Excel arama işlevi olan DÜŞEYARA'nın yeteneklerini aşan bir durumdur.

Aslında, sol taraftan arama yapma yeteneği, INDEX ve MATCH kombinasyonunun DÜŞEYARA'yı gölgede bıraktığı yönlerden biridir.

Bulmak Evelyn'in sınıfıiçin aşağıdaki formülü kullanın B2:B11'de Evelyn'i arayın ve A2:A11'den karşılık gelen değeri al.

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

Büyük/küçük harfe duyarlı bir arama uygulamak için INDEX ve MATCH

MATCH işlevleri doğası gereği büyük/küçük harfe duyarlı değildir. Ancak formülünüzün büyük ve küçük harf karakterleri arasında ayrım yapmasını istediğinizde, formülünüzü aşağıdakileri ekleyerek geliştirebilirsiniz: TAM işlev. MATCH işlevini bir INDEX formülünde EXACT ile birleştirerek, aşağıda gösterildiği gibi etkili bir şekilde büyük/küçük harfe duyarlı bir arama gerçekleştirebilirsiniz:

• dizi değeri döndürmek istediğiniz aralığı ifade eder.
• lookup_value karakterlerin durumu dikkate alınarak eşleşecek değeri ifade eder. arama_dizisi.
• arama_dizisi MATCH'in karşılaştırmasını istediğiniz hücre aralığını ifade eder lookup_value.

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

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

√ Not: Bu, aşağıdakilerle girmenizi gerektiren bir dizi formülüdür: Ctrl + vardiya + Keşfet, Excel 365 ve Excel 2021 hariç.

En yakın eşleşmeyi bulmak için INDEX ve MATCH

Excel'de, bir veri kümesindeki belirli bir değere en yakın veya en yakın eşleşmeyi bulmanız gereken durumlarla karşılaşabilirsiniz. Bu tür senaryolarda, INDEX ve MATCH işlevlerinin yanı sıra ABS ve MIN işlevlerinin bir kombinasyonunun kullanılması inanılmaz derecede yararlı olabilir.

• dizi değeri döndürmek istediğiniz aralığı ifade eder.
• arama_dizisi en yakın eşleşmeyi bulmak istediğiniz değer aralığını ifade eder lookup_value.
• lookup_value en yakın eşleşmeyi bulacak değeri ifade eder.

Örneğin, öğrenmek için puanı 85'e en yakın olaniçin aşağıdaki formülü kullanın C85:C2'de 11'e en yakın puanı arayın ve A2:A11'den karşılık gelen değeri al.

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

√ Not: Bu, aşağıdakilerle girmenizi gerektiren bir dizi formülüdür: Ctrl + vardiya + Keşfet, Excel 365 ve Excel 2021 hariç.

İnanılmaz! Excel'de Chrome, Edge, Firefox ve Safari Gibi Verimli Sekmeler Kullanmak!
Zamanınızın% 50'sinden tasarruf edin ve her gün binlerce fare tıklamasını azaltın!

Birden çok kritere sahip bir arama uygulamak için INDEX ve MATCH

İki veya daha fazla sütunda arama yapmanızı gerektiren, birden çok koşulu karşılayan bir değer bulmak için aşağıdaki formülü kullanın. Formül, farklı sütunlarda çeşitli koşullar belirterek çok kriterli arama yapmanıza olanak tanır ve belirtilen tüm kriterleri karşılayan istediğiniz değeri bulmanıza yardımcı olur.

√ Not: Bu, aşağıdakilerle girmenizi gerektiren bir dizi formülüdür: Ctrl + vardiya + Keşfet. Formül çubuğunda bir çift süslü parantez görünecektir.

• dizi değeri döndürmek istediğiniz aralığı ifade eder.
• (aranan_değer=aranan_dizi) tek bir durumu temsil eder. Bu koşul belirli bir durumun olup olmadığını kontrol eder. lookup_value içindeki değerlerle eşleşir arama_dizisi.

Örneğin, bulmak için Doğum tarihi 7/2/2008 olan A Sınıfı Coco'nun puanı, aşağıdaki formülü kullanabilirsiniz:

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

Birden çok sütuna bir arama uygulamak için INDEX ve MATCH

Birden çok 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 girişin kategorisini veya sınıflandırmasını belirlemek için veri sütunlarında bir arama yapmanız ve onu referans sütunundaki ilgili anahtarla ilişkilendirmeniz gerekir.

Örneğin aşağıdaki tabloda INDEX ve MATCH kullanarak Shawn adlı öğrenciyi karşılık geldiği sınıfla nasıl eşleştirebiliriz? Evet, bunu bir formülle başarabilirsiniz ama formül oldukça kapsamlıdır ve bırakın hatırlamayı ve yazmayı, anlaşılması bile zor olabilir.

=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)), "")

Bu nerede Kutools for Excel's Birden Çok Sütunda Dizin ve Eşleştirme özelliği işe yarıyor. Süreci basitleştirerek belirli girişleri karşılık gelen kategorilerle eşleştirmeyi hızlı ve kolay hale getirir. Bu güçlü aracın kilidini açmak ve Shawn'u sınıfıyla zahmetsizce eşleştirmek için tek yapmanız gereken Kutools for Excel eklentisini indirip yükleyinve ardından aşağıdakileri yapın:

1. Eşleşen sınıfı görüntülemek istediğiniz hedef hücreyi seçin.
2. Üzerinde Kutools sekmesini tıklayın Formül Yardımcısı > Arama ve Referans > Birden Çok Sütunda Dizin ve Eşleştirme.
3. 
3. Açılan iletişim kutusunda aşağıdakileri yapın:
   1. 1.'e tıklayın yanındaki düğmeyi tıklayın Ara_kol döndürmek istediğiniz anahtar bilgileri, yani sınıf adlarını içeren sütunu seçmek için. (Burada yalnızca tek bir sütunu seçebilirsiniz.)
   2. 2.'ye tıklayın yanındaki düğmeyi tıklayın tablo_rng seçilen değerlerle eşleşecek hücreleri seçmek için Ara_kolyani öğrenci isimleri.
   3. 3.'e tıklayın yanındaki düğmeyi tıklayın aranan_değer Sınıfıyla eşleştirmek istediğiniz öğrencinin adını (bu durumda Shawn) içeren hücreyi seçin.
   4. Tıkla OK.
   5. 

Sonuç

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

İlk boş olmayan değeri aramak için INDEX ve MATCH

Bir sütundan veya satırdan hataları göz ardı ederek ilk boş olmayan değeri almak için INDEX ve KAÇINCI işlevlerine dayalı bir formül kullanabilirsiniz. Ancak aralığınızdaki hataları göz ardı etmek istemiyorsanız ISBLANK işlevini ekleyin.

• Hataları göz ardı ederek 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 bir sütun veya satırdaki ilk boş olmayan değeri alın:

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

  

İlk sayısal değeri aramak için INDEX ve MATCH

Bir sütundan 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))

MAX veya MIN ilişkilerini aramak için INDEX ve MATCH

Bir aralık içindeki maksimum veya minimum değerle ilişkili bir değer almanız gerekiyorsa, INDEX ve MATCH işlevleriyle birlikte MAX veya MIN işlevini kullanabilirsiniz.

• Maksimum değerle ilişkili bir değeri almak için INDEX ve MATCH:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• Minimum değerle ilişkili bir değeri almak için INDEX ve MATCH:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Yukarıdaki formüllerde iki argüman vardır:
• • dizi ilgili bilgiyi döndürmek istediğiniz aralığı ifade eder.
  • arama_dizisi belirli kriterlere göre incelenecek veya aranacak değerler kümesini, yani maksimum veya minimum değerleri temsil eder.

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

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

İpucu: Kendi #YOK hata mesajlarınızı uyarlayın

Excel'in INDEX ve MATCH işlevleriyle çalışırken eşleşen sonuç olmadığında #N/A hatasıyla karşılaşabilirsiniz. Örneğin aşağıdaki tabloda Samantha isimli bir öğrencinin puanı bulunmaya çalışıldığında veri setinde yer almadığı için #N/A hatası çıkıyor.

E-tablolarınızı daha kullanıcı dostu hale getirmek için INDEX MATCH formülünüzü IFNA işlevine sararak bu hata mesajını özelleştirebilirsiniz:

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

Yukarıda Excel'deki INDEX ve KAÇINCI işlevleriyle ilgili tüm içerik bulunmaktadır. Umarım öğreticiyi faydalı bulursunuz. Daha fazla Excel ipucu ve püf noktası keşfetmek istiyorsanız, lütfen buraya tıklayınız binlerce öğreticiden oluşan kapsamlı koleksiyonumuza erişmek için.