Match reverse

[Jan]

Hi

the Match function is quite helpful to compare cells and look for the
first matching entry in a sorted array of data. Is there a possiblity
to look for the first non-matching entry ?

Background: I have a column of data sorted according to an accession
number in column A. In every row I have one value in column B I am
interested in. Every accession number can occur several times., from 1
to 20 (too many for IF). In order to calculate means, STD, etc. from
the values in column B, I need to identify the beginning and the end of
the range of rows with the same accession numbers in column A.
Identifying the beginning of the range is easy using MATCH, but how can
I identify the last row with the same accession number ?

Thanks a lot !

Jan

 

[Peo Sjoblom]

Try

=MAX((A2:A200=H1)*ROW(A2:A200))

array entered with ctrl + shift & enter

will return the row number of the last instance of the
lookup value (lookup value in example is in H1)
adapt to fit accordingly

Regards,

Peo Sjoblom

 

[Jan]

Hi,

works great ! Thanks !

Jan