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

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:D,4,0)
B3:
=INDEX(Record!D1:D100,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

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

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

ROW(2:2)

Biff

"Jambruins" wrote in message
...
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:D,4,0)
B3:
=INDEX(Record!D1:D100,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

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

See response in your other thread...

In article ,
Jambruins wrote:

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:D,4,0)
B3:
=INDEX(Record!D1:D100,SMALL(IF(Record!A1:A100=$B$1 ,ROW(Record!A1:A100)-ROW(Rec
ord!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

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

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

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


"Domenic" wrote:

See response in your other thread...

In article ,
Jambruins wrote:

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:D,4,0)
B3:
=INDEX(Record!D1:D100,SMALL(IF(Record!A1:A100=$B$1 ,ROW(Record!A1:A100)-ROW(Rec
ord!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