vlookup to provide row reference instead of value

G

Graham Tritton

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
 
J

JMB

=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.
 
B

Biff

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
 
Top