Generieke formule
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Samenvatting
Om de positie van de laatste overeenkomst (dwz de laatste keer) van een opzoekwaarde te krijgen, kunt u een matrixformule gebruiken die is gebaseerd op de functies ALS, RIJ, INDEX, VERGELIJKEN en MAX. In het getoonde voorbeeld is de formule in H6:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Waarbij "namen" het benoemde bereik C4: C11 is.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Uitleg
De kern van deze formule is dat we een lijst met rijnummers voor een bepaald bereik maken, die overeenkomen met een waarde, en vervolgens de functie MAX gebruiken om het grootste rijnummer te krijgen, dat overeenkomt met de laatste overeenkomende waarde. We gebruiken de genoemde reeks "namen" alleen voor het gemak.
Dit deel van de formule werkt van binnen naar buiten en genereert een relatieve reeks rijnummers:
ROW(names)-MIN(ROW(names))+1
Het resultaat van de bovenstaande uitdrukking is een reeks getallen zoals deze:
(1;2;3;4;5;6;7;8)
Merk op dat we 8 nummers krijgen, die overeenkomen met de 8 rijen in de tabel. Zie deze pagina voor details over hoe dit deel van de formule werkt.
Voor het doel van deze formule willen we alleen rijnummers voor overeenkomende waarden, dus gebruiken we de ALS-functie om de waarden als volgt te filteren:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Dit resulteert in een array die er als volgt uitziet:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Merk op dat deze array nog steeds acht items bevat. Alleen rijnummers waarvan de waarde in de genoemde reeks "namen" gelijk is aan "amy" zijn bewaard gebleven (dwz 1, 4, 7). Alle andere items in de array zijn FALSE, omdat ze de logische test in de ALS-functie niet hebben gehaald.
Ten slotte levert de ALS-functie deze array aan de MAX-functie. MAX retourneert de hoogste waarde in de array, het getal 7, dat overeenkomt met het laatste rijnummer waar de naam "amy" is. Zodra we het laatste overeenkomende rijnummer kennen, kunnen we INDEX gebruiken om een waarde op die positie op te halen.
Voorlaatste, etc.
Om de op een na laatste positie, de derde naar de laatste, etc. te krijgen, kunt u als volgt van MIN-functie naar de LARGE-functie overschakelen:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
waarbij k staat voor "n-de grootste". Om bijvoorbeeld de voorlaatste overeenkomst in het bovenstaande voorbeeld te krijgen, kunt u het volgende gebruiken:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Net als voorheen is dit een matrixformule en moet deze worden ingevoerd met control + shift + enter.
