Need some help with a formula...thanks in advance
thanks Domenic, it works perfect.
"Domenic" wrote:
Try...
=INDEX(Record!D1:D100,SMALL(IF(Record!A1:A100=B1,R OW(Record!A1:A100)-ROW(
Record!A1)+1),2))
....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.
Hope this helps!
In article ,
Jambruins wrote:
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:D,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(Sco res!$Q$1:$Q$50000="CAPITALS"
,ROW(INDIRECT("1:50000"))),ROW()-1))),(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Sco
res!$Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000 "))),ROW()-1))),"")
|