INDEX-MATCH-MATCH ile İki Yönlü Arama

Aylık satışlardan oluşan bir ızgara düşünün; bölgeler solda aşağı doğru, aylar ise üstte yatay olarak sıralanmış olsun. Seçilen bir bölge ile seçilen ayın kesiştiği değeri bulmak istiyorsunuz.

Normal bir arama, yalnızca tek bir yönde arama yaparak değer bulur. İki yönlü arama ise aynı anda her iki yönde arama yapar: doğru satırı ve doğru sütunu bulur, ardından bunların kesişimindeki değeri döndürür.

Bunun klasik aracı, iki MATCH çağrısıyla birleştirilen INDEX işlevidir; bu kullanım genellikle INDEX-MATCH-MATCH olarak yazılır.

INDEX, konumuna göre bir aralıktan değer döndürür. Tam biçimi INDEX(array, row_num, column_num) şeklindedir.

İşleve bir hücre bloğu, bir satır numarası ve bir sütun numarası verirsiniz; işlev de o konumdaki değeri döndürür. Örneğin B2'den başlayan bir ızgarada 3. satır ve 2. sütun istendiğinde, bu bloğun içinde 3 satır aşağıdaki ve 2 sütun sağdaki değer döndürülür.

Temel fikir şudur: INDEX etiketlere değil, konumlara ihtiyaç duyar. MATCH tam olarak bu konumları sağlar.

MATCH, tek bir satır veya sütun içindeki bir değerin konumunu bulur. Biçimi MATCH(lookup_value, lookup_array, match_type) şeklindedir.

Tam eşleşme için eşleşme türü olarak 0 kullanın. Sonuç bir sayıdır ve değerin 1'den başlayarak sayıldığında nerede bulunduğunu gösterir.

"East" A2:A5 aralığındaki ikinci öğeyse MATCH, 2 döndürür. Bu 2, INDEX için satır numarası olarak kullanılabilir.

İki yönlü arama için MATCH işlevini iki kez çalıştırırsınız:

• Bir MATCH, bölgenizin hangi satırda olduğunu bulur.
• Diğer MATCH, ayınızın hangi sütunda olduğunu bulur.

Ardından her iki sayıyı INDEX işlevine aktarırsınız. Satır MATCH'i dikey etiket aralığında, sütun MATCH'i ise yatay başlık aralığında arama yapar.

Sonuç, bu satır ile sütunun kesiştiği tek hücredir.

Bu yerleşimi gözünüzde canlandırın. Bölge etiketleri A2:A5 hücrelerinde (East, West, North, South) bulunur. Ay başlıkları B1:D1 hücrelerinde (Jan, Feb, Mar) yer alır. Gerçek satış sayıları ise B2:D5 aralığını doldurur.

Aramayı iki giriş hücresi yönlendirir: G1 istediğiniz bölgeyi, G2 ise istediğiniz ayı içerir.

Amacımız, G1 ve G2'yi okuyup B2:D5 aralığından eşleşen satış değerini döndüren tek bir formül oluşturmaktır.

Önce bölgeyi bulun. MATCH, G1'e yazılan değeri dikey etiket listesi olan A2:A5 aralığında arar.

G1 "North" içeriyorsa ve North üçüncü etiketse bu MATCH, 3 döndürür.

Bu sayı, INDEX'e veri bloğunun hangi satırını okuyacağını söyler. A2:A5 aralığında yalnızca etiketleri, verileri değil, aradığınıza dikkat edin; böylece 3. konum veri bloğunun üçüncü satırıyla hizalanır.

Sonra ayı bulun. Bu MATCH, G2'deki değeri yatay başlık satırı olan B1:D1 aralığında arar.

G2 "Feb" içeriyorsa ve Feb ikinci başlıksa MATCH, 2 döndürür.

Bu sayı, INDEX için sütun konumu olur. Satırlarda olduğu gibi yalnızca B1:D1 başlıklarında arama yaparız; böylece konum, B2:D5 içindeki veri sütunlarıyla hizalanır.

Şimdi her iki MATCH çağrısını INDEX'in içine yerleştirin. B2:D5 veri bloğu dizi olur; satır MATCH'i satır numarasını, sütun MATCH'i ise sütun numarasını sağlar.

G1 "North" ve G2 "Feb" olduğunda satır MATCH'i 3, sütun MATCH'i 2 verir; bu nedenle INDEX, B2:D5 aralığının 3. satırındaki ve 2. sütunundaki değeri döndürür.

Bu tek formül, iki yönlü aramanın tamamıdır.

B2:D5 aralığının şu değerleri içerdiğini varsayalım: North satırında Oca 50, Şub 80, Mar 65.

• MATCH("North", A2:A5, 0), 3 değerini döndürür.
• MATCH("Feb", B1:D1, 0), 2 değerini döndürür.
• INDEX(B2:D5, 3, 2), 3. satır ve 2. sütunu okuyarak 80 değerini döndürür.

G1'i "East" veya G2'yi "Mar" olarak değiştirirseniz formülün tamamı anında yeniden hesaplanır. INDEX işlevini iki MATCH aramasıyla yönlendirmenin gücü budur.

VLOOKUP yalnızca ilk sütunda arama yapar ve sağındaki sabit sayıda sütundan bir değer döndürür. Ayları değiştirmek için sütun dizinini kendiniz sabit olarak yazmanız veya hesaplamanız gerekirdi.

INDEX-MATCH-MATCH, hem satırın hem de sütunun etikete göre dinamik olarak seçilmesini sağlar. Sütunları yeniden sıralasanız veya yeni aylar ekleseniz bile formül çalışmaya devam eder; çünkü sabit bir sayıyla değil, başlık metniyle eşleştirme yapar.

En yaygın hata, uyuşmayan aralıklardır. Satır MATCH aralığı, INDEX veri bloğuyla aynı yüksekliğe; sütun MATCH aralığı ise aynı genişliğe sahip olmalıdır.

Burada A2:A5 4 satır yüksekliğindedir ve B2:D5 de 4 satır yüksekliğindedir; bu nedenle MATCH sonucunun 3 olması gerçekten üçüncü veri satırı anlamına gelir. Yanlışlıkla başlık içeren A1:A5 aralığında arama yaparsanız konumlar bir satır kayar ve yanlış hücreyi elde edersiniz.

İki yönlü arama düzenini öğrendiniz:

• INDEX, bir blok içindeki satır ve sütun konumunda bulunan değeri döndürür.
• Bir MATCH, dikey etiketlerde arama yaparak satırı bulur.
• İkinci bir MATCH, yatay başlıklarda arama yaparak sütunu bulur.

Birleşik =INDEX(data, MATCH(row), MATCH(col)) formülü iki girdi okur ve bunların kesişimindeki değeri döndürür. Hizaların bozulmasını önlemek için MATCH aralıklarını veri bloğuyla aynı boyutta tutun.