Index-match ile excel'de düşeyara nasıl tersine çevrilir?

INDEX ve MATCH’in geriye doğru arama formülü olarak nasıl çalıştığını anlamak için basit bir şey seçmeniz gerekir: Match, dizine bir sütun veya satırdaki bir değerin konumunu (hücre numarası) bildirir, ardından dizin bu değeri döndürür. Bu konumu kullanarak değer (hücre numarası) .

Şöyle düşünün, MATCH fonksiyonu suçluyu bulan gizli bir ajan, INDEX fonksiyonu ise daha sonra o suçluyu tutuklayan bir polis.

Ancak gelin bu iki işlevi nasıl birleştirebileceğimizi detaylı olarak öğrenelim. Aşağıda bildiğiniz gibi INDEX’in söz dizimi bulunmaktadır.

INDEX(dizi; satır_sayısı; [sütun_sayısı])

INDEX işlevinde satır_sayısı argümanı, değeri hangi satırdan döndürmesi gerektiğini söyler. Diyelim ki 4 girerseniz 4. satırın değerini döndürecek.

Geriye doğru arama formülünü oluşturmak için MATCH’ı INDEX’in satır_argumentiyle değiştirmemiz gerekir.

MATCH kullandığımızda arama sütununda değeri arar ve o değerin hücre numarasını döndürür. Daha sonra INDEX, değer sütunundan hücre konumunu belirlemek için bu sayıyı kullanır.

Sonunda o hücrenin değerini döndürür ve aradığınız değeri elde edersiniz. Ama şimdi gerçek bir örnekle çalışalım. Aşağıda şehirlerin ve orada çalışan çalışanların adlarının bir listesi bulunmaktadır.

Burada Mumbai’de çalışan çalışanın adını aramamız gerekiyor. Şimdi, verilere bakarsanız, şehirlerin olduğu sütunda, bu bizim arama sütunumuzdur ve çalışan adlarının olduğu sütunda, bu da sizin değer sütununuzdur. Ve formül şöyle olacak:

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

Anlamak için bu formülü iki parçaya ayıralım.

Bölüm 1 : İlk bölümde “Mumbai” değerini aramak için match fonksiyonunu kullandık ve bu, şehirler sütununda “Mumbai” değerinin bulunduğu hücrenin konumu olan “5” değerini döndürdü.

Bölüm 2 : İkinci bölümde INDEX’i kullandık ve değeri bulmak için çalışan adı sütununa başvurduk. Burada indeks fonksiyonu sütunun 5. hücresinin değerini istediğinizi biliyor. Yani sonuçta “Siya” döndürdü.

Daha Fazla INDEX ve Eşleştirme Örneği

INDEX MATCH formülünü kullanarak çözdüğümüz bazı genel sorunlarımız var. Örnek dosyalara erişin: Her örneği takip etmek için bu örnek dosyaları buradan indirdiğinizden emin olun.

1. INDEX – MATCH ile temel arama

Normal bir arama, arama formülleriyle yapmanız gereken en önemli görevlerden biridir ve INDEX MATCH bunun için mükemmeldir. Burada çalışan kimliğini ve adını içeren bir veri tablomuz var. Her kimlik benzersizdir ve çalışanın adını kimliğiyle birlikte aramanız gerekir.

Diyelim ki EMP-132 adını aramak istiyorsunuz. Bunun için formül şu şekilde olacaktır:

 =INDEX(name_column,MATCH(emp-id,emp-id_column,0))

bu formül bu şekilde çalışıyor

Öncelikle MATCH, emp id sütunundaki emp id ile eşleşir ve aradığınız id’nin hücre numarasını döndürür. Burada satır numarası 6’dır.

Bundan sonra INDEX, aynı hücre numarasını kullanarak isim sütunundan çalışan adını döndürür.

2. Sola bakın

Bir değer ararken DÜŞEYARA sola gidemez. Bahsettiğim gibi INDEX ve MATCH’te istediğiniz yönde arama yapabilirsiniz. Aşağıdaki veri tablosunda tutar sütunundan sonra fatura numarası sütunu bulunmaktadır.

Yani belirli bir faturanın tutarını aramak istiyorsanız DÜŞEYARA ile bu mümkün değildir. DÜŞEYARA’da bir tablo seçtiğinizde, o tablodaki ilk sütun arama sütunu olacaktır.

Ancak burada, bu tabloda, tablonun son sütununu arama sütunu olarak kullanmamız gerekiyor. DÜŞEYARA’yı burada beğenmedim. Kurtarma için INDEX ve MATCH’i çağıralım ve formül şöyle olacaktır:

 =INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

…bu formül bu şekilde işliyor

• Öncelikle indeks fonksiyonunda miktar sütununa değindiniz. Bu, değeri almamız gereken sütundur.
• İkinci olarak , indeks fonksiyonunun satır_numarası argümanında, eşleştirme fonksiyonunu kullanıp fatura numarasını belirttiniz, fatura sütununa atıfta bulundunuz ve tam eşleşme için sıfır kullandınız.
• Üçüncüsü , eşleştirme işlevi aralıktan faturanın hücre numarasını döndürür.

Ve sonunda INDEX, hücreyi miktar sütunundan konumlandırarak tutarı döndürmek için bu sayıyı kullanır.

3. Bulanık Arama

Tıpkı DÜŞEYARA gibi kaba bir arama için INDEX/MATCH’i de kullanabilirsiniz.

Bulanık arama, aradığınız değerin listelenmediği ve en yakın eşleşmeyi elde etmek istediğiniz durumlarda yararlı olabilir. Aşağıdaki tabloda notlara göre notların bir listesi bulunmaktadır.

Eğer 79 puan almak istiyorsanız aşağıdaki formülü kullanabilirsiniz.

 =INDEX(B2:B6,MATCH(D3,A2:A6,1))

…bu formül bu şekilde işliyor

Bu formülde, yaklaşık bir arama yapmasına olanak tanıyan match_type için match işlevinde 1’i kullandık. Arama değerinden küçük veya ona eşit olan ilk değeri döndürür.

79 için ilk en düşük değer 75, 75 için ise B notu oluyor. Bu yüzden sonuçta B notu alıyorsunuz.

4. Yatay ARAMA

Bildiğiniz gibi YATAYARA yatay arama içindir ancak bunun için INDEX ve MATCH’ı da kullanabilirsiniz. Aşağıdaki veri tablosunda aylık satışlara ilişkin yatay bir tablonuz var ve “Mayıs ayı” satış değerini almak istiyorsunuz.

Ve formül şöyle olacak:

 =INDEX(amount,0,MATCH(lookup_month,months,0))

…bu formül bu şekilde işliyor

Yukarıdaki formülde indeksin satır_sayısı argümanında MATCH kullanmak yerine sütun_sayısı’nda kullandık. Match, Mayıs ayının sütun numarasını döndürür.

Daha sonra INDEX, konum numarasına göre sonuç sütununun değerini döndürür.

5. İki yönlü arama

İki yönlü aramada bir tablodan değer almamız gerekir. Bölgeye ve ürüne göre satış miktarlarını bulabileceğiniz aşağıdaki tabloya bakmanız yeterli.

Artık bir ürünün belirli bir alandaki satış miktarını öğrenmek istiyorsanız iki yönlü bir aramaya ihtiyacınız var ve bunun için INDEX MATCH MATCH kombinasyonunu kullanmanız gerekiyor. Evet, burada MATCH’i iki kez kullanmanız gerekiyor.

Normal bir INDEX ve MATCH kombinasyonunda, satır numarası için MATCH’i kullanırsınız, ancak iki yönlü aramada bunu sütun numarası için de kullanmanız gerekir. Formüller şöyle olacaktır:

 =INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

…bu formül bu şekilde işliyor

Yukarıdaki formülde, tablodan satış tutarını almak için indeks fonksiyonunu ve ardından satır_sayısı ve sütun_sayısı argümanları için eşleştirme fonksiyonunu kullandınız. Sütun_num argümanı olan match fonksiyonu, başvurduğunuz aralığın 5. satırında Product-D değeri olarak 5 değerini döndürür.

Ve satır_sayısı bağımsız değişkeninde bulunan eşleşme işlevi 2 değerini döndürür çünkü kuzey bölgesi değeri başvuruda bulunduğunuz aralığın ikinci sütunundadır.

Şimdi bu değerlerle indeks fonksiyonu 2. sütun ve 5. satırdaki değeri döndürdü: 1456.

6. Büyük/küçük harfe duyarlı

Bir liste veya sütunda iki aynı değere sahip olduğunuzda ancak metnin büyük/küçük harf durumu farklı olduğunda bir sorunla karşılaşırsanız, doğru değeri bulmak için büyük/küçük harfe duyarlı bir arama yapabilirsiniz. Aşağıdaki ikinci sütunda işaretlediğiniz adınızın bulunduğu öğrenci listesine bir göz atalım.

Ve başlangıçta aynı olan ancak farklı metin durumlarında olan isimler var. Örneğin John Parker ve JOHN Mathew. Diyelim ki “John” değil “JOHN” markalarını aramak istiyorsunuz, INDEX ve MATCH ile tam eşleme araması oluşturabilirsiniz. Ve formül şöyle olacak:

 =INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

…bu formül bu şekilde işliyor

Bu formülde, eşleştirme işlevinde TAM işlevini kullandınız. Eşleştirme işlevi büyük/küçük harfe duyarlı bir değer arayamadığından ve EXACT bunun için mükemmel bir işlevdir.

İki değeri karşılaştırabilir ve tamamen aynıysa (büyük/küçük harf dahil) DOĞRU döndürebilir, ancak bu formülü tablo biçiminde girmeniz gerekir çünkü tüm sütunu EXACT’taki tek bir değerle karşılaştırmanız gerekir. Girdiğinizde aşağıdaki gibi bir dizi döndürecektir.

 =INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

Buradan, diziden TRUE konumunu almak için match fonksiyonunu kullanmanız gerekir.

Bu noktada bulmanız gereken değerin hücre numarasına (satır numarasına) sahipsiniz. Ve bu sütun değeri için INDEX’i kullanabilirsiniz.

Önemli nokta: Eğer EXACT ile birden fazla TRUE elde ederseniz, eşleşme sadece ilk TRUE’nun sayısını döndürecektir.

7. INDEX MATCH ile joker karakterlerle

Joker karakterler çok faydalıdır. Joker karakterleri kullanarak kısmi arama yapabilirsiniz. Ve en iyi yanı, diğer tüm formül aramalarında olduğu gibi, indeks ve eşleşme ile joker karakterleri de kullanabilmenizdir.

Çalışanların adlarını, soyadlarını ve yaşlarını içeren aşağıdaki isim listesine bir göz atmanız yeterli.

Bu listeden belirli bir çalışanın (Sondra) yaşını almanız gerekir. Ama gerçek şu ki siz sadece ilk ismi biliyorsunuz.

Yıldız işareti kullanırsanız, Sondra’nın yaşını ilk adını kullanarak arayabilirsiniz. Bunun için formül şu şekilde olacaktır:

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

…bu formül bu şekilde işliyor

Yıldız işareti, n sayıda karakterin yerini alabilecek bir joker karakterdir. Yani adından sonra kullanıldığında soyadının yerine geçiyordu.

8. En düşük değer

Diyelim ki aşağıdaki gibi puanlara sahip bir öğrenci listeniz var. Şimdi bu listeden en düşük puanı alan öğrencinin adını aramak istiyorsunuz.

Bunun için MIN fonksiyonunu indeks ve eşleşme ile kullanabilirsiniz, formül şu şekilde olacaktır:

 =INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

A sütununda öğrencilerin adları, B sütununda ise puanları var.

Yani bu formülü bir hücreye yazıp Enter’a bastığınızda en düşük puanı alan öğrencinin adı yani Librada Bastian dönecektir.

Açıklama

Bu formülde üç farklı parçamız var.

İlk bölümde MIN işlevi en düşük puanı döndürür.

Bundan sonra, ikinci bölümde, eşleştirme işlevi o en düşük puana sahip hücreyi döndürür.

Sonunda indeks işlevi, eşleşmenin döndürdüğü aynı hücre konumunu kullanarak öğrenci adı sütununun değerini döndürür.

İpucu: Aynı şekilde en yüksek puanı alan öğrencinin adını da alabilirsiniz.

9. En iyi n’inci skor

Şimdi şöyle düşünün, elinizde sınav puanları olan öğrencilerin bir listesi var ve bu listeden 2. en yüksek puanı alan öğrencinin adını almak istiyorsunuz.

Sorun şu ki, ikinci en yüksek puanın ne olduğunu bilmiyorsunuz.

Normalde formül aramaları ile bir değer aradığınızda aradığınız değerden emin olursunuz. Ancak burada ikinci en yüksek puanın ne olduğunu bilmiyorsunuz.

Yani bunun için büyük bir fonksiyonu bir indeksle birleştirip eşleştirebilirsiniz. Büyük fonksiyon, aralıktaki ikinci en yüksek değeri belirlemenize yardımcı olacaktır.

Ve formül şöyle olacak:

 =INDEX(student_names,MATCH(LARGE(score,2),score,0))

…bu formül bu şekilde işliyor

Bu formülde aranan_değer bağımsız değişkeni için eşleşme işlevi içindeki büyük işlevi kullandınız. Ve büyük fonksiyonda ikinci en yüksek değeri elde etmek için puan aralığından ve 2’den bahsettiniz.

Büyük işlev ikinci en büyük değeri döndürdükten sonra, eşleştirme işlevi bu değeri kullanır ve karşılık gelen hücre numarasını döndürür.

Ve sonunda indeks fonksiyonu bu hücre numarasını kullanır ve öğrenci adını döndürür.

10. Çoklu kriterler

Normalde indeks ve eşleşme kombinasyonunun amacı tek bir değeri aramaktır. İşte bu yüzden match işlevinde yalnızca bir aralık kullanırsınız.

Ancak bazen gerçek dünyadaki verilerle karşı karşıya kaldığınızda bir değer bulmak için birden fazla kriter kullanmanız gerekir.

Aşağıdaki örneği düşünün. Burada ürün adı, kategori ve boyut gibi çeşitli ayrıntılara sahip bir ürün listeniz var.

Ve bu verilerden, tüm kriterleri kullanarak belirli bir ürünün fiyatını almak istiyorsunuz.

Bu nedenle formül şöyle olacaktır:

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))

Not: Bu bir dizi formülüdür, dolayısıyla ctrl + üst karakter + enter tuşlarını kullanarak girmeniz gerekir.

…bu formül bu şekilde işliyor

Bu formülde, üç farklı değeri eşleştirecek üç farklı diziniz var ve bu diziler, değerlerin eşleştiği yerde DOĞRU ve YANLIŞ değerini döndürüyor.

Daha sonra bunları birbiriyle çarptığınızda bir dizi veya buna benzer bir şey elde edersiniz.

 =INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

Ve match işlevi dizi için 1 konumunu döndürür.

Sonunda INDEX, eşleşmenin döndürdüğü sayıyı kullanarak fiyat sütununun fiyatını döndürür.

İpucu: Dizi formülü kullanmak istemiyorsanız SUMproduct koşulunu kullanabilirsiniz.

11. Bir aralığın ilk sayısal değeri

Diyelim ki hem metin hem de sayısal değerlerin bulunduğu bir listeniz var ve şimdi bu listeden ilk sayısal değeri almak istiyorsunuz.

Bunu yapmak için ISNUMBER işlevini indeks/eşleşme ile birleştirebilirsiniz. ISNUMBER, hangi değerin sayı, hangisinin metin olduğunu belirlemenize yardımcı olabilir.

Formül şöyle olacaktır:

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0))

Bu formülü tablo biçiminde girmelisiniz (Ctrl + Shift + Enter tuşlarını kullanarak).

…bu formül bu şekilde işliyor

Bu formülde ISNUMBER, listenin uzunluğuna eşit bir dizi döndürür ve bu dizide sayı olan değerler için TRUE ve geri kalan değerler için FALSE bulunur.

Bundan sonra eşleştirme fonksiyonunda arama değeri olarak TRUE’yu kullandınız. Böylece dizideki ilk DOĞRU’nun konum numarasını döndürür.

Sonuçta bu pozisyon numarası indeksini kullanmak ilk sayısal değeri döndürür.

12. İlk boş olmayan değeri alın

Şöyle düşünelim, ilk hücrelerin bir kısmının boş olduğu bir değerler listeniz var ve ilk boş olmayan değeri elde etmek istiyorsunuz.

Ve bu ilk boş olmayan değeri elde etmek için bu formülü kullanabilirsiniz.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))

…bu formül bu şekilde işliyor

Bu formülü akıllıca anlayabilmek için üç farklı parçaya ayırmamız gerekiyor.

İlk olarak , boş hücreler için DOĞRU ve boş olmayan hücreler için YANLIŞ’ın olduğu bir dizi elde etmek için match işlevindeki ISBLANK işlevini kullandınız.

İkinci olarak MATCH, ISBLANK tarafından döndürülen dizideki ilk TRUE’nun konum numarasını döndürür.

Yani bu noktada ilk boş olmayan değerin hücre numarasına sahipsiniz.

Üçüncüsü , indeks işlevi listedeki boş olmayan ilk değeri döndürür.

13. En sık kullanılan metin

Şimdi, metin değerlerinin bir listesi verildiğinde, en sık kullanılan metni saymanız gerektiğini varsayalım.

Aşağıdaki listede isimleriniz var.

Ancak birden fazla kullanılan isimler de vardır.

Yani şimdi listede en yüksek tekrara sahip olan ismi almanız gerekiyor. MODE, INDEX ve MATCH kombinasyonundan oluşan aşağıdaki formülü kullanabilirsiniz.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

Bu formülü eklediğinizde en yaygın isim olan “Tamesha” değerini döndürecektir.

…bu formül bu şekilde işliyor

İlk olarak MATCH tüm ad aralığını kendisiyle karşılaştıracaktır. Ve bunu yaparken her metnin ilk konumunu temsil edeceği bir dizi döndürecektir.

Listedeki en yaygın ismimiz olan “Tamesha” ismine bir örnek verelim. Şimdi listeye bakarsanız, bu önce 8. hücrede, sonra 12. hücrede gerçekleşti.

Ancak tabloya bakarsanız “Tamesha”nın olduğu tüm pozisyonlar için ilk pozisyonu olan 8’i döndürdü.

Bundan sonra, MATCH tarafından döndürülen diziden mode işlevi, ” Tamesha “nın ilk geçtiği hücre numarası olan en sık sayıyı döndürecektir.

Ve sonunda INDEX, bu hücre numarasını kullanarak metni döndürecektir.

14. Köprü oluşturun

Şimdi, bir değer aramanın yanı sıra o değer için bir köprü de oluşturmak istediğinizi varsayalım. Bu şekilde arama sütununuzun bulunduğu hücreye hızlı bir şekilde gidebilirsiniz.

Örneğin aşağıdaki tabloda bir kişinin yaşını almanız gerekiyor. Ve bu değer için bir köprü oluşturursanız bu değerin bulunduğu hücreye kolayca gidebilirsiniz.

Bunun için INDEX ve MATCH ile HYPERLINK + Cell’i kullanmalıyız ve formül şöyle olacaktır:

Bu formül şu şekilde çalışıyor

Daha iyi anlamak için bu formülü birkaç parçaya ayıralım.

• Öncelikle hücre fonksiyonunda indeks ve eşleşmeyi kullandınız. Ve hücre işlevinde her iki işlevi de kullandığınızda, karşılık gelen değer yerine karşılık gelen değerin hücre referansını alırsınız.
• İkinci olarak , “#” işaretini hücre referansıyla birleştirdiniz.
• Üçüncüsü , bağlantı metni olarak kullanılacak eşleşen değeri elde etmek için index ve match’i tekrar kullandınız. Bu şekilde karşılık gelen değere ve bu değerin bulunduğu hücreye olan bağlantıya sahip olursunuz.