vlookup to provide row reference instead of value
 

I am trying to get the row reference from a vlookup not the value.
A: B
Shop : sales

I have repeating records for 100 shops and I am trying to get the row
reference for the first & last record for each shop so that I can use it in a
TTEST function

=MIN(IF(A1:A100=C1,ROW(A1:A100)))
=MAX((A1:A100=C1)*ROW(A1:A100))

where C1 contains the shop name you are trying to find.

these are array formulas and must be confirmed with Cntrl+Shift+Enter.


"Graham Tritton" wrote:

I am trying to get the row reference from a vlookup not the value.
A: B
Shop : sales

I have repeating records for 100 shops and I am trying to get the row
reference for the first & last record for each shop so that I can use it in a
TTEST function

Hi!

Some non-array alternatives:

First row:

=MATCH(C1,A1:A100,0)

Since Match returns the relative position you would have to make an
adjustment based on the actual starting cell of the data. If the data was in
the range A5:A100:

=MATCH(C1,A5:A100,0)+4

Last row:

=SUMPRODUCT(MAX((A1:A100=C1)*(ROW(A1:A100))))

Biff

"Graham Tritton" <Graham wrote in message
...
I am trying to get the row reference from a vlookup not the value.
A: B
Shop : sales

I have repeating records for 100 shops and I am trying to get the row
reference for the first & last record for each shop so that I can use it
in a
TTEST function