Formula not quite right...any help...

[Guest]

I have a tab called Record. Here are the cells it contains:
A30: 0-.4
A31: .5-.9
A32: 1.0-1.4
A33: +1.5
A34: PL

A58: 0-.4
A59: .5-.9
A60: 1.0-1.4
A61: +1.5
A62: PL

This pattern keep repeating down the column.

In another tab called Graph I have the following:
B1: 0-.4
B2: =VLOOKUP(B1,Record!A,4,0)
B3:
=INDEX(Record!D1100,SMALL(IF(Record!A1:A100=$B$1,ROW(Record!A1:A100)-ROW(Record!A1)+1),2))


The above formula works fine as it finds the first 0-.4 and gives me the
number in cell D. However, I copied the formula down but cell B4 gives me
the same answer as cell B3. What needs to be changed?

Thanks

 

[Biff]

Change the 2 at the end of the formula to this:

ROW(2:2)

Biff

 

[Domenic]

See response in your other thread...

 

[Guest]

for anyone interested this is the formula that does it. Thank you Domenic

=INDEX(Record!D$1$100,SMALL(IF(Record!$A$1:$A$100=$B$1,ROW(Record!$A$1:
$A$100)-ROW(Record!$A$1)+1),ROWS(B$3:B3)+1))