Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |