Need some help with a formula...thanks in advance

[Guest]

I have a tab called Record. Here are the cells it contains:
A1: 0-.4
A2: .5-.9
A3: 1.0-1.4
A4: +1.5
A5: PL

This repeats in cells A6-A10,A11-A15,etc.

In another tab called Graph I have the following in cell B2 (cell B1: 0-.4)
=VLOOKUP(B1,Record!A,4,0)

The above formula works fine as it finds the first 0-.4 and gives me the
number in cell D. I would like a formula for cell B3 to find the next
occurance of 0-.4 and give me the number in the corresponding cell D.

Anyone know how to do it?

I belive it is something like this (this is another formula in my
spreadhsheet but I can't follow it). I believe I could use the same formula
but just need to reference the Record tab instead of the Scores tab, B1
instead of Capitals, and the Q and S references change to something.

=IF(ISTEXT(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Scores!$Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000"))),ROW()-1))),(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Scores!$Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000"))),ROW()-1))),"")

 

[Domenic]

Try...

=INDEX(Record!D1100,SMALL(IF(Record!A1:A100=B1,ROW(Record!A1:A100)-ROW(
Record!A1)+1),2))

....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.

Hope this helps!

 

[Biff]

Hi!

Try this:

Use this formula in Graph B2:

=IF(COUNTIF(record!A:A,B$1)>=ROWS($1:1),VLOOKUP(B$1,OFFSET(record!A$1,(ROW(1:1)-1)*5,,5,4),4,0),"")

Copy down as needed.

Biff

 

[Guest]

thanks Domenic, it works perfect.

Try...

=INDEX(Record!D1100,SMALL(IF(Record!A1:A100=B1,ROW(Record!A1:A100)-ROW(
Record!A1)+1),2))

....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.

Hope this helps!

 

[Guest]

Domenic,
Acutally there is still a problem. If I put the formula you gave me into
cell B3 it does find the next occurance of 0-.4. However, I copied the
formula down but cell B4 gives me the same answer as cell B3. Should I be
using $ before and after the A and D in the formula? Should the +1, 2 change
when I copy it down? Thanks

 

[Domenic]

Try...

B3, copied down:

=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))

....confirmed with CONTROL+SHIFT+ENTER. This will return the second
occurrence to B3, the third occurrence to B4, and so on.

Hope this helps!

 

[Guest]

thanks Dom, perfect

Try...

B3, copied down:

=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))

....confirmed with CONTROL+SHIFT+ENTER. This will return the second
occurrence to B3, the third occurrence to B4, and so on.

Hope this helps!