INDEX ve MATCH'i Birleştirme

Artık bir aramanın iki parçasını biliyorsunuz. MATCH bir değerin nerede olduğunu bulur, INDEX ise bir konumda bulunan değeri döndürür.

Bunları birleştirdiğinizde eksiksiz bir arama elde edersiniz: MATCH satırı bulur, ardından INDEX seçtiğiniz herhangi bir sütundan o satırdaki veriyi getirir.

Yapıyı gördüğünüzde kalıp basittir: MATCH'i, normalde satır numarasının yazıldığı yerde INDEX'in içine yerleştirin.

Tekrar tekrar kullanacağınız yapı şöyledir:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

İçten dışa doğru okuyun. Önce MATCH çalışır ve bir konum numarası döndürür. Ardından bu sayı INDEX için row_num olur; INDEX de dönüş aralığınızdaki değeri döndürür.

Dönüş aralığı ile arama aralığı genellikle aynı sayıda satıra sahiptir; böylece birindeki konum diğerindeki konumla örtüşür.

A sütununda ürün adlarının, C sütununda da fiyatların bulunduğu bir tablo düşünün. "Cherry" ürününün fiyatını istiyorsunuz.

Önce MATCH Cherry'yi bulur: =MATCH("Cherry", A2:A20, 0) örneğin 3 sonucunu döndürür.

Ardından INDEX bu 3 değerini kullanır: =INDEX(C2:C20, 3), C sütununun 3. satırındaki fiyatı döndürür.

İç içe yerleştirdiğinizde işlemi tek seferde yaparsınız: =INDEX(C2:C20, MATCH("Cherry", A2:A20, 0)).

"Cherry" ifadesini doğrudan yazmak öğrenme aşamasında uygundur; ancak gerçek formüller bunun yerine bir hücreye başvurur. Arama terimini E1 hücresine yazın ve bu hücreye başvurun.

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))

Artık E1 hücresine hangi ürünü yazarsanız onun fiyatı anında döndürülür. Banana yazarsanız Banana'nın fiyatını, Date yazarsanız güncellenmiş sonucu görürsünüz.

Tek bir formül, tamamen girdi hücresinin yönlendirdiği yeniden kullanılabilir bir arama aracına dönüşür.

INDEX-MATCH yöntemini özel kılan püf noktası budur. Arama sütunu ile sonuç sütunu birbirinden bağımsızdır; bu nedenle döndürdüğünüz değer, aradığınız değerin solunda olabilir.

Diyelim ki fiyatlar A sütununda, ürün adları C sütununda. Bir ürünün fiyatını adına göre bulmak için =INDEX(A2:A20, MATCH(E1, C2:C20, 0)) yazarsınız.

C sütununda arama yaptınız, ancak A sütunundan sonuç döndürdünüz. VLOOKUP bunu yardım almadan yapamaz.

Sonuç aralığı, geri alacağınız şeyi belirler. Aynı anahtara göre arama yaparken yalnızca INDEX aralığını değiştirerek istediğiniz sütundan veri çekebilirsiniz.

Bir müşterinin e-posta adresini bulun: =INDEX(D2:D50, MATCH(E1, A2:A50, 0)).

Bunun yerine aynı müşterinin şehrini bulun: =INDEX(F2:F50, MATCH(E1, A2:A50, 0)).

MATCH bölümü aynı kalır; farklı bir yanıt seçmek için yalnızca INDEX aralığı değişir.

INDEX işlevine, ikinci bir MATCH ile bulduğunuz bir sütun numarası da sağlayabilirsiniz. Böylece bir değeri bir satır ile sütunun kesişiminde nokta atışı olarak bulursunuz.

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

İlk MATCH, A sütunundaki etiketlerden satırı bulur; ikinci MATCH, 1. satırdaki başlıklardan sütunu bulur. INDEX, bunların kesiştiği hücreyi döndürür. Bu gelişmiş kalıp ileride ayrıntılı olarak ele alınacaktır.

Konumların birbirine uyması için arama aralığınız ile sonuç aralığınız aynı satırdan başlamalı ve aynı yüksekliğe sahip olmalıdır.

MATCH A2:A20 aralığında (19 satır) arama yaparken INDEX C2:C19 aralığından (18 satır) sonuç döndürürse konumlar kayar ve yanlış sonuç alırsınız.

Güvenilir bir alışkanlık olarak her ikisi için de tam olarak aynı satır aralığını kullanın; örneğin A2:A20 ve C2:C20. A:A ve C:C gibi tüm sütun başvuruları da otomatik olarak hizalı kalır.

MATCH arama değerini bulamazsa #N/A döndürür ve INDEX-MATCH işleminin tamamı bu hatayı gösterir. Düzenli bir yedek sonuç için işlemi IFNA ile sarmalayın.

=IFNA(INDEX(C2:C20, MATCH(E1, A2:A20, 0)), "Not found")

Artık eksik bir ürün, korkutucu bir hata yerine "Bulunamadı" metnini gösterir. IFERROR da işe yarar, ancak IFNA yalnızca bulunamama durumunu hedefler ve diğer hataların görünür olmasına izin verir.

Şimdi hepsini bir araya getirin. Bir çalışan tablonuz var: A sütununda kimlikler, B'de adlar, C'de departmanlar, D'de maaşlar bulunuyor. Kullanıcı G1 hücresine bir kimlik giriyor.

Bu çalışanın departmanını döndürmek için: =INDEX(C2:C200, MATCH(G1, A2:A200, 0)).

Bunun yerine maaşını döndürmek için INDEX aralığını D2:D200 olarak değiştirin. Arama mantığı hiç değişmez; yalnızca veriyi okuduğunuz sütun değişir. Bu, günlük dinamik aramaların temel aracıdır.

Bir formül göz korkutucu görünüyorsa, elektronik tablonun yaptığı gibi onu en içteki işlevden dışa doğru değerlendirin.

=INDEX(C2:C20, MATCH(E1, A2:A20, 0)) için önce MATCH(E1, A2:A20, 0) ifadesini okuyun; bunun 5 gibi bir sayı döndürdüğünü düşünün, ardından zihninizde bu ifadeyi değiştirerek =INDEX(C2:C20, 5) sonucuna ulaşın.

Birdenbire formül yalnızca "5. fiyatı döndür" anlamına gelir. Bu alışkanlık, iç içe geçmiş her aramanın hatalarını ayıklamayı kolaylaştırır.

İki işlevin nasıl birleştiğini anladığınızı doğrulayın.

İki işlevi esnek bir aramada birleştirdiniz:

• Kalıp: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• MATCH satır konumunu bulur; INDEX o konumdaki değeri döndürür
• Arama ve sonuç sütunları bağımsızdır; böylece sola da sağa baktığınız kadar kolay bakabilirsiniz
• Her iki aralığı aynı yükseklikte tutun ve hataları düzgün biçimde işlemek için IFNA ile sarmalayın

Sırada, bu yaklaşımın neden çoğu zaman VLOOKUP yöntemini geride bıraktığını tam olarak göreceksiniz.