Exceli valem: hankige n-nda vaste INDEX / MATCH -

Üldine valem

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

Kokkuvõte

Valemiga andmekomplektist mitme sobiva väärtuse hankimiseks võite kasutada funktsioone IF ja SMALL, et selgitada välja iga vaste rea number ja sisestada see väärtus tagasi INDEX-i. Näidatud näites on valemi I7 valem:

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

Nimetatud vahemikud on amdid (D4: D11), id (I3) ja id (C4: C11).

Pange tähele, et see on massiivivalem ja see tuleb sisestada klahvidega Control + Tõst + Enter.

Selgitus

Põhimõtteliselt on see valem lihtsalt INDEX-valem, mis tõmbab massiivi väärtuse antud positsioonil. N väärtus on esitatud veerus H ja kogu "raske" töö, mida valem teeb, on välja selgitada rida, kust väärtus saada, kus rida vastab "n" väärtusele.

Funktsioon IF teeb kindlaks, millised read sisaldavad vastet, ja funktsioon SMALL tagastab loendi n-nda väärtuse. IF-i sees on loogiline test:

ids=id

mis annab selle massiivi:

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

Pange tähele, et kliendi ID vastab 1. ja 4. positsioonile, mis kuvatakse kui TRUE. IF väärtus "true if true" genereerib selle avaldisega suhteliste ridade arvude loendi:

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

mis toodab selle massiivi:

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

Seejärel "filtreeritakse" see massiiv loogiliste testitulemuste abil ja funktsioon IF tagastab järgmise massiivi tulemuse:

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

Pange tähele, et meil on kehtivad rea numbrid 1. ja 2. rea jaoks.

Seejärel töötleb seda massiivi SMALL, mis on konfigureeritud kasutama veerus H olevaid väärtusi "n" väärtuste tagastamiseks. Funktsioon VÄIKE ignoreerib massiivi loogilisi väärtusi TRUE ja FALSE. Lõpuks vähendavad valemid järgmiseks:

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

Vigade käitlemine

Kui antud ID-le pole enam vasteid, tagastab funktsioon VÄIKE vea #NUM. Selle tõrke saate lahendada funktsiooniga IFERROR või lisades loogika vastete loendamiseks ja töötluse katkestamiseks, kui veerus H olev number on suurem kui vastete arv. Siinne näide näitab ühte lähenemist.