Excel formülü: INDEX / MATCH ile n'inci eşleşmeyi elde edin -

İçindekiler

Genel formül

(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))

Özet

Formül içeren bir veri kümesinden birden çok eşleşen değer almak için, her eşleşmenin satır numarasını bulmak ve bu değeri INDEX'e geri beslemek için EĞER ve KÜÇÜK işlevlerini kullanabilirsiniz. Gösterilen örnekte, 17'deki formül şöyledir:

(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))

Adlı aralıkları olduğu AMTS : (D11 D4) id (I3) ve kimlikleri (C4: C11).

Bunun bir dizi formülü olduğunu ve Control + Shift + Enter ile girilmesi gerektiğini unutmayın.

Açıklama

Temelde, bu formül, belirli bir konumdaki bir dizideki değeri alan bir INDEX formülüdür. N değeri H sütununda sağlanır ve formülün yaptığı tüm "ağır" işler, bir değerin alınacağı satırı bulmaktır, burada satır "n'inci" eşleşmeye karşılık gelir.

EĞER işlevi, hangi satırların bir eşleşme içerdiğini bulma işini yapar ve KÜÇÜK işlevi bu listeden n'inci değeri döndürür. IF'nin içindeki mantıksal test:

ids=id

bu diziyi verir:

(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)

1. ve 4. pozisyonlardaki müşteri kimliğinin DOĞRU olarak görünen eşleştiğine dikkat edin. IF'deki "değer doğruysa değer" bağımsız değişkeni, bu ifadeyle göreli satır numaralarının bir listesini oluşturur:

ROW(ids)-ROW(INDEX(ids,1,1))+1

bu diziyi üreten:

(1;2;3;4;5;6;7)

Bu dizi daha sonra mantıksal test sonuçlarına göre "filtrelenir" ve IF işlevi aşağıdaki dizi sonucunu döndürür:

(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)

Satır 1 ve satır 2 için geçerli satır numaralarımız olduğunu unutmayın.

Bu dizi daha sonra "nth" değerleri döndürmek için H sütunundaki değerleri kullanmak üzere yapılandırılan SMALL tarafından işlenir. KÜÇÜK işlevi, dizideki DOĞRU ve YANLIŞ mantıksal değerlerini otomatik olarak yok sayar. Sonunda formüller şu şekilde indirgenir:

=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125

Hataları işleme

Belirli bir id için başka eşleşme olmadığında, KÜÇÜK işlevi #SAYI hatası döndürür. Bu hatayı EĞERHATA işleviyle veya eşleşmeleri saymak için mantık ekleyerek ve H sütunundaki sayı eşleşme sayısından büyük olduğunda işlemi durdurarak halledebilirsiniz. Buradaki örnek bir yaklaşımı göstermektedir.