Row number for result of lookup function NOT using match

[newtonj2]

I am trying to return the row number as the result of a lookup function. However, the vector does not include every row in the sheet and therefore MATCH will not work because the relative position of the result in the vector is not the same as the row number. Any suggestions on another way to do this?

Ultimately I am trying to refer to the first row in a set of rows. I have several dates that correspond to a store number and close to 1000 store numbers so I cannot enter the cell reference by hand for each one. However, each store has a different number of dates. I am trying to find a way to reference the first date in that set. My thought was to put a 1 in a hidden column next to the first date and then do a lookup for the 1. However I need to return the row number of the 1 so that I can use this in other formulas.

For example:

Store 1:
1/1
2/2
3/3

Store 2:
4/4
5/5

Store 3:
6/6
7/7
8/8
9/9

For 3/3, 5/5, and 9/9 I need a to refer to the row number of 1/1, 4/4, or 6/6 respectively which be blind to the number of rows in the set. I need a formula that I can drag down for all 1000 stores without having to enter the reference by hand.

Thanks!

 

[Weaver]

Welcome to the board.

You could still use match, but to get the row number, just add the row number of the first element in the range you're looking at and subtract 1

So if your range started in A10 and the result comes back as 3

then

row number = 3 + 10 - 1 = 12

HTH