INDEX ve MATCH ile birden fazla sütunda arama

Birden fazla sütunda eşleştirerek bir değer aramak için, INDEX ve MATCH işlevlerine dayanan; ayrıca MMULT, TRANSPOSE ve SÜTUN işlevlerini içeren güçlü bir dizi formülü size yardımcı olacaktır.

Birden fazla sütunda eşleştirerek bir değer nasıl aranır?

Yukarıdaki tabloda gösterildiği gibi, her öğrencinin karşılık gelen sınıfını doldurmak için, bilgilerin birden fazla sütuna yayılmış olması durumunda öncelikle MMULT, TRANSPOSE ve SÜTUN işlevlerini kullanarak bir matris dizisi oluşturabilirsiniz. Ardından MATCH işlevi, aradığınız değerin konumunu verir ve bu konum INDEX işlevine aktarılarak diziden istediğiniz değer alınır.

Genel Söz Dizimi

=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))

√ Not: Bu bir dizi formülüdür ve girilmesi için Ctrl+Shift+Entertuşlarına basmanız gerekir.

Jimmy'nin sınıfını bulmakiçin aşağıdaki formülü H5 hücresine kopyalayın veya girin ve sonucu almak için Ctrl+Shift+Entertuşlarına basın:

=INDEX()$B$5:$B$7,(MATCH(1,MMULT(--())))$C$5:$E$7=G5),TRANSPOSE(SÜTUN()$C$5:$E$7)^0)),0)))

√ Not: Yukarıdaki dolar işaretleri ($), mutlak başvuruları gösterir; bu da formüldeki ad ve sınıf aralıklarının formülü diğer hücrelere taşıdığınızda veya kopyaladığınızda değişmeyeceği anlamına gelir. Arama değerini temsil eden hücre başvurusuna dolar işareti eklememelisiniz çünkü onun diğer hücrelere kopyalandığında göreli kalmasını istersiniz. Formülü girdikten sonra, formülü alttaki hücrelere uygulamak için doldurma tutamacını aşağı doğru sürükleyin.

Formülün açıklaması

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))

• --($C$5:$E$7=G5): Bu bölüm, $C$5:$E$7 aralığındaki her bir değerin G5 hücresindeki değerle eşleşip eşleşmediğini kontrol eder ve aşağıdaki gibi bir DOĞRU/YANLIŞ dizisi oluşturur:
  {DOĞRU,YANLIŞ,YANLIŞ;YANLIŞ,YANLIŞ,YANLIŞ;YANLIŞ,YANLIŞ,YANLIŞ}.
  Çift eksi (--) operatörü, ardından DOĞRU ve YANLIŞ değerlerini sırasıyla 1 ve 0’a dönüştürerek şu diziye ulaşmanızı sağlar:
  {1,0,0;0,0,0;0,0,0}.
• COLUMN($C$5:$E$7): SÜTUN işlevi, $C$5:$E$7 aralığı için şu sütun numarası dizisini döndürür: {3;4;5}.
• TRANSPOSE()COLUMN($C$5:$E$7)^0)=TRANSPOSE(){3,4,5}^0): Üs alma işleminden sonra {3,4,5} dizisindeki tüm sayılar 1 değerine dönüştürülür: {1,1,1}. Ardından TRANSPOSE işlevi, bu sütun dizisini şu satır dizisine çevirir: {1;1;1}.
• MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0))=MMULT(){1,0,0;0,0,0;0,0,0},{1;1;1}): MMULT işlevi, iki dizinin matris çarpımını şu şekilde döndürür: {1;0;0}.
• MATCH(1,)MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0)),0)=MATCH(1,){1;0;0},0): match_type0, MATCH işlevini 1 değerinin {1;0;0} dizisindeki ilk eşleşmesinin konumunu döndürmeye zorlar; bu da 1’dir.
• INDEX()$B$5:$B$7,(MATCH(1,))MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7 INDEX işlevi, 1. sıradaki değeri $B$5:$B$7 aralığından döndürür; bu da A.

Birden fazla sütunda eşleştirerek bir değeri kolayca aramak için profesyonel Excel eklentimizi de kullanabilirsiniz Kutools For Excel.Görevi gerçekleştirmek için buradaki talimatları inceleyin.

İlgili işlevler

İlgili Formüller